Help

Using Mapped Values in a Formula Field

Topic Labels: Base design Formulas
Solved
Jump to Solution
241 2
cancel
Showing results for 
Search instead for 
Did you mean: 
dhigbee
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

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:

Screenshot 2024-06-23 at 1.10.51 PM.png

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

Screenshot 2024-06-23 at 1.13.02 PM.png

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

Screenshot 2024-06-23 at 1.13.30 PM.png

Link to base

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

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:

Screenshot 2024-06-23 at 1.10.51 PM.png

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

Screenshot 2024-06-23 at 1.13.02 PM.png

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

Screenshot 2024-06-23 at 1.13.30 PM.png

Link to base

Worked great! Thank you!