Door 16 Delete

Today we want to take a look at how to delete records. As already mentioned in door 3, we can also delete records using the MBS SQL functions. Today you will learn how this works.

For deleting, we have the functions FM.DeleteRecord and FM.DeleteRecords in the MBS FileMaker Plugin, which I would like to introduce to you. As the name suggests, the FM.DeleteRecord function is well suited for deleting single records from the database. This function works with unique fields. In the function, we first define the file in which we find the record to be deleted in the parameters, then the table name, and finally the field information that identifies the data record. We specify the field name in which we find the unique ID and then the unique ID itself of the record to be deleted. This is the information you must provide. You can also specify the data record with additional unique IDs. It is important that the fields that help us identify the data record not only contain unique values, but that this is also reflected in the database structure in the validation. If we set the name as a field in the Door7 table, even though the values are unique, an error will occur because it is not declared as unique in the database. So, if we want to delete the movie Gramlins from our database in the Door7 table, we use the PrimaryKey field and the primary key corresponding to the movie for identification.

Set Variable [ $r ; Value: MBS("FM.DeleteRecord"; Get(FileName); "Door7"; "PrimaryKey"; "C44FD2D1-75CA-4C4E-967A-9632C1BA64B9") ]

If we want to delete multiple records, or if we don't have the option of using the unique ID, we can use the FM.DeleteRecords function instead. The structure of the function is exactly the same, except that here the field that identifies the record does not have to contain unique values. This means we can now also delete movies by name.

Set Variable [ $r ; Value: MBS("FM.DeleteRecords"; Get(FileName); "Door7"; "Name"; "Die Hard") ]
Show Custom Dialog [ $r ]

However, multiple records can also be deleted simultaneously. For example, all movies released in 1992.

Set Variable [ $r ; Value: MBS("FM.DeleteRecords"; Get(FileName); "Door7"; "Launch"; "1992") ]
Show Custom Dialog [ $r ]

But we don't just have functions specifically for deletion; we can also perform deletion using SQL functions. To do this, we need the keyword DELETE. To delete specific data records, we need the following structure:

DELETE FROM TabelName WHERE condition

So we specify that we want to delete records, define the table from which we want to delete the records in the FROM section, and specify the condition that a record must fulfill in order to be deleted in the WHERE section.

For example, we can delete all movies released before 1990.

Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQL"; Get(FileName); 
   "DELETE FROM Door7 WHERE Launch < 1990") ]

Sometimes we may want to delete all records in a table. This is also very easy to do with SQL. To do this, we simply omit the WHERE clause and all records in the specified table will be deleted.

Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQL"; Get(FileName); "DELETE FROM Door7") ]

But be careful: once you delete something from the database, you cannot undo this step, so use this powerful tool wisely.

I hope you enjoy using these features.