Transforming Execute Data API Script results

Questions
,
1

Does anyone have a method of removing fieldData, recordId, modId and flattening out the portal data returned from the Execute as Data API script?

2

I've no experience with it, but if that's json (and I think it is) you could use jq command line, or use it thru bBox plugin.

2 Likes
3

You could use the JSON.Search or JSON.Query functions in MBS Plugin to select data you want and thus leave out the data you don’t want.

1 Like
4

There is a custom function that was built for these types of JSON querying operations. It won't have the speed that a plugin or Javascript will have, but the speed difference may or may not matter, depending on the size of the input.

For very large input sizes, the function will not be practical, and I would instead recommend an approach such as described in the posts above, e.g., plugin, or JavaScript. That said, in many cases the custom function is very useful, including having admirable performance.

Here is an image of what it looks like to perform the operation of extracting only the portal data across all returned records, and flattening that data.

JS_Example
JS_Example1423×1037 89 KB

The file in the image above should be attached here:
QueryExample_B.fmp12.zip (95.9 KB)

That file is using the beta version of the next upcoming version of the CF.

The current version of the custom function can be found on Kevin Frank's FileMaker Hacks site.

Edit: Updated the attached file to include a few more examples...

3 Likes
5

I was thinking about this some more, and realized that I kind of reached for a larger tool than might be necessary.

Writing a basic FMP calc to flatten out the portal children is not too difficult, partially because the structure of the Execute Data API output is very consistent/predictable.

The final JSONFormatElements call is, of course, optional. I included it just to sanity check the calc.

EDIT: Corrected calc to fix bug where empty portals were not properly handled.

While([

  ~input = $JSON ;

  ~source_array = JSONListValues(  ~input ; "response.data" );

  ~source_buffer = Case( Left( ~source_array ; 1 ) <> "?" ;  ~source_array ; "" );
  
  ~output_buffer = ""

];

  not IsEmpty( ~source_buffer );
  
[

  ~target_object = GetValue( ~source_buffer ; 1 );

  ~source_buffer = Replace( ~source_buffer ; 1 ; Length( ~target_object ) + 1 ; "" ); // Add 1 to capture trailing newline char

  ~child_values = JSONListValues( ~target_object ; "portalData.RelatedProducts" );

  ~output_buffer = List( ~output_buffer ; ~child_values )
  
];

  Let([

	~output_buffer_comma_delimited = Substitute( ~output_buffer ; Char( 13 ); "," );

	~output_array = "[" & ~output_buffer_comma_delimited & "]"
  ];

	JSONFormatElements( ~output_array )
  )
)
3 Likes
6 
While([

  ~input = $JSON ;

  ~source_array = JSONListValues(  ~input ; "response.data" );

  ~source_buffer = Case( Left( ~source_array ; 1 ) <> "?" ;  ~source_array ; "" );
  
  ~output_buffer = ""

];

  not IsEmpty( ~source_buffer );
  
[

  ~target_object = GetValue( ~source_buffer ; 1 );

  ~source_buffer = Replace( ~source_buffer ; 1 ; Length( ~target_object ) + 1 ; "" ); // Add 1 to capture trailing newline char

  ~child_values = JSONListValues( ~target_object ; "portalData.RelatedProducts" );

  ~output_buffer = List( ~output_buffer ; ~child_values )
  
];

  Let([

	~output_buffer_comma_delimited = Substitute( ~output_buffer ; Char( 13 ); "," );

	~output_array = "[" & ~output_buffer_comma_delimited & "]"
  ];

	JSONFormatElements( ~output_array )
  )
)

I couldn't get the code to produce a result. I ended up with an empty array.
Based on what you wrote I came up with this - which does not support portals.

While( 
	[	~index = 0 ;
		~inputArray = JSONGetElement ( json; "response.data" ) ;
		~outputArray = "[]" ;
		~count = ValueCount ( JSONListKeys ( ~inputArray; "" ) ) 
	]; 
	~index < ~count;
	[ 
		~node = jsongetelement(~inputarray,~index) ;
		~fieldData = JSONGetElement ( ~node ; "fieldData" ) ;
		~outputArray = JSONSetelement( ~outputArray ; ~index ; ~fieldData ; JSONObject ) ;
		~index = ~index + 1
	] ;
	JSONFormatElements ( ~outputArray )
)

This approach might be an easier way of handling portals.

Thanks for pointing me in the right direction :slight_smile:

1 Like