Query current found set with Execute FileMaker Data API as JSON

Resources Tips and Techniques
,
1

Today we have a tip for you without using MBS FileMaker Plugin and in pure FileMaker. Let's say you like to query current records in your found set as JSON, how would you do this?

First you need the list of the primary keys and we like to use a While() for this. For older FileMaker versions, you could use FM.Loop instead. The loop goes over found set and just queries primary key fields for all records in found set:

While ( [ liste = ""; i = 0 ] ; i < Get(FoundCount) ; [ i = i + 1; liste = GetNthRecord ( FileMaker Ideas::PrimaryKey ; i ) & "¶" & liste ] ; liste )

We could store this in $Keys and then continue with building the request, which needs to include the layout to use and the query, which looks for all the records by ID.

Set Variable [ $Request ; Value: JSONSetElement ( "{}" ;

[ "layouts" ; "FileMaker Ideas" ; JSONString ] ;

[ "query" ; "[{ \"PrimaryKey\":\"==" & Substitute($Keys; ¶; "\"},¶{ \"PrimaryKey\":\"==") & "\" }]" ; JSONArray ]

) ]

Now we can just run this via Data API:

Execute FileMaker Data API [ Select ; Target: $$JSON ; $Request ]

Now you can query the result from the response:

Set Variable [ $$Result ; Value: JSONFormatElements ( JSONGetElement ( $$JSON ; "response.data" ) ) ]

And if you use MBS FileMaker Plugin, get some color to show JSON in a field:

Set Variable [ $$ResultColored ; Value: MBS("[JSON.Colorize](http://www.mbsplugins.eu/JSONColorize.shtml)"; $$Result) ]

Please try and let me know if you can use this in your development in FileMaker.

3 Likes
2

I like the concept, but I would tend to use a slightly different approach to building the request as I really dislike escaping double quotes - it makes code so much harder to read.

Set Variable [ $keyArray ; While ( 
[ 
   ~result = "[]" ;
   ~i = 1 ;
   ~iMax = Get ( FoundCount )
] ; 
   ~i ≤ ~iMax ;
[ 
   ~result = JSONSetElement ( ~result ; "[" & ~i-1 & "].id" ; 
                        GetNthRecord ( tickets::id ; ~i ) ; JSONString ) ;
   ~i = ~i + 1

] ; 
   ~result 
) ]

followed by

Set Variable [ $Request ; JSONSetElement ( "{}"
   ; [ "layouts" ; Get ( LayoutName ) ; JSONString ] 
   ; [ "query" ; $keyArray ; JSONArray ]
) ]

I am always torn between having to have a special layout to limit the fields returned using the DataAPI and using ExecuteSQL which doesn't easily return data in JSON format.

3

I'm going to have to play with this suggestion. It looks like something that I want to have as a part of my dev-tool-kit. :hammer_and_wrench:

4

I've been thinking about this and it occurs to me that if we are already traversing the found set with While(), why not just build the JSON we want

While ( 
[ 
	~result = "[]" ;
	~i = 1 ;
	~iMax = Get ( FoundCount )
] ; 
	~i ≤ ~iMax ;
[ 
	~item = JSONSetElement ( "{}"
		; [ "id" ; getnthrecord( customers::id ; ~i ) ; JSONString ]
		; [ "name" ; getnthrecord( customers::custName ; ~i )  ; JSONString ]
		; [ "contact" ; getnthrecord( customers::contactName ; ~i )  ; JSONString ]
		; [ "email" ; getnthrecord( customers::email ; ~i )  ; JSONString ]
		; [ "displayName" ; getnthrecord( customers::displayName ; ~i )  ; JSONString ]
         )  ;
	~result = JSONSetElement ( ~result  "[" & ~i - 1 & "]" ; ~item ; JSONObject )  ;
	~i = ~i + 1
] ; 
	JSONFormatElements ( ~result ) 
)
4 Likes
5

Hey @apjcs ,

Really nice to see you posting here. Thank you.

I would understand if you would not wish to do so from a "purity" point of view, but one variation that I see to the code above would be to iteratively build up ~result using string concatenation, rather than JSONSetElement, and then do any final tidying up of ~result in the final section of the while.

My motivation for suggesting this is just to get a little performance increase out of each iteration.

My MO for doing this sort of thing is usually to:

  • Start with an empty result string
  • Make sure that each item I am appending is a properly formed JSON object
  • Append each item with a comma prepended
  • After the loop, remove the initial leading comma (which is unwanted), then then surround the result in square brackets to create a proper JSON array.

Note that the Replace function works nicely for this last step, i.e.,


  Replace( ~result ; 1 ; 1 ; "[" ) & "]"
1 Like
6

Steve,

I don't object at all to string concatenation, I just dislike having to escape double quotes. I find it harder to read the code and also to debug. Perhaps a custom function to create "name":"value" would help me.

1 Like
7

Hi Paul,

Thanks for considering this. No escaped quotes required. I don't like that either, and I don't recommend trying to roll JSON by hand, as it is not robust enough for my liking.

The idea would look something like this:

While ( 
[ 
   ~result = "" ;
   ~i = 1 ;
   ~iMax = Get ( FoundCount )
] ; 
   ~i ≤ ~iMax ;
[ 
   ~item = JSONSetElement ( "{}"
   	; [ "id" ; getnthrecord( customers::id ; ~i ) ; JSONString ]
   	; [ "name" ; getnthrecord( customers::custName ; ~i )  ; JSONString ]
   	; [ "contact" ; getnthrecord( customers::contactName ; ~i )  ; JSONString ]
   	; [ "email" ; getnthrecord( customers::email ; ~i )  ; JSONString ]
   	; [ "displayName" ; getnthrecord( customers::displayName ; ~i )  ; JSONString ]
        )  ;
   ~result = ~result & "," & ~item ;
   ~i = ~i + 1
] ; 
   Let(
   	~result_as_array = Replace( ~result ; 1 ; 1 ; "[" ) & "]" ;

   	JSONFormatElements( ~result_as_array )
   )
)
8

Steve,

Thanks for clarifying. Your example make perfect sense and will certainly be faster on large found sets.

BTW JSONQuery is proving very useful, although I don't use it often enough and have to open the example file whenever I use it...

1 Like
9

Thanks for the reply and thoughts, about JSONQuery @apjcs.

Occasionally, I conclude that I made JSONQuery a little too feature-rich for its own good, thus increasing everyone's need to go back to the examples more than I wish were necessary. There are two sides to that, of course -- if it had a more concise feature set, would it still be helpful enough. Either way, I appreciate reading the above comment. Thank you.