Skip to main content
Solved

Produce JSON for linked records

  • April 22, 2021
  • 3 replies
  • 95 views
S

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

Best answer by kuovonne

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

    3 replies

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • Answer
    • April 22, 2021

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

    S
    • Shaun_Hughston
    • Author
    • Participating Frequently
    • 9 replies
    • May 3, 2021

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


    @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
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • May 3, 2021

    @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,"\\"","}")

    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.


    Terms & ConditionsAccessibility statement