Tip: Query FileMaker records as JSON

[MonkeybreadSoftware]

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 ] 
Else 
    # 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