Using Mapped Values in a Formula Field

[dhigbee]

I have a “Campaign Name” table that contains a “Product” field (which uses abbreviations).

The “Campaign Name” table has a formula field which uses the “Product” values (along with other fields) to generate a campaign name.

Our request form (on the “Campaign Name” table) uses the “Product” field, which shows the abbreviations in a picklist. You can only choose one Product per campaign.

I have created a new “Products" table which maps the Product Codes (abbreviations) with full Product Names.

For example:

Product Code	Product Name
WDG	Widget

What I would like to do is have the form display the Product Name to the user but still use the Product Code when generating the campaign name. 

Is this done just with Lookup fields, or is another formula required to do the mapping? And then what product field am I referencing in the existing formula that generates the campaign name in the "Campaign Name" table?

[TheTimeSavingCo · Accepted solution]

re: What I would like to do is have the form display the Product Name to the user but still use the Product Code when generating the campaign name

I would suggest that you convert the primary field of your 'Products' table to be the Product Name instead, and then create a lookup field in the 'Campaign Name' table to display the Product Code so that you can use that for your Campaign Name generation.

The reason I suggest this is that you want to display the Product Name in the form so that your users can select that easily, and we cannot display lookup values within the form natively.  (You can do this with some third party form tools like Fillout.com though, so if changing your base schema isn't an option let me know and I can show you how to do that!)

Specifically, assuming your campaign name is the Product Code + the year, this is how your data looks now:

And after you swap stuff around it'd look like this:

And in your form your users would be able to see the Product Name:

Link to base

[dhigbee]

Worked great! Thank you!