Door 18 Update

Welcome to door 18 of our Advent calendar. Today, I would like to show you how to update data records.

The plugin offers two types of functions for this purpose. On the one hand, we have functions that update a single data record, and on the other hand, we have functions that update several data records at once. Let's start with the single records. Here we have the functions FM.UpdateRecord, FM.UpdateRecord2, FM.UpdateRecord3, FM.UpdateRecord4, and FM.UpdateRecord5. We'll come back to why there are so many update functions in a moment.

Let's take a look at the structure of the FM.UpdateRecord function. First, we need to specify the file name of the database in which we want to update data. In our case, this is the current one, and we can use the familiar Get function from FileMaker. Then we enter the name of the table in which the record is located, and then we can specify an ID field that uniquely identifies the record, as well as the appropriate value required for identification. As in Door 16, we must ensure that not only are the values in the table unique, but that they have also been defined as unique in the database structure. The parameters of the function are then followed alternately by the field names and the corresponding values to be updated. There are two films with the same name, Miracle on 34th Street. We entered the old film in our database. But we actually wanted to enter the new one. Of course, we could now remove the data record and add a new one with the data, but we can also update it with the new step. If we insert the data just discussed into the function, it looks like this:

Set Variable [ $r ; Value: MBS("FM.UpdateRecord"; Get(FileName); "Movie"; 
   "PrimaryKey"; "0E9810F9-46B0-4508-99B6-458B3C88808E"; 
   "Director";"Les Mayfield";"Launch";1994;"Generated_Sales";46300000) ]

We have already mentioned that we have various functions that sound similar but only have a different sequential number. These functions are very similar to the function we have just learned about, except that we can specify as many ID fields as the number in the function, which specify the data record for us.

In addition to the functions for updating a single data record, there are also functions for updating multiple data records: FM.UpdateRecords, FM.UpdateRecords2, FM.UpdateRecords3, FM.UpdateRecords4, FM.UpdateRecords5.

Here, too, we see the numbers behind the functions, and here, too, the reason is that, as with the previous functions, we can have a different number of fields to identify the data record, depending on the function. But let's take a look at an example here as well: We have a Genre field for each movie. So far, it says Christmas Movie everywhere. We want to change that now. We want all films directed by Chris Columbus to now have the term “Christmas comedy” as their Genre. The parameters of the function are identical to those of the individual functions. The only exception is that the ID fields for identification no longer have to be unique. We can then implement the example as follows:

Set Variable [ $r ; Value: MBS("FM.UpdateRecords"; Get(FileName); "Movie"; 
   "Director"; "Chris Columbus"; "Genre";"Christmas comedy") ]

SQL also offers a way to update fields. To do this, we use the keyword Update. In the SQL statement, we first have the keyword UPDATE followed by the table name. Now we have to specify which fields should receive which values. We introduce this part with the term SET, followed by the field names with an equal sign and then the corresponding value. The individual fields can then be listed separated by commas. Last but not least, we have the WHERE clause, in which we define to which data records this change should apply.

This results in the following structure:

UPDATE Table SET Field1= Value1, Field2=Value2… WHERE Condition

Let's imagine that one of our films had the wrong year in the data record. The Santa Claus 2 film was not broadcast in 2001, but in 2002. We now want to correct this.

Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQL"; Get(FileName); 
   "UPDATE Movie SET Launch = 2002 WHERE Name='The Santa Clause 2'") ]

That brings us to the end of today's door. I hope you enjoyed it, and see you tomorrow for door number 19.