New records without layout change in FileMaker

[MonkeybreadSoftware]

You may find it convenient sometimes to create or update records in a different table without switching layouts. If you do this in a script, you would usually cause a layout change, to fill the fields from the other table on a different layout. However, such a layout change is not really necessary and costs time. Wouldn't it be easier to create a record with only one script line? Without any relationship, portal and layout change?

FM.InsertRecord

We have the FM.InsertRecord, FM.UpdateRecord and FM.DeleteRecord and related functions in MBS FileMaker Plugin to help you. The FM.InsertRecord function inserts a new record in any table of any database. Relationships are not necessary, but the database must be open and therefore "known" to FileMaker. In the documentation of the MBS FileMaker Plugin you can find the following declaration of the function:

MBS ("FM.InsertRecord"; filename; table; field; value; ...)

As you can see, the command has at least four parameters after the function name. The first parameter is the filename of the target database. In principle, this can be left empty, but if the table name exists in several open databases, FileMaker may access the wrong one. In our tests, FileMaker did choose the current database, but there is no guarantee that this is always the case. The second parameter is the name of the target table, which may be in the same file or in another open database. The exact spelling is important.

Furthermore, when renaming a table, you must make sure to adjust the name in the function call to the MBS FileMaker Plugin!

As further parameters follow field names and values. For each field you pass two parameters, first the name and second the value. For the values you are not limited to text, because the plugin passes the values directly - texts, (external) containers, numbers, dates, times and timestamps work fine. The call in our example looks like this:

MBS ("FM.InsertRecord"; $TargetDatabase; $TargetTable; "firstname"; $FirstName; "LastName"; $LastName)

In the sample file we have defined the name of the target database and the target table as fields, so that you can try out the different values. The two transferred fields are called first name and last name, the values come from the current database.

Record number?

Unfortunately, however, there is a small problem: We do not know what number the new record has. In many databases it is possible to query the field values for so-called auto-increment fields or the row number directly, but in FileMaker this option is missing. One solution would be a column with a UUID that is unique and could directly serve as a primary key. Another option is to temporarily insert a UUID into a field and query the RecordID for that UUID via SQL.

This solution is shown in the following script:

Set Variable [ $UUID Value: Get(UUID) ]
Set Field Value [ New Records::Result Value: 
	MBS ("FM.InsertRecord"; 
		$TargetDatabase; $TargetTable; 
		"FirstName"; $FirstName; 
		"LastName"; $LastName; 
		"UUID"; $UUID)
Set Variable [ $RecordID; Value: 
	MBS ("FM.ExecuteFileSQL"; $TargetDatabase; 
		"SELECT RowID FROM data WHERE UUID=?"; 9; 13; $UUID)]
Show Custom Dialog [ Title: "Result"; Message: 
	"New record with RecordID " & $RecordID & " with UUID " & $UUID]

First, a fresh unique ID is created, which is passed into the UUID field in our table. This field, defined as unique and indexed, thus forms a primary key. After creation, the RecordID is queried via SQL, using the RowID keyword. The result is displayed in a dialog. If you use UUIDs as primary keys anyway, it is of course not necessary to query the row number additionally. By the way, UUIDs have another big advantage: You can use them to cleanly merge two databases without having to work over duplicate IDs and relationships.