Generating JSON Data
The data format used by JavaScript for data storage and transfer is called JSON or JavaScript Object Notation. It is a flexible text-based format for converting nearly any type of data into a textual format compatible for use with servers and browsers.
Although JSON is not natively supported as an export format in FileMaker, the app’s scripting tools enable the conversion of database record data into JSON for insertion into the Omni Automation script URLs executed by the FileMaker scripts.
Omni Automation uses specialized URLs to communicate between applications. When transferring information using URLs, the data to be transfered must converted into a text format that can be percent encoded for transmission and decoded when received.
In this section we’ll examine how to generate JSON for individual records of the database, and then how to combine the record JSON into a single JSON object for transmission via an Omni Automation script.
Generating JSON for the Current Record
Here is the FileMaker script for generating JSON from the data in the current record. The conversion process involves a small amount of data cleaning, but mostly it is creating JSON objects and arrays by encasing and ordering the record information with the punctuation used by JSON structures.
1 Create a variable named $reportsValue containing the contents of the Reports field.
2 A conditional statement that checks the value of the $reportsValue variable. If it is an empty string, then execute line 3, otherwise, execute lines 5 through 17.
3 Create and set the contents of a variable named $reportsArrayString to an empty array coerced into a string: "[ ]"
4 If the contents of the $reportsValue is not empty, then process the list of report entries into an array of employee IDs.
5 Instantiate a variable named $counter and assign it an initial value of 1.
6 Instantiate a variable named $listLength and assign it a numeric value that is the number of entries in the Reports list.
7 Begin a loop for processing each of the Report entries to extract the Employee ID from the entry string.
8 Instantiate a variable named $listItem that contains the filtered value of one of the entries of Reports list. The chosen filter will only return the numeric characters in the source string, which in this case is the Employee ID. The other characters are ignored.
9-15 If the processed Reports entry is the first one, instantiate a variable named $reportsArrayString and prepend its contents with the opening array bracket ([) and encase the value in quote marks. Otherwise, if the processed Reports entry is the last one in the list, append its quote-encased value with the closing array bracket: (]) Otherwise, append the quote-encased value to the contents of the $reportsArrayString variable.
16-17 If the value of the $counter variable is the value of the $listLength variable, exit the loop, otherwise increment the value of the $counter variable by 1.
20 Now that the Employee IDs have been extracted from the contents of the Reports field, instantiate a variable named $resultingJSON containing the data from the record in JSON format (see image below)
21 Stop the script and return the string value contained in the $resultingJSON variable.
(below) The contents of the $resultingJSON variable is a calculation of field contents placed within the punctuation used to create JSON: quotes, braces, brackets, and colon-delimiters (character 58)
The result of the calculation will resemble a JSON object like this:
| Record JSON | ||
| 01 | {"name":"Katherine Jacobs","Employee ID":"340048809","Manager":"Rene Dalton","Manager ID":"43331060","Reports":["71628234","65779692","73127070"]} | |
Generating Combined JSON of All Records
Now that we have a script for extracting the data of a single record in JSON format, the data of all records (or the records of a found set) can be extracted by iterating the records and applying the script to each record. Here is a FileMaker script for doing that:
1 After extracting the data from the iterated records, we want to return to the current record. Instantiate a variable named $recNum and assign its value to be the record number of the current record.
2 Instantiate a variable named $dbJSON to hold the accumulated JSON content. Assign its value to be an empty string.
3 Instantiate a variable named $recordCount that will contain the number of records being processed.
4 Instantiate another variable named $counter and assign it an initial value of: 1
5 Go to the first record in the database.
6-17 A loop for iterating the individual records of the database.
7 Execute the FileMaker script “Get JSON for Record” that will generate and return JSON data using the content of the current FileMaker database record.
8-14 Conditional statements for determining how to append the record JSON to the variable $dbJSON
15 Go to the next record unless the current record is the last record, in which case exit the loop.
16 Increment the integer value of the loop counter variable by 1
18 Display the record that was showing before the script was run.
1 Stop the script and return the generated JSON stored in the variable: $dbJSON
The result of the script will be an array of JSON objects representing the iterated records of the database:
| Combined JSON | ||
| 01 | [{"name":"Katherine Jacobs","Employee ID":"340048809","Manager":"Rene Dalton","Manager ID":"43331060","Reports":["71628234","65779692","73127070"]},{"name":"Vanessa Moody","Employee ID":"88232780","Manager":"Rene Dalton","Manager ID":"43331060","Reports":["17577344","91170578","53477483"]},…] | |
Next
The next section examines how to insert the generated record JSON data in to an Omni Automation script that transfers the data to the currently open OmniGraffle document.
This webpage is in the process of being developed. Any content may change and may not be accurate or complete at this time.
DISCLAIMER