Produce JSON for linked records

[Shaun_Hughston]

I am using Airtable to send data through to another platform (Formstack Docs aka Webmerge). My base has a main record with a number of linked records per main record.

The use case is that each item from the linked records will be a new row on a document. In order to do that, I need to send the data through to the document in JSON.

Is there a way that I can produce the JSON of the linked records with a script within Airtable? Ideally I could put that JSON into a long text field.

Failing that, is using a code step in Zapier the only other option?

Thanks
Shaun

[kuovonne · Accepted solution]

No scripting needed. Create the JSON with a formula field in the linked table and a rollup field in the current table.

[Shaun_Hughston]

@kuovonne I did the formula like this to create the JSON. It works, but I’m wondering if I went a long way about it…let me know if you would have done it differently?

CONCATENATE("{","\"","Full_Name","\""," ",":"," ","\"",Mem_Full_Name,"\"",",","\"","Address_1","\""," ",":"," ","\"",Mem_Add1,"\""," ,"," ","\"","Address_2","\""," ",":"," ","\"",Mem_Add2,"\"",","," ","\"","City","\""," ",":"," ","\"",Mem_City,"\"",","," ","\"","State","\""," ",":"," ","\"",Mem_State,"\"",","," ","\"","Zip","\""," ",":"," ","\"",Mem_Zip,"\"","}")

[kuovonne]

Shaun_Hughston:

about it…let me know if you would have done it differently?

CONCATENATE("{","\"","Full_Name","\""," ",":"," ","\"",Mem_Full_Name,"\"",",","\"","Address_1","\""," ",":"," ","\"",Mem_Add1,"\""," ,"," ","\"","Address_2","\""," ",":"," ","\"",Mem_Add2,"\"",","," ","\"","City","\""," ",":"," ","\"",Mem_City,"\"",","," ","\"","State","\""," ",":"," ","\"",Mem_State,"\"",","," ","\"","Zip","\""," ",":"," ","\"",Mem_Zip,"\"","}")

There are many ways to do things in code.
If your formula works, and you understand it, congratulations!

I find formulas easier to maintain when written across multiple lines. You also do not need to escape your double quotes if you put your strings inside single quotes. I also like including newline characters and other white space to make the result more human-readable.

Here is a sample of my coding style for the first two key/value pairs.

CONCATENATE(
  '{\n',
  '  "Full_Name": "' & {Mem_Full_Name} & '",\n',
  '  "Address_1": "' & {Mem_Add1} & '"\n',
  '}'
)

Note that this assumes that there are no quotes or other characters in the fields that need to be escaped.