BlogFileMaker

How to Parse JSON (or any other language) in FileMaker

By December 17, 2011 9 Comments

Sometimes you find the need to parse another language in order to get that data into FileMaker. I’ve found that with a lot of substitutes, this process can be pretty painless. I will demonstrate how to do this via a script, and then how to do this via a custom function. I’ll be using JSON in my demonstration.

I recommend looking at the sample file while reading through these steps: ParsingJSON.fp7

Here is a breakdown of the script (parsing part only, see sample file for full script).

  1. Remove the noise and separate the data. I use a substitute to remove the unwanted characters (“{[}[ ) and replace commas with a return character so that the data I want is in a nice value list.

Remove noise from JSON

  1. Find what the error case will be. To find the error case, it may take some trial and error via the script debugger/data viewer. For this example, the error case is when the result returned is the first value in our value list. Therefore, when I go through the loop, and the value I’m looking for cannot be found (for example: name), then the first value will be returned (Service:Request:IsValid:True). So I know that if the first value is returned, then I should exit the loop.
  2. Create a loop that does the parsing. Add an exit loop script step that checks if the value returned is the error case. Use an iteration variable that gets updated with each passing of the loop.
  3. Inside the loop, set each variable to the proper node. The formula for each set variable script step is the let function:
Let( [search = "name:";  //Node we are currently setting, includes semicolon
start = Position( $JSON; search; 1; $i); //Where the node is in the value list
vCount = PatternCount( Left ( $JSON ; start ); "¶")+1; //what number in the value list
result = Substitute( GetValue ( $JSON ; vCount ); search; "")//get the value and remove the node name
];

result

),/pre>

The custom function fn_ParseJSON ( JSON ; node ; instance ) follows the same concept:

Let ( [

CodeClean = Substitute ( JSON ; [""" ; ""]; ["["; ""]; ["]"; ""]; ["{"; ""]; ["}"; ""]; [",";"¶"]; ["dogs:"; "¶"]  ); //Clean up the code
start = Position( CodeClean; node & ":";1; instance); //Find where the node is
vCount = PatternCount( Left( CodeClean; start); "¶") +1;  //Find the value position
Result = Substitute( GetValue ( CodeClean; vCount ); node & ":"; "")//get the value and remove the node name
];

Trim( Result)

)

You’ll see in the sample file that you can start with the JSON code and end with the specific data you are looking for.
Parsing JSON Screenshot
I hope this helps you with your parsing! Let me know if you have any questions.

Thanks for reading!

Makah Encarnacao

If you would like assistance parsing more complicated JSON or XML, please contact us.

Button Text
Makah Encarnacao

Makah Encarnacao

Makah is a Technical Project Lead that hails from Albuquerque, NM. She joined Soliant a few months after graduating from UCLA in 2007, and must like it a lot because she’s still here! She is certified in FileMaker 9, 10, 11, 12, 13, 14, 15, 16 and 17, and placed 4th in the 2015 FileMaker DevCon Developer Cup. She was a speaker at the 2016 and 2018 FileMaker Conference. When she is not coding, she is enjoying life with her family that recently increased by 1.

9 Comments

  • Avatar Osamu Noda says:

    How’s about this idea?
    Use a webviewer as JSON Handler
    –webviewer definition–
    Let(
    html=”data:text/html,

    var obj=_STR_;
    var targetValue=obj._TARGET_;
    location.href=’data:text/html,’+targetValue;

    “;
    Substitute(html;[“_STR_”;json];[“_TARGET_”;targetNode])
    )
    /*json:raw json string(filemaker text field)*//*targetNode(filemaker text field):the property you want to get. for example-> Service.Response.dogs[2].name*/

    –Filemaker Side Script(onmodify trigger of json field or targetNode field)–
    pause/resume script [duartion:0.4]
    Set Field[resultField;Substitute(GetLayoutObjectAttribute(“web”;”source”);”data:text/html,”;””)]/*web:webviewer object name*/

    Then you can get the property you want as soon as json or targetNode field is changed.

  • Avatar john renfrew says:

    This is JUST what I have been looking for today. Thank you.

    I am using the javascript post to self in a webviewer trick to add cards to Trello without a plug-in, so it works on Go, and I needed to parse the JSON which is returned (which I would normally do in Groovy)

    Cool

  • Avatar Olly Groves says:

    Thanks for this Makah, second time I’ve used your demo as a starting point for some parsing.

    Tried Osamu’s method but not managed to get ‘targetValue’ object to evaluate correctly (yet) for some reason.

    + another option to add to those above. I’m trying the MBS plugin (commercial) (http://www.monkeybreadsoftware.de/filemaker), has a bunch of Json functions out the box, so far is lot less painful when quickly need to target specific objects, arrays and values in a big doc.

  • Avatar Osamu Noda says:

    From Filemaker version12, we can use fmp protocol to interact between web viewer and filemaker.
    Now the method I posted may be old.
    I think it’s easier to handle JSON in web viewer(javascript) than in filemker text function.
    After processing JSON in javascript,use fmp prototocol with the variable which includes the value you want,then you can get the variable in your filemaker script.

  • Avatar mattbarney says:

    Can someone who is familiar with this process, has a good handle on FM12, and designing user interfaces, and is looking for a small project please email me at: info(at)positioninc.com.au

  • Avatar Brent Hedden says:

    This is a great and very elegant solution. There is one problem with this method that can be a deal buster. It won’t work if any of the values contain a comma. Or any of the ‘reserved’ characters that are in the substitute part of the CleanCode variable. Since my case uses commas in the data, I ended up using the MonkeyBread Software plugin. Although, using the WebViewer would certainly work too.

  • Avatar Makah Encarnacao says:

    Hi Brent. That is a good point, I didn't think of that. I played around with it a little big, and you could substitute the comma in very specific cases, like when it's surrounded by quotes, and when there's a bracket in front of the comma, etc. For example, something like this: 

    Substitute ( $JSON; ["\",\""; "\"" & ¶ & "\""]; [ "\"" ; ""]; ["[" ; ""];[ "]" ; ""]; ["{"; ""]; ["}"; ""]; ["dogs:"; "¶"])

    You'll have to be careful with the order of the subsitution, and make sure you account for all the situtations you do what a return character instead of a comma. 

  • […] The Soliant Consulting Blog fn_ParseJSON ( JSON ; node ; instance […]

  • […] The Soliant Consulting Blog fn_ParseJSON ( JSON ; node ; instance […]

Leave a Reply