Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Tip: Query FileMaker records as JSON


Recommended Posts

  • Advertiser

When you need FileMaker records as JSON, you can just query them via MBS Plugin functions. Use FM.SQL.Execute first to run SQL and get reference number to result in memory. Then call FM.SQL.JSONRecords to get the records as a JSON array. This has high performance and may be faster than looping in a script to build the JSON in pure FileMaker functions. 

Once you have the JSON, you may just pass it to FileMaker's data API to move records to another server or pass them as payload for a web service. Within the SQL you can make adjustments to the data. For example you insert fixed values with putting them in the SQL (text in single quotes), you can use CONCAT to concat texts or use CAST() to change data type in SQL. By passing field list to FM.SQL.JSONRecords, you can rename the fields to match whatever you target service expects.

# get related teams
Set Variable [ $r ; Value: MBS( "FM.SQL.Execute"; Get(FileName); "SELECT \"UUID\", \"ID\", \"ID_Abteilung\", \"Team.Name\", \"Txt_Anz.Mia\" FROM \"Teams\" WHERE ID_Abteilung=?"; Abteilung::ID) ] 
If [ MBS("IsError") ] 
    Show Custom Dialog [ "SQL error" ; $r ] 
    # fill as json in field
    Set Field [ Abteilung::JSON_Abteilung ; MBS( "FM.SQL.JSONRecords"; $r; "UUID¶ID¶ID_Abteilung¶Team.Name¶Txt_Anz.Mia") ] 
    Set Variable [ $e ; Value: MBS( "FM.SQL.Release"; $r ) ] 
End If 

Don't forget to release memory when you are done by calling FM.SQL.Release function.
Check also FM.SQL.JSONRecord to just get one row as JSON object. Use FM.SQL.InsertRecords to insert records into another table in another file within FileMaker or FM.SQL.InsertRecordsToSQL to insert records to a foreign database, e.g. MySQL. You can use FM.SQL.CSV to get records as CSV for exporting.

If you have questions, please don't hesitate to contact us.from Query FileMaker records as JSON

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use