Insert or update with JSON

[MonkeybreadSoftware]

 

Recently clients asked to get a quick way to insert records with JSON data. As soon as that was done, the next one asked for updating and then of course for our insert and update.

We had these functions for years: 

• FM.InsertRecord
• FM.InsertOrUpdateRecord
• FM.InsertOrUpdateRecord2
• FM.InsertOrUpdateRecord3
• FM.InsertOrUpdateRecord4
• FM.InsertOrUpdateRecord5
• FM.UpdateRecord
• FM.UpdateRecord2
• FM.UpdateRecord3
• FM.UpdateRecords
• FM.UpdateRecords2
• FM.UpdateRecords3
• FM.DeleteRecord
• FM.DeleteRecords

And all these functions are very handy, but how about passing parameters as JSON?

Insert record

To insert, you need a JSON with an object containing the value and then call our JSON.InsertRecord function: 

MBS( "JSON.InsertRecord"; FileName; TableName; JSON )

Let us make an example:

MBS("JSON.InsertRecord"; ""; "Contacts"; "{\"FirstName\": \"John\", \"TheNumber\": 123}")

Here we push "" for the file name, which refers to current file. Then we pass the name of the table with Contacts and a JSON with the values, in this case first name and a number field.

You can also just use our JSON functions or JSONSetElement to build the JSON. That may include using GetFieldName() to get the field names without typing them:

MBS("JSON.InsertRecord"; ""; "Contacts"; 
JSONSetElement ( 
	JSONSetElement ( "{}" ; 
		GetFieldName ( Contacts::FirstName ); "John"; JSONString); 
		GetFieldName ( Contacts::TheNumber ); 134; JSONNumber)
)

When you get the name with GetFieldName, the plugin just removes the table name prefix.

If the function fails, you get an error message returned and OK on success. In case of error, you can use FM.ExecuteSQL.LastSQL function to get the SQL command for inspection.

Update record

When you insert, you may also enjoy updates. Whether you just update one field or the whole record, you can pass a JSON for the keys to match and find the record as well as the values record:

MBS( "JSON.UpdateRecord"; FileName; TableName; PrimaryKeys; Values )
MBS( "JSON.UpdateRecords"; FileName; TableName; IDField; IDValue; JSON )

The function exists in two variants. First one is limited to one record and second one updates many records:

MBS("JSON.UpdateRecords"; ""; "StatusRecords"; "{\"Status\": 1}"; "{\"Status\": 2}")

This would find all records with Status = 1 and change them to Status = 2.

Or let's update two fields and pass the primary key of the current record:

MBS("JSON.UpdateRecord"; ""; "Contacts"; 
JSONSetElement ( "{}" ; 
	GetFieldName ( Contacts::PrimaryKey ) ; Contacts::PrimaryKey ; JSONString ); 
JSONSetElement ( 
	JSONSetElement ( "{}" ; 
		GetFieldName ( Contacts::FirstName ); "John"; JSONString); 
		GetFieldName ( Contacts::TheNumber ); 134; JSONNumber)
)

Update or insert record

When you insert, you may want to avoid duplicates. So how about letting the plugin check if there is a duplicate and if we find it, update the values.

We got this function to do that and you pass primary keys with values to identify the record and a JSON with new values:

MBS( "JSON.InsertOrUpdateRecord"; FileName; TableName; PrimaryKeys; Values )

How the magic works? Well we first check if the record exists. If it is missing, we can just insert it and be done. But if FileMaker reports a duplicate on the insert, we fall back to update. If the insert or update works, we return OK, otherwise an error.

Delete record

Once you got an update, you can also do the delete in the same style:

MBS( "JSON.DeleteRecord"; FileName; TableName; PrimaryKeys )
MBS( "JSON.DeleteRecords"; FileName; TableName; PrimaryKeys )

The function with s deleted multiple records. That may be records identified not just by primary key, but any field and value. 

Values

If the value in JSON is a text, we pass it on directly as text. Same for numbers, but we pass them as numbers to FileMaker. We check the field type via the functions used for our Audit handling, so first time you call one of the functions, it may take a bit time to query the schema data. If the field type is date, we use GetAsDate() on the text in the JSON. For time we use GetAsTime() and for TimeStamp fields the GetAsTimeStamp() function. If the JSON contains an array or other objects, we convert JSON to text and put it in the text field.

Finally, checkout the JSON.Import function to import a lot of data. That function then creates tables and fields as needed.