Forum Discussion

PaulTria's avatar
PaulTria
Qrew Member
5 years ago

How to use another table for Calculated/Formula Fields

Hi, I created Table A which contains the fields below. Please see attachment for more details as well Stem(Text Field) Region(Text Field) Country(Text Field) Date 1(Number Field) Table A is ...
Formulas and functions
Relationships
MichaelTamoush's avatar
MichaelTamoush
Qrew Captain
5 years ago
If I understand the ask correctly, you need a conditional dropdown. Make a formula text field on both Table A and B, called [StemRegionCountry] with the formula [Stem] & [Region] & [Country].

In your related field, set the conditional value to where value in this field [StemRegionCountry] = TableA[StemRegionCountry].

Once you pick all 3, the related dropdown should only have one choice available. Using this method, you still need to select the related item from table A though. perhaps someone has a sneaky way to auto grab the date in a better way.

If you want to go the extra mile, to be sure the user cannot choose the dropdown until *all fields are selected*, either set that in a dynamic rule, or in your [StemRegionCountry] formula on table B, use an if statement like if([Stem]<>"" and [Region]<>"....., [Stem][Region][Country])

------------------------------
Mike Tamoush
------------------------------
PaulTria's avatar
PaulTria
Qrew Member
5 years ago
Hi Mike,

Thank you for the input! I did not realize we can use the concatenate function to be used for our conditional dropdown.

Yes, you are also correct that when using this method, we still need to select the the corresponding record under reference field. This is an additional step that might bug some users.

Hopefully, someone can still help in removing this added step.

In any case, the logic works. Thank you, appreciate the help!


------------------------------
Paul Tria
------------------------------