Jul 12, 2021 03:34 AM
Hello,
I’ve integrated Airtable with Pandadoc. Each time a client signs a quote, the information lands in airtable so that we can prepare the order to be shipped to the client.
However, the order information we receive is split in 2 columns :
On the left are the product references and on the right the order quantities for each reference. Is there a way to use ROLLUP or FORMULAS in order to have something that is more readable. For example :
V-1C * 1
CS-1C-24M * 1
CS-6C-24M *1
Even better would be to be able to display only the references we want. For example, if I want to display only the V-1C orders, I’d have only V-1C * 1 in the resulting field.
All we need is to easily understand the order in order to prepare it and ship it to the client. Thanks a lot for your help !
Jul 12, 2021 06:34 PM
Welcome to the community, @Corentin_Metgy! :grinning_face_with_big_eyes: Unfortunately Airtable doesn’t have any split-like functions in its formula system. If it did, this would be a trivial problem. As it stands, though, the best way to solve this would be using JavaScript. This is done either in the Scripting app, or in a Run script
action in an automation. If this data “lands” in a new Airtable record all at once from Pandadoc, an automation should do the trick. However, simply reformatting the data isn’t going to let you do what you want here:
Based on what little I know of your process, my gut feeling is that the automation should create new records in a [Line Items]
junction table, with each record linking to a product in a [Products]
table and listing its quantity. These would then link to a single order record in an [Orders]
table. Orders could be filtered on either [Orders]
or [Line Items]
to show you more details.
Does that sound like a viable solution?
Jul 14, 2021 05:28 AM
Hi @Justin_Barrett Thanks a lot for your reply !
You’re right, running a script seems to be the best solution. We’re going to investigate and we’ll keep you informed if it works :blush:
Cheers,
Corentin