FileMaker and JSON Files

FileMaker introduced its JSON functions in version 16. They’ve changed how many developers work, both as a protocol for handling data interchange within FileMaker and working with external APIs. The JSON functions are for working with text, but what if we run into a situation where we need to work with actual JSON files? Does FileMaker provide the tools to read and write .json files as well as JSON text?

[ba-box background=”#ffffd1″ border=”#374c77″ textcolor=”#F7F7F7″]

Looking for our article and example file on the JSON Parsing Function? Here it is: FileMaker JSON Functons.

[/ba-box]

FileMaker JSON: Reading Files

JSON files are really utf-8 text files, and we have a few different ways of handling them in FileMaker.

The Insert From URL script step can be used to insert .json files that are local to the system or downloaded from an external site or API. The way these files are inserted depends on the target that’s specified in the script step. For example, if a variable or text field is specified as the target, then Insert From URL will simply read the .json file and insert it as text. As text, we can then use the JSONGetElement function to parse the specific values.

FileMaker JSON Script
Sample script For inserting JSON as text

If a container field is specified as the target in the Insert From URL step, then the .json file is inserted into the container as an actual JSON file.

FileMaker JSON Container Field
JSON file inserted into a container as .json file

Since .json files are actually utf-8 encoded text files, we can read the text right from the container using the TextDecode function and specifying utf-8 as the encoding. However, it’s also possible these .json files are getting into the container field using a method that’s not Insert From URL, and the option of specifying the target is not available. Hence, the TextDecode function is useful in that scenario as well.

Using TexDecode Script Step
Insert JSON as a file and then read contents with TextDecode

Writing JSON Files from FileMaker

There are also scenarios where you may want to store or send your JSON from FileMaker as a .json file rather than as text. For example, some APIs recommend that you upload a .json file if the payload is very large instead of writing the JSON text inline as part of your request.

Let’s say we want to create the following JSON from FileMaker data and then turn it into a .json file.

{
    "contact" : 
    {
        "email1" : "[email protected]",
        "email2" : "[email protected]",
        "phone" : "855-733-3263"
    },
    "name" : "Jason Young",
    "title" : "Developer"
}

The first thing we’ll do is to create the JSON with our JSONSetElement function like this.

JSONSetElement ( "{}" ; 


	[ "name" ; "Jason Young" ; JSONString ];
	[ "title" ; "Developer" ; JSONString ];
	[ "contact" ; JSONSetElement ( "{}" ;
		[ "phone" ; "855-733-3263" ; JSONString ];
		[ "email1" ; "[email protected]" ; JSONString ];
		[ "email2" ; "[email protected]" ; JSONString ] )
	; JSONObject ]
	

)

If we’re using FileMaker client, we can set the JSON into a text field and then use Export Field Contents and specify the file name with the .json extension and then re-insert the file into our container: we’re all set. However, if we want this to run on the server or WebDirect, we need to handle this differently as Export Field Contents is unavailable on the server and is limited on WebDirect. As discussed earlier, .json files are just utf-8 text files, so we can use the TextEncode function to convert our JSON text to a file. When we do this, we get a file named utf-8.txt, but we need this to be a .json file with a specific name. To change the file name and extension, we can encode the file as Base64 and then decode it and specify the file name with the Base64Decode function’s optional second parameter. Here’s an example script showing how we can create the .json file with the name Example2.json.

Using Base64 with JSON files
Create .json file by encoding and then decoding as base64

We now have a properly encoded .json file ready for an API or for storing as a file.

Export JSON files from FileMaker
.json file created by a FileMaker script

Conclusions

In addition to the JSON text functions like JSONSetElement and JSONGetElement, FileMaker also provides us with the TextEncode and TextDecode functions. These, in combination with the JSON functions, allow us to not only read and write JSON as text but to write as .json files as well, extending the platform to cover whatever our JSON requirement may be.

Featured Posts

Follow Along

Stay up to date with the latest news & examples from SeedCode

7 Comments

  • PL

    {“status”:true,
    “data”:
    {“id”:19,”
    col1″:”subhash”,
    “col2″:”testing”,”col3″:”test2″,”col4″:null,”col5″:null
    this is my json result, but i want to print only col1’s value, how to proceed plz suggest
    }}

    • seedcode

      Hi, PL. We can’t provide ad hoc tech support here. I’d suggest posting this question on the Claris community forum: https://community.claris.com/

      Thanks!

  • This all works great inside the FileMaker world. When I try to export the JSON created in FileMaker as a script result (for example) using the FileMaker API it converts the quotation marks into “\”” which is not really usable for most of other apps. I think we should consider really hard how important is this inconvenience becoming now when we use JSON so much. and want to communicate with the rest of the world so much. We really need to have the better way to decode anything UTF-16 (script result, variable, field content and of course export file…) to at least UTF-8.

    • Jason Young

      Hi Milan,

      Right, so if I’m following you, this is getting JSON out of a FileMaker field using the Data API. The issue here is that the Data API sees a text field and not JSON, so encodes it accordingly in the payload returned by the API, as opposed to just treating it as a nested JSON object in the payload. Since there is no JSON field type, I’m not sure how else the Data API could handle this. Maybe some kind of parameter in the request to specify “return field A as JSON.”

      This could be a place where using a .json utf-8 file in a container and downloading it via the API might be better than getting the JSON from a text field, but I’m not sure where this JSON is being applied after the download, so hard to say.

      Let me know if I’m understanding your comment correctly and thanks for posting.

      My best,
      Jason

      • Milan

        Hi Jason,
        yes, I think you understood the issue perfectly.
        We needed to call a script in FileMaker from the mobile app built in React (JS) and thought it would be beautiful to use your above technique to parse the outcome of the script as JSON and send it back to the mobile app in a script result. That didn’t work exactly because of the issue you are mentioning in the reply. We managed to find a workaround (as FileMaker Developers so often do), but it would be really cool if we could just export JSON as JSON. Creating the file and getting the JSON from there looks like a good idea as well, I will try that next time we bump into this kind of issue.

  • Paul Jansen

    Thanks for the article. I am puzzled by yours of nested JsonSetElement(). Is there any reason why you did not use:

    JSONSetElement ( “{}” ;
    [ “name” ; “Jason Young” ; JSONString ];
    [ “title” ; “Developer” ; JSONString ];
    [ “contact.phone” ; “855-733-3263” ; JSONString ];
    [ “contact.email1” ; “[email protected]” ; JSONString ];
    [ “contact.email2” ; “[email protected]” ; JSONString ]
    )

    which produces the same result.

    • Jason Young

      Thanks for posting this, Paul!

      The main reason is that I forget that JSONPath supports that kind of notation. When I’m writing JSON, I am definitely translating making JavaScript objects in my head, where you need to make sure the object is there before adding keys to it, but your way is a clean, clear, and quick way of doing this and maybe someday I’ll remember you can use the dots for creation too when I’m actually working ;-).

Leave a Reply

Your email address will not be published. Required fields are marked *

Check out some of our other posts ...

Comments in FileMaker Calendar

Highlight Notes and Comments in Your Calendar

Inline Notes in DayBack Calendar The latest DayBack extension adds an icon to your events when there is a comment present that matches your criteria. You can

Closed Through the New Year

SeedCode is closed for our holiday break from December 19th through the end of the year. We’ll have folks available in case of emergencies and

COMPANY

FOLLOW ALONG

Stay up to date with the latest news & examples from SeedCode

© 2023 SeedCode, Inc.