Processing data, splitting it into multiple tables, breaking apart cells, and tying it all back together with a reference field

This format is called JSON. In this specific case, it is an array of JSON values.

To get this into another table, I would recommend using a script and/or automation. If your data comes in with curly quotes like shown in your post, use a formula field to fix them. If your quotes in your data are straight you don’t need the formula.

REGEX_REPLACE({JSON Field}, "(“|”)", '"')

You can use a script like this one assuming:

1. The names of all the columns in your Locations table exactly match the properties of your objects (addr_1, city, state_prov, …)
2. All fields are text fields, except your boolean values (in this case, “is_hq”) which should be checkboxes
3. Your Locations table has a Link to Another Record field pointing at your original table and that field is named “Link to Original Table”
4. You want to click a button on each record to convert that record’s JSON into several linked records in the Locations table.
5. New records should always be created instead of merging duplicates where they may occur

let table1 = base.getTable("Name of Original Table")
let record = await input.recordAsync("Select record", table1)

let JSONtext = record.getCellValueAsString("Name of Field with JSON in it")

let JSONparsed = JSON.parse(JSONtext)

let updates = JSONparsed.map(x => {
    return {fields: {...x, "Link to Original Table": [{id: record.id}]}}
})

let table2 = base.getTable("Locations")

while (updates.length > 0) {
    await table2.createRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

The script would need to be adjusted if you want to (a) process the entire original table (or a view in your original table) in one go instead of record-by-record, or (b) run the script inside an automation.

REGEX_REPLACE({JSON Field}, "(“|”)", '"')

let table1 = base.getTable("Name of Original Table")
let record = await input.recordAsync("Select record", table1)

let JSONtext = record.getCellValueAsString("Name of Field with JSON in it")

let JSONparsed = JSON.parse(JSONtext)

let updates = JSONparsed.map(x => {
    return {fields: {...x, "Link to Original Table": [{id: record.id}]}}
})

let table2 = base.getTable("Locations")

while (updates.length > 0) {
    await table2.createRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

Wow, thank you!! I have a feeling this will take me a little while to dig through. Very much appreciated!

Oh wow! This is perfect!! As a non-scripter/coder myself, I was envisioning something exactly like this. Thank you so much :slightly_smiling_face: This seems like it’ll be perfect for my use case since I’m already using Make as the automation chain and adding this layer should fit in perfectly :grinning_face_with_big_eyes:

That’s great to hear! :grinning_face_with_big_eyes: :raised_hands:

This is working great for about half my columns, thanks Scott!

One follow up question:

I have some data that is formatted slightly differently, example:

[“Data & Analytics Consulting Competency”,“Machine Learning Consulting Competency”,“DevOps Consulting Competency”,“Financial Services Consulting Competency”,“Migration Consulting Competency”,“Digital Customer Experience Consulting Competency”]

The Parsing tool is picking each list item up as a bundle, but I am unable to link them to Airtable in the following step. I know this is probably approaching more of a “Make” help thread, but I appreciate any input.

Are you trying to combine them all together as an array to dump them together into one Airtable field?

There are many different ways of doing this, but the easiest way is to probably just use the Array Aggregator to combine them all into a comma-separated array that you can dump into an Airtable field.

p.s. If your business needs ongoing professional Airtable/Make technical support, and you have a budget to hire an expert Airtable/Make consultant, please feel free to contact me through my website:

Airtable consulting — ScottWorld