Door 11 Adding records

Today, we want to take a look at how we can use SQL to insert new records into our table. This feature is incredibly cool because you can insert a record into your FM table without having to switch layouts. Personally, this is my favorite use of SQL in FileMaker databases. Let's take a closer look at the appropriate SQL command:

INSERT INTO table_name (field1, field2, field3, ...)

VALUES (value1, value2, value3, …)

First, we have the keywords INSERT INTO. This is followed by the name of the table into which the new data is to be inserted. The field names for which we want to specify a value are given in brackets. This is followed by the keyword VALUES, which indicates that the specified fields are now to be filled with data. We then enter the values for the individual fields, also in brackets, in the correct order. If we now want to enter a new Christmas movie for our Movie table, it might look like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "INSERT INTO Movie (Name, Director, Launch, Generated_Sales) 
    VALUES ('A Christmas Story', 'Bob Clark', 1983, 19200000)" ) ]

The data record is inserted quickly and easily.

If you don't want to remember the entire SQL command, MBS also offers a function called FM.InsertRecord. In this function, you first specify the file in which you want to insert the record. Since we want to stay in the same file, we use Get(FileName) here. Next, we select the table to which the data record is to be added. Then we alternately specify the field name and the corresponding value. We can specify as many fields as necessary with the corresponding value. For our movie, it looks like this:

Set Variable [ $r ; Value: MBS("FM.InsertRecord"; Get(FileName); "Movie";
   "Name"; "A Christmas Story"; 
   "Director"; "Bob Clark"; 
   "Launch"; 1983; 
   "Generated_Sales"; 19200000) ]

I hope this useful tool will make your work a little easier too.