Query current found set with Execute FileMaker Data API as JSON

[MonkeybreadSoftware]

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"; $$Result) ] 

 

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