Split record into multiple records in new table

[TammyPaul]

Fingers crossed that there's a solution to this! I have data coming in from interface forms that is structured with headers like this:

Name 1 / Email 1 / Address 1  / Name 2 / Email 2 / Address 2.... and so on, all the way up to 100! Yes, 100!

We use this data to fill in PDF forms so it must come in as 1 row (at least with the app that I am using, I am using PDF Maker by Bustbrain labs).

My question is, is there a way to split this data into multiple records like this:

Name / Email / Address (header)
Name 1 / Email 1 / Address 1
Name 2 / Email 2 / Address 2

Also, I need the data to sync, so that if there's an update on the 1st table where the data is in a single row, it will also update the data in the split records.

I am open to a script, automation, syncing and duplicating tables, whatever I need to do to accomplish this. All the data I need is there, I just need it in a usable format!

[TammyPaul]

Found the Table Optimizer script under extensions that turn long records into individual data points. This should do what I want it to do. But I am getting this error message:

O: Can't create records: invalid cell value for field 'Fed ID'.
Cell value has invalid format: <root> must be a string
at main on line 91

I made sure that the fields on both tables are the same, single line text, so not sure what is causing this. Any help would be appreciated.

Also, would this script automatically update the receiving table when the sending table is updated? And will it automatically run?

[ScottWorld]

So you’re saying that you have 300 fields on your form?

That’s not a good way to set this up because of many different problems, including the problem that you’re facing now.

But just for a moment, let’s stick with the idea of you having 300 fields on your form. 

Airtable’s automations can’t natively handle that unless you know how to write JavaScript code, but you could solve that with Make’s advanced automations for Airtable.

However, you would have to create an automation with 100 conditional paths to create 100 records in Airtable. That would take a long time for you to set that up, and it would be very inefficient.

The best way (and the real way) to solve this problem is to solve it at the source of the problem, which is the form.

Whenever you have something that repeats multiple times, the solution typically involves creating that thing JUST ONCE and then letting the tool repeat it up to whatever number of repeats is necessary.

So I would recommend solving this by using JotForm's advanced forms.

JotForm offers a widget called the configurable list widget, which will let your users enter multiple repeating rows on a form, but you only need to setup one row. 

After a form is submitted, you would then use Make's JotForm integrations and Make's Airtable integrations to loop through all of the rows from JotForm to bring them into Airtable as separate records.

The reason that you will need to use Make is because all of the rows will be coming in from JotForm as raw JSON, so you will need to use Make's Parse JSON module to loop through the rows.

Take a look at the 2 screenshots below to see how absolutely simple this is to setup in Make.

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld

 

 

[TheTimeSavingCo]

Hmm...would you need to modify the data in the second table?  If you don't need to modify it at all you could potentially do this with (a LOT) of lookups and a single automation that triggers when a new form gets submitted

--
I'm also curious as to why you need the data to update actually, specifically, given how hard it is to read the data when it's in a single row in the sending table, how are edits being made to it, you know what I mean? 

===
re: Table Optimizer script

If you could DM me an invite link with some example data I could take a look at the error for you!  It wouldn't automatically update the receiving table when the sending table is updated though, and you'd need to modify the code so that it could run in an automation if you wanted that