Easy JSON Record Data
Coding software solutions offers the fascinating realization that there are always multiple pathways to achieve the same goal. Sometimes the methods we're familiar with may not be the most optimal, prompting us to expand our knowledge to explore alternative approaches.
This video explores precisely that theme - the comparison between older and newer methods of accomplishing the same task. The advantage lies in the fact that newer methods often come with additional features or benefits. This is particularly true when it comes to obtaining the current record data as a JSON object. Historically, we've relied on ExecuteSQL to extract field names and values from the current record or a set of records.
In this video, I delve into the existing options using ExecuteSQL for creating a JSON object of the current record data. This method is effective in various scenarios. However, accessing related data can be time-consuming, especially when constructing SQL joins accurately requires significant mental effort. Unless you're a SQL expert, this may not be the most efficient use of your development time. Fortunately, recent versions of FileMaker have streamlined the process, making it exceptionally easy, with an alternative method, to extract any data from any record and convert it into the precise JSON object needed for passing parameters or interacting with external APIs.
Comments
SQL Timestamp and Date formats
An interesting thing I learnt about getting timestamps or date for the ExecuteSQL function is that if you concatenate the field name with an empty string then you get the field as it is stored in FileMaker. E.g.
“SELECT
timestampField||’’
FROM
Table
…”
Will return “dd/mm/yyyy hh:mm:ss” or the US format if that is your system local rather than ISO format.
You could modify your custom function to append the empty string onto every field so you don’t have to do any transformations for each data type.
Awesome find!
I'm going to have to check this out. That's a great find which will save the effort of casting the data into another format.
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Correction
After double checking, it has to be at the start of the field name like:
“SELECT
‘’||timestamp
FROM
…”
I don’t know why but it only works with the empty string first.
ExecuteSQL – Date Formats
Additional reference and conversation regarding ExecuteSQL and Date Formats on the Seedcode site:
https://seedcode.com/executesql-date-formats/
A little help with javascript
Matt,
I've used the Execute FileMaker Data API script to create a valid JSON result I want for a layout without portals, and the result contains a JSON object with 10 records. I'm learning javascript and I'm not quite sure how to change the example javascript you provided. Can you post an example javascript that would work for multiple lines of data in data.response.fieldData?
I'm sure it's similar to the portalData section, but I'm struggling with the syntax.
Thanks!
Tom Horton Lead Database Architect
www.visiongift.org
Wrong demonstration file posted?
Hi Matt,
Thanks for another great tutorial!
I noticed that the JSON Record script doesn't quite match what is shown in the video. In particular, the Execute FileMaker Data API script step specified $response rather than $$RESPONSE as the target.
I don't know that it matters, but on the chance there are other differences I thought I would mention it.
Extremely helpful!
Hi Matt,
This has saved me a lot of time!
- and thank you for your consistent and inspiring stream of quality content.
Peter
Peter Christiansen
Can't call from another script
Tried to put this to work in another file.
If I run the script on it's own - works fine calls the response script -no issues.
If I try and perform the script from another script - it doesn't call the Response script.
What am I missing? Any help appreciated.
Option 5, my friends, option
Option 5, my friends, option 5.
Realized that in the perform script with option it needs to be set with parameter 5.
The defaults command so you can inspect the web viewer
defaults write com.FileMaker.client.pro12 WebKitDebugDeveloperExtrasEnabled -bool YES