Weetbicks Best of 2021: Get Record as JSON

Heads-Up! in the News
1

Hi all!

Number 2 in our Best of 2021 series is how to easily grab a record in JSON notation, with just a single script. A neat little tool which can aid in API integration as well as proving useful as a tool when trying to debug end user issues. You can check out the article below along with an example file that illustrates the technique so you can use it in your own solutions.

https://www.teamdf.com/blogs/get-record-as-json

Thanks for reading

Daniel

get-record-as-json-teaser

3 Likes
2

Well, I certainly joined this forum a few days too late. I actually wrote a custom function to do this just a few days ago, doesn't even need a script... you do need to pass it a single field, any field, from the record you want, just so it knows what table and record you are requesting. For instance, if you had a client table with a related address table, using the following function, you might have an unstored calc field clients::clientAsJson defined as recordAsJson(self). Or you might grab the first related addess as json with recordAsJson(addresses::_kp) or recordAsJson(addresses::street_address).

As it's intended to be used in calculations where we don't want to risk it causing a loop, and because I only plan to use it for duplicating or moving records into archives, it only grabs the data entry fields (number, text, date, time, etc), not calculated fields. This can easily by changed by modifying or removing ' AND FieldClass='Normal'.

Custom function definition:

recordAsJSON ( fieldForRecord) = 

Let (
        theFieldNames =           
                Case (
                        IsValid(GetFieldName(fieldForRecord)) = 0 and fieldForRecord="";
                         FieldNames (
                Get ( FileName ) ; Get ( LayoutTableName ) );
       IsValid(GetFieldName(fieldForRecord)) = 0 and fieldForRecord≠"";
                         FieldNames (
                Get ( FileName ) ; fieldForRecord );
ExecuteSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName='"&
GetValue(Substitute(GetFieldName(fieldForRecord);"::";"¶");1)
&"' AND FieldClass='Normal'";"";"")
                ) ;
        While (
                [ count = ValueCount ( theFieldNames ) ; out = "{}" ] ;
                count > 0 ;
                [
                        thisField = GetValue ( theFieldNames ; count ) ;
                        out =
                            JSONSetElement (
                                out ;
                                thisField ;
                                GetField ( Get ( LayoutTableName ) & "::" & thisField ) ;
                                ""
                                ) ;
                        count = count - 1
                ] ;
                out
                  )
        )

EDIT: Ooops! The site suggested this post to me, I just assumed the list at each page bottom was recent posts, and didn't notice the date on this until after I'd posted. Sorry to necropost.

2 Likes
3

Hi @kupietz, welcome the soup!
For a long thread it may be better to start a new one after a period of inactivity. This one here is rather short and no one gets lost :slightly_smiling_face:

4

+1.00