Combining informations from 2 fields, need help please ! :)

[Corentin_Metgy]

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 !

[Justin_Barrett]

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:

Corentin_Metgy:

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.

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?

[Corentin_Metgy]

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