Product code lookup and assignment upon form submission

[Gruss]

Howdy good people!

Hoping someone can advise with a product code lookup scenario. 

Our team sells training subscriptions. New subscribers submit airtable forms to notify of catalog onboarding. Each subscription is tied to a certain product (9 product types currently). Product name auto-populates in a multi-select 'Product' field upon form submission in an 'Offline Subs' table. Each product has a set unique code stored in another table (Product Codes). We need to auto lookup and match the product name with corresponding product code and auto populate this in a 'product code' field in the 'Offline Subs' table (see attached). 

Playing around with lookup fields and can't see an automation option, imagining that this will require a script to achieve?

I don't have any coding/scripting experience so hoping someone can share a similar successful script or use case experience. 

Any assistance greatly appreciated. 🙂

[AirOps]

Hi Gruss, 

My recommendation is to do one of two things - and you should not need a script to accomplish this functionality in either option!

The first and likely most simple solution is to convert your multiple select "Product" field on your form to a linked field that links directly to your "Product Codes" table. You would just need to ensure the primary field on your products code matches your options in your "Product" multiple select. From there you could easily lookup/rollup your product codes. 

The second solution I would recommend is an automation in the case that you do not want to convert "products" to a linked field in your form. To accomplish this automation, if you don't already have a field on your "Product Codes" table that matches your "Product" multiple select field exactly (all uppercase), you will need to create one. From there, trigger your automation on a trigger that is most suitable for your workflow. As the first action in your automation use a find records step to find the record on your "Product Codes" table where the "Product" selected in the form matches the "Product" field in your "Product Codes" table. Ideally this always returns 1 record, to make sure this is the case ensure you only have one record on product code table that matches each of your product options in the form. The next step of your automation would be update your form submission with either the product code, found in your find step, or to instead link the product code directly to your submission. (This would work great if your primary field on product codes was the code itself, but you can also always look up the product code from this link as well!.)

I hope this helps! I know this abstract, so please let me know if you need further clarification! 

Chantal 

[Gruss]

Hi Chantal,

Many thanks for your response and insight. Much appreciated. 

Option 2 is preferable as we have many dependencies reliant on the multi-select 'Product' field. Trying to configure the automation suggested but have a few further Q's:
> In creating the Find Records step, unable to select the Product multi-select field from the form table as this is greyed out? (see attached)
> Can you share examples of the Find and Update Record steps?

A general Q:  Will this option 2 approach enable product code matching for all 9 product types in a single automation? 

[AirOps · Accepted solution]

Hi Gruss, 

You are unable to select this field for because it is a multiple select field, Airtable wont be able to match records if your submission has multiple products selected, and therefore does not allow for you to select it in your automation set up (this is my bad for not catching this - sorry for the confusion). In your example the records all only had one product selected, is it possible for your form to be updated and to have this question be a single select? If this were possible you would be able to use method 2 described above. 

Assuming converting your multiple select to a single select is not feasible I would recommend the following automation instead: 

I would keep your multi-select as is in your form, and also create a linked field to your products codes table. On the products code table I would convert your primary field to match the options in your multiple select field exactly, with another field to host your product codes. Then you could set up an automation that pastes the selected options in the form directly to your linked fields. Finally, you can create a look-up for your product codes. Your automation would look something like this: 

Sign Up Table: 

Product Codes Table: 

As for your general question, yes! Assuming this is setup correctly this should work for all of your products and their codes (all options I have provided). 

Please let me know if you run into any more hiccups!

- Chantal

[Gruss]

Hi Chantal, this has worked fine.  🙂

Many thanks for the clarifications!