Forum Discussion

PaulTria's avatar
PaulTria
Qrew Member
4 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 the parent table of Table B. Table B Contains the fields:
Date(Date Field)  -> User input
Stem(Text Field) -> Drop Down
Region(Text Field) -> Drop Down
Country(Text Field) -> Drop Down
Calculated Date(Date Formula field) - Current Formula is "AdjustMonth([Date],)"

Basically, the setup is when a user fills data for Date, Stem, Region and Country in Table B, the Calculated Date field should result in the Date based on the formula above. However, I cannot seem to select the correct record in Table A based on the inputs(Stem, Region and Country) of the user.

I have used the reference field for Table B, but it just provides a dropdown of all the records in Table A. This should not be the case as the reference field should be filtered based on the fields (Stem, Region and Country in Table B) which would select the respective record in Table A

As an example, a user selects the following in Table B:
Date = April 1, 2021
Stem = 1
Region = Region A
Country = Country A

The Calculated Date should result to June 1, 2021 which is 2 months(from Table A) after April 1, 2021 without manually adjusting the reference field in Table B. 

Hope this makes sense. Thank you for the help!

------------------------------
Paul Tria
------------------------------
Formulas and functions
Relationships
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    4 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
      4 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
      ------------------------------