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
Script Breakdown
Here is a breakdown of the script (parsing part only, see sample file for full script).
- 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.
- 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.
- 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.
- 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.
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.
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.
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
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.
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.
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
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.
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.
Pingback: FileMaker: Getting Live Exchange Rates [Updated] • Information To Share
Pingback: FileMaker: Getting Live Exchange Rates [Updated] | Nick-chan