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?

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

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.

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

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.

Tagged with →  

5 Responses to FileMaker and JSON Files

  1. Paul Jansen says:

    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” ; “support@seedcode.com” ; JSONString ];
    [ “contact.email2” ; “support@dayback.com” ; JSONString ]
    )

    which produces the same result.

    • Jason Young says:

      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 ;-).

  2. Milan says:

    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 says:

      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 says:

        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.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share →
DOWNLOAD
DayBack Calendar
DayBack's 30-day trial is unlocked so you can customize it and integrate it with your files.
Download DayBack and we'll send you a couple short emails with tips on how to modify it and use some of the coolest features.
Thank you! Please download: DayBack Calendar
Need More?
SeedCode tips & example files in your inbox
Need More?
SeedCode tips & example files in your inbox
Want More?
Be the first to see articles and tips like these
DOWNLOAD
TimeZync
Download TimeZync and we'll send you a couple short emails with tips syncing your FileMaker Go files.
Thank you! Please download: TimeZync
Want More?
Be the first to see articles and tips like these