Welcome to the last door of the Advent calendar. Today, we would like to take a look at a function in MBS that is particularly popular with users: FM.InsertOrUpdateRecord. This function means that we don't always have to decide whether a data record needs to be inserted if it doesn't already exist, or whether it already exists and just needs to be updated. The function makes this decision for you. It checks whether the entry already exists in the database. If so, it updates the data record; otherwise, it creates a new data record.
This is what the structure of the function looks like:
MBS( "FM.InsertOrUpdateRecord"; FileName; TableName; IDField; IDValue; FieldName...; FieldValue... )
When we use this function, we first enter the file name and the table name of the table into which we want to insert data or make updates. Then we enter the ID field. This must contain a unique value, but unlike the functions we learned about earlier, it does not have to be defined as unique in the database. This means that it is sufficient for the movie name to appear only once in the database, but this does not have to be based on the database definition. We then specify the value for the IDField. This field combination then determines which data record is to be changed. We can then specify the values we want to set in the data record that is to be changed or newly created. To do this, we specify the field names alternating with the values and separate them with a semicolon.
Let's try this out with an example. We want the movie Snow Bride, released in 2013 and directed by Bert Kish, to be added to the database, or if the movie title already exists, the fields should be updated accordingly. Such an entry might look like this:
Set Variable [ $r ; Value: MBS( "FM.InsertOrUpdateRecord"; Get(FileName); "Movie"; "Name"; "Snow Bride";
"Name"; "Snow Bride";"Director";"Bert Kish";"Launch";2023 ) ]
Show Custom Dialog [ $r ]
We can now see that the ID field can also be set in the same function, which is why we have entered the name and value Snow Bride twice here.
Since the data record is not yet contained in our database, it will be added to the database this time. We can now see that we have made an “unintentional” 
typo. It should say 2013 and not 2023. We can now simply change this in the script step and execute it again.
As you can see, the value has been changed without any further effort.
We don't just have one FM.InsertOrUpdateRecord function, but several functions that sound very similar: FM.InsertOrUpdateRecord2, FM.InsertOrUpdateRecord3, FM.InsertOrUpdateRecord4, FM.InsertOrUpdateRecord5. All of these functions work very similarly, but again, the difference is the number of ID fields. FM.InsertOrUpdateRecord has one ID field, but if you want to describe the record in more detail and need to identify it uniquely, this is often not enough. For this reason, you have functions that have more ID fields and that you can choose according to your needs.
Now I would like to introduce you to a very special function: FM.InsertOrUpdateRecordQuery. This function allows you to update and insert records into a table using another table. We use an SQL query to retrieve records from a table and insert them if the corresponding value does not yet exist in the ID field. If the value already exists, we change the specified fields.
Let's look at an example. We have created a table called Door24 and filled it with data covering all films released between 2000 and 2009. We did this with this command.
Set Variable [ $r ; Value: MBS( "FM.InsertOrUpdateRecordQuery"; Get(FileName); "Door24";
"Name¶Launch¶Generated_Sales"; "Name=0"; Get(FileName);
"SELECT Name, Launch, Generated_Sales FROM Movie WHERE Launch>=2000 AND Launch <=2009") ]
Now let's delete a few of these entries, change others, and add a movie so we can see the changes right away. Next, we call FM.InsertOrUpdateRecordQuery in a script. In the parameters, we first specify the name of the file and the table in which we want to update and insert the data, followed by a list of fields that we want to fill in during the update or insert. Now comes an important parameter that is often a little difficult to understand. Here, we have to specify which field or fields are used for the comparison. This determines whether the record already exists in the table and is updated (reference fields are identical) or whether it is added (there is no reference field with the same value). In our table, the reference field is the movie name. But now we don't specify the names of the two fields that should match. Instead, we specify the field name from the table into which the data is to be inserted and then specify the index of the field that is now in the SELECT part of the SQL query we are writing in a moment. In our example, we want to compare the movie names, so we enter the name of the field from the Door 24 table, and when we define the Select table, we name the field Name first in the Select part, giving it the index 0. So we can write Name=0. The next parameter is the file name of the file containing the table to which we are about to send our data request. As already mentioned, the SQL query finally comes next. In the WHERE part of the query, we again want all movies between 2000 and 2009.
Set Variable [ $r ; Value: MBS( "FM.InsertOrUpdateRecordQuery"; Get(FileName); "Door24";
"Name¶Director¶Launch¶Generated_Sales"; "Name=0"; Get(FileName);
"SELECT Name, Director, Launch, Generated_Sales FROM Movie WHERE Launch>=2000 AND Launch <=2009") ]
We see that the previously deleted movies are reinserted into the table because they are imported again from the Movie table. The previously changed records are also changed again. The movie Harry Potter and the Philosopher's Stone does not exist in the Movie table. When the data is synchronized, it is not deleted, but simply left as it is.
But what happens now if we make a change to the reference field and the data is no longer the same?
We tried this with the movie ”The Family Stone” and we see that it is no longer recognized as a movie to be synchronized and is therefore newly inserted.
This brings us to the end of this year’s calendar. I hope you enjoyed it and that you were able to take away a thing or two for your everyday life. All that remains is for me to wish you a wonderful and peaceful Christmas.
Merry Christmas!
Download the example file: Christmas2025.fmp12.zip and all articles as PDF: MBS_Advent_25_complete.pdf.
