Brian Dunning's FileMaker Custom Functions

padJSON ( JSON ; rowLabels ; columnLabels )

Pad out JSON string to two-dimensional array with null values

  Average rating: 3.8 (6 votes) Log in to vote

Mike Kupietz   Mike Kupietz
http://www.kupietz.com

Share on Facebook Share on Twitter

  Sample input:
"{\"A\":{\"1\":\"Hi\",\"3\":"there\"},"B":{\"1\":\"Hello\",\"4\":\"you\"}}"
  Sample output:
{\"A\":{\"1\":\"Hi\",\"2\":\"\",\"3\":there\"\",\"4\":\"\"},\"B\":{\"1\":\"Hello\",\"2\":\"\",\"3\":\"\",\"4\":\"you\"},\"C\":{\"1\":\"\",\"2\":\"\",\"3\":\"\",\"4\":\"\"},\"D\":{\"1\":\"\",\"2\":\"\",\"3\":\"\",\"4\":\"\"},\"E\":{\"1\":\"\",\"2\":\"\",\"3\":\"\",\"4\":\"\"}}

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

I needed a function to pad out JSON strings into full 2-dimensional arrays*, without overwriting existing data. For example, if:

$$JSON={"A":{"1":"Hi","3":"there"},"B":{"1":"Hello","4":"you"}}

then

padJSON ( $$JSON ; "A¶B¶C¶D¶E" ; "1¶2¶3¶4" ) =

{"A":{"1":"Hi","2":"","3":there"","4":""},"B":{"1":"Hello","2":"","3":"","4":"you"},"C":{"1":"","2":"","3":"","4":""},"D":{"1":"","2":"","3":"","4":""},"E":{"1":"","2":"","3":"","4":""}}

which is a full 5x4 array with existing keys for every permutation of the label lists "A¶B¶C¶D¶E" and "1¶2¶3¶4".

My reasons for needing this function are my own. I'm involved in some very dark JSON doings.

Note that this function has been needlessly complicated by the fact that FileMaker's value list functions insert garbage carriage returns... IE, rightvalues("A¶B¶C",1) is "C¶". So I I had to add the variables "theRightRowValues", "FileMakerFixRows", "theRightColValues", and "FileMakerFixCols" to work around this, marring the pristine beauty of my formerly elegant custom function. Oh well, it's a living.


(*Please note I am referring to an abstract tabular array of data, not a literal JSON array, which requires brackets. We apologize for any confusion caused. The parties responsible have been sacked.)

 

Comments

Mike Kupietz   Mike Kupietz, Bay Area, CA
Jul 7, 2017
There was a glitch in the first version I posted. It would return a JSON error on the last row if you passed it a null value "" in the JSON field instead of a valid JSON string. Fixed now.

For now, it will probably still choke if you pass it invalid JSON, but at least if you pass it nothing it will reply with an valid empty, initialized JSON array. This way you can auto-enter initialized JSON arrays into text fields on creation.
 
19752   19752, Japan
Jul 12, 2017
Terminologically, you don't use brackets [], so it is not array.
https://www.w3schools.com/js/js_json_arrays.asp
 
Michael E. Kupietz, FileMaker Consultant   Michael E. Kupietz, FileMaker Consultant, San Francisco, CA
Jul 12, 2017
It's not a JSON array. The data is a two-dimensional array, rows and columns of tabular data. In terms of the JSON structure, yes, it's stored in an ordinary JSON object, not a JSON array. I've added a clarification to that effect.
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.