{"id":17026,"date":"2024-05-03T06:00:00","date_gmt":"2024-05-03T06:00:00","guid":{"rendered":"https:\/\/www.directimpactsolutions.com\/?p=17026"},"modified":"2025-05-01T02:49:33","modified_gmt":"2025-05-01T02:49:33","slug":"portable-filemaker-records-using-json","status":"publish","type":"post","link":"https:\/\/www.directimpactsolutions.com\/en\/portable-filemaker-records-using-json\/","title":{"rendered":"Portable FileMaker Records Using JSON"},"content":{"rendered":"

How to pack up an entire FileMaker record into a JSON object using a simple modular script<\/h2>

In FileMaker development, as in any development environment, we have lots of tricks and techniques we use to get things done \u2013 from the simple (like automatically removing text formatting<\/a> in a field) to the advanced (using \u201cmagic key<\/a>\u201d style transactions). In my development work, I often have a need to move data from one place to another, and I\u2019ve found that JSON has become my go-to format for accomplishing this, specifically because it\u2019s well suited to this exact use case: storing and moving structured data. If you\u2019re not familiar with using JSON in FileMaker, then it\u2019s high time you learned. Go read my step-by-step guide to building JSON in Filemaker<\/a>.<\/p>

A few years ago, a colleague of mine built a quick and easy script to pack up an entire FileMaker record into a JSON object (which I\u2019ve since adapted and optimized), and I\u2019ve found myself using her technique over and over again. It\u2019s particularly useful when parsing data from a flat data source into multiple tables, but I\u2019ve also used it to pack up an entire record, add metadata, encode the entire thing in Base64 format, and send it across the internet to another server using the Data API.<\/p>

The basic premise is this: using a list of the field names in your table, you pull a record\u2019s value from each field in the list, and add it as an element to a JSON object. By the end of this process, you have a list of all fields in the table and their values, neatly packed up in a single JSON object!<\/p>

Here is the entirety of the script:<\/p>

\"\"<\/figure>

This technique leverages the ExecuteSQL function and FileMaker System Tables<\/a> to get a list of the fields in the table you\u2019re sitting on. We start by getting the table name and the list of fields in that table:<\/p>

\"\"<\/figure>

This results in two variables, called $$table and $$fieldList, which will look something like this:<\/p>

\"\"<\/figure><\/div>\n\n
\"\"<\/figure><\/div><\/div><\/div>

We then count the number of fields in our table, and prepare to loop through the list of fields:<\/p>

\"\"<\/figure>

In the next step, we loop through each of the field names in $$fieldlist, use the GetField() function to get the contents of this field in this table, and add it as an element to a JSON object called $record:<\/p>

\"\"<\/figure>

At the end of this loop, we end up with a variable called $record that looks something like this:<\/p>

{\"BirthCountry\":\"USA\",\"DOB\":\"8\/11\/1954\",\"Email\":\"marcus@odiorne.com\",\"First\":\"Marcus\",\"Language\":\"\",\"Last\":\"Aurelius\",\"Middle\":\"K\",\"PersonID\":\"109992\",\"Race\":\"White\",\"SexAtBirth\":\"M\",\"cAge\":\"66\"}<\/code><\/pre>
We can reformat it using the JSONFormatElements() function so that it looks better:<\/p>
{\n\t\"BirthCountry\" : \"United States\",\n\t\"DOB\" : \"8\/11\/1954\",\n\t\"Email\" : \"marcus@odiorne.com\",\n\t\"First\" : \"Marcus\",\n\t\"Language\" : \"\",\n\t\"Last\" : \"Odiorne\",\n\t\"Middle\" : \"K\",\n\t\"PersonID\" : \"109992\",\n\t\"Race\" : \"White\",\n\t\"SexAtBirth\" : \"M\",\n\t\"cAge\" : \"66\"\n}<\/code><\/pre>
At the end of the script, we can add metadata to this JSON block, if we want, but this is totally optional. You might consider things like this:<\/p>
{\t\n    \"Record\" : \t\n    {\n        \"BirthCountry\" : \"United States\",\n  \t\t\"DOB\" : \"8\/11\/1954\",\n  \t\t\"Email\" : \"marcus@odiorne.com\",\n  \t\t\"First\" : \"Marcus\",\n  \t\t\"Language\" : \"\",\n  \t\t\"Last\" : \"Odiorne\",\n  \t\t\"Middle\" : \"K\",\n  \t\t\"PersonID\" : \"109992\",\n  \t\t\"Race\" : \"White\",\n  \t\t\"SexAtBirth\" : \"M\",\n  \t\t\"cAge\" : \"66\"\t\n    },\n    \"metaData\" :\n    {\n\t\t\"Account Name\" : \"Admin\",\n\t\t\"DeviceID\" : \"C56C3D9278D302299180B26B584EEE\",\n\t\t\"Host IP\" : \"\",\n\t\t\"PrivSet\" : \"[Full Access]\",\n\t\t\"ScreenSize\" : \"2560x1440\",\n\t\t\"System IP\" : \"192.168.0.7\",\n\t\t\"SystemVer\" : \"14.4.1\",\n\t\t\"Time\" : \"4\/12\/2024 1:58:27 PM\"\t\n\t}\n}<\/code><\/pre>
In the last step of the script, there is an exit step, which returns the JSON result:<\/p>
Exit Script [ Text Result: $result ]<\/code><\/pre>
This hands the JSON back to the parent script, which you get by calling the Get ( ScriptResult ) function:<\/p>
Set Variable [ $record ; Value: Get ( ScriptResult ) ]<\/code><\/pre>
That\u2019s it!<\/p>
Once you have the record packed up in this way, you can pull individual elements out by using FileMaker\u2019s built-in JSON functions. For example, if you wanted to go to a new table, create a record, and set some fields in that new record to some of the elements in the $record example above, you\u2019d do something like this:<\/p>
\"\"<\/figure>
It\u2019s important to note here that the data in the JSON variable is all in captured in \u201cstring\u201d format, meaning the data types are not recognized as Number, Date, Boolean, etc. While this is advantageous in one respect \u2013 FileMaker does not try to reformat anything \u2013 when parsing the data, you may be required to convert the elements to the data type that you need, particularly if you\u2019re pushing data into a different system. As an example, you may pull a date out of your record as a string like this: \u201c4\/26\/01\u201d. When pushing it into another system, you might need to use a FileMaker function like GetAsDate() to recast it as an actual Date data type, or reformat it into ISO format (like \u20182024-04-26\u2019). Similarly, recasting the parsed data type using GetAsBoolean() or GetAsNumber() might be required.<\/p>
Notice that the beginning of the script has a step that checks to see if a global variable called $$table is the same as the current table name:<\/p>
If [ $$table \u2260 Get ( LayoutTableName ) ]<\/code><\/pre>
This is simply to save time if you\u2019re calling this script inside a loop. Running the ExecuteSQL function against the FileMaker System Tables can be rather slow; it can take up to a half-second to generate the field list. While this would not typically be an issue with a single script call, when looping through records, it will add a significant slowdown to the process if done repeatedly. Since a looping process would usually be performed in the same context again and again, there\u2019s no need to get the same list of fields for each loop, so we start by setting the table name that we\u2019re sitting on in the global $$table variable. If the context of the script is the same for each loop, we can reuse the existing $$fieldList for the next loop!<\/p>
This technique has been so useful in my FileMaker development that I return to it again and again. I hope you can find a use case for it in your own work. Happy data packing and unpacking!<\/p>","protected":false},"excerpt":{"rendered":"
How to pack up an entire FileMaker record into a JSON object using a simple modular script In FileMaker development, as in any development environment, we have lots of tricks and techniques we use to get things done \u2013 from the simple (like automatically removing text formatting in a field) to the advanced (using \u201cmagic …<\/p>\n
  Portable FileMaker Records Using JSON<\/span> Read More »<\/a><\/p>\n","protected":false},"author":23,"featured_media":17048,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","footnotes":""},"categories":[29],"tags":[201,202],"class_list":["post-17026","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-low-code","tag-json","tag-json-object"],"uagb_featured_image_src":{"full":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-scaled.jpg",2560,1600,false],"thumbnail":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-150x150.jpg",150,150,true],"medium":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-300x188.jpg",300,188,true],"medium_large":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-768x480.jpg",768,480,true],"large":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-1024x640.jpg",1024,640,true],"1536x1536":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-1536x960.jpg",1536,960,true],"2048x2048":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-2048x1280.jpg",2048,1280,true],"woocommerce_thumbnail":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-300x300.jpg",300,300,true],"woocommerce_single":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-600x375.jpg",600,375,true],"woocommerce_gallery_thumbnail":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2024\/04\/iStock-1456089042-100x100.jpg",100,100,true]},"uagb_author_info":{"display_name":"David Weiner","author_link":"https:\/\/www.directimpactsolutions.com\/en\/author\/david-weiner\/"},"uagb_comment_info":0,"uagb_excerpt":"How to pack up an entire FileMaker record into a JSON object using a simple modular script In FileMaker development, as in any development environment, we have lots of tricks and techniques we use to get things done \u2013 from the simple (like automatically removing text formatting in a field) to the advanced (using \u201cmagic…","_links":{"self":[{"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/posts\/17026","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/users\/23"}],"replies":[{"embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/comments?post=17026"}],"version-history":[{"count":3,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/posts\/17026\/revisions"}],"predecessor-version":[{"id":19965,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/posts\/17026\/revisions\/19965"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/media\/17048"}],"wp:attachment":[{"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/media?parent=17026"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/categories?post=17026"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/tags?post=17026"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}