« JSON with large integ… | Home | MBS Sessions at FileM… »

Sum up values in JSON with MBS FileMaker Plugin

We recently got a new FM.Loop function for our plugin. It allows you to run a loop in one plugin call and evaluate a lot of things in one line. For example you can loop over items in a JSON array and sum them up.

 

Lets take our first example. We have a JSON area with numbers from 1 to 10. You probably remember this sums 55 from school. The JSON.GetArraySize queries the number of entries in the array. With the loop function we loop from 0 to count-1 to query each of the items int the array and sum them up. For that we use a Let statement. In this case we first query the value with JSON.GetPathItem and than use $sum variable to add the new value. Take a look:

 

Set Variable [ $json ; Value: "[1,2,3,4,5,6,7,8,9,10]" ] 

Set Variable [ $count ; Value: MBS( "JSON.GetArraySize"; $json ) ] 

Set Variable [ $sum ; Value: 0 ] 

Set Variable [ $r ; Value: MBS( "FM.Loop"; "i"; 0; $count-1; 1; "Let([v = MBS( \"JSON.GetPathItem\"; $json; i; 1); $sum = $sum + v];0)" ) ] 

Show Custom Dialog [ "Sum" ; $sum ] 

 

For the next example we have a bigger JSON string with an array of objects. You may have got this from a web service. Like this list of prices for items:

 

[

  {

    "item": "1",

    "name": "Test",

    "price": 123

  },

  {

    "item": "2",

    "name": "Other",

    "price": 234

  },

  {

    "item": "3",

    "name": "Item",

    "price": 345

  }

]

 

Our script is a bit more complicated as we need to build the path to the item in the script with the calculation i & \¶ & \"price\". That takes the index of current array element, followed by ¶ as separator and price as the item to pick. This is the script with a lot of extra backslashes for escaping:

 

Set Variable [ $json ; Value: "[   {     \"item\": \"1\",     \"name\": \"Test\",     \"price\": 123   },   {     \"item\": \"2\",     \"name\": \"Other\",     \"price\": 234   },   {     \"item\": \"3\",     \"name\": \"Item\",     \"price\": 345   } ]" ] 

Set Variable [ $count ; Value: MBS( "JSON.GetArraySize"; $json ) ] 

Set Variable [ $sum ; Value: 0 ] 

Set Variable [ $r ; Value: MBS( "FM.Loop"; "i"; 0; $count-1; 1; "Let([v = MBS( \"JSON.GetPathItem\"; $json; i & \¶ & \"price\"; 1); $sum = $sum + v];0)" ) ] 

Show Custom Dialog [ "Sum" ; $sum ] 

 

We hope you enjoy hacking JSON like this with our plugin!

Claris FileMaker Plugin
11 05 18 - 16:14