Calculations on comma separated values

[MarioD]

I have a zap that pushes 3 cells with comma-separated values, the first one being the part or service names, for example [trip, labour] the second cell is the quantities [1, 3.5] and the third one the prices [60, 120] to our invoices table.

How can I extract that information and create a record in another table (line items) every time  a new record is added to the Invoices table.

[Ben_Young1]

Hey @MarioD! 

So, I was actually in the middle of typing out a few potential solutions when a small detail caught my attention.

How can I extract that information and create a record in another table (line items) every time  a new record is added to the Invoices table.

---

I'd love clarity on why you're writing the new records to this first table, instead of just directly to your Invoices table.
It seems like it might be an extra step in the process that adds an unnecessary data transfer requirement.
Additionally, suppose you're just creating a new record with identical information in a different table. In that case, you're compromising the integrity of your data model since you now have two (or more) places where duplicate data lives.

Now, barring the existence of those details, if I had to approach your problem as it currently exists, I would actually do it in Zapier.
Zapier has a growing set of data transformation tools that are not (currently) available within Airtable's automations without the use of scripting.
I stopped using Zapier a while ago in lieu of Make, but Zapier should allow you to take your comma-separated values (array) and parse its values.
Based on the results of that parse action, you can then iterate through and execute any actions you need before writing your final request(s) to Airtable.

[MarioD]

Thank you for your insightful reply.  I was not aware of Zappier having those features, however, my train of thought is that if we do it within Airtable, we would have fewer zaps.  For some invoices, we have upwards of 20 line items, and that could get costly, albeit I have not looked at how much more it would cost.  Is  Make a viable alternative to Zappier?  We have been investigating Pably Connect as well.

[TheTimeSavingCo · Accepted solution]

If you're on a Pro account you might want to just get someone familiar with JavaScript to write you a script that you can run on an automation instead; fairly simple given your requirements

[MarioD]

Thank you, that is exactly what I am working on at this time.  While I am far from an expert I am learning how to use Javascript and I think I got it mostly figured out at this time.