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(please see attachment for more details):
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 after April 1, 2021 without manually adjusting the reference field in Table B. 

Hope this makes sense. Thank you for the help!




------------------------------
Paul Tria
------------------------------
  • Good morning,
    You will need to make each drop down selection into it's own table, and make each of them properly linked to the others as Parent Tables, and then inside of the reference field on the child tables then make them dependent on the selection in the field above.

    {Regions} -< {Country}
    {Regions}-< {Stem/Table A}
    {Country}-< {Stem/Table A}
    All 3 need to be Parents of Table B

    Good luck,
    John

    ------------------------------
    John Crosland
    Software Developer
    Inglett & Stubbs
    Mableton GA
    ------------------------------
    • PaulTria's avatar
      PaulTria
      Qrew Member
      Hi John,

      Thanks for the help. However, I need all the fields (Region, Stem, and Country) in only 1 table. The reason for this is that our team can just update the single parent table for simpler maintenance.

      ------------------------------
      Paul Tria
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        Paul,
        You seem to be saying that you have a table of A's (Let's call them Stem RegionCountries) and you will load that table with every single possible valid combination.

        Then when the user is entering a detail Table B record, once having chosen the Step Region and Country, the  "Date Adjustment" value of the # of months should lookup automatically.

        You can do this by making a formula field in the Table A Stem Region Countries which a calculates a Key field.

        For example a field called [Stem Region Country formula]

        List("-", [Stem],[Region], Country])

        Then copy that value into a text field called  ​[Stem Region Country] using grid edit copy paste.

        Make a form rule to populate the [Stem Region Country] from the formula field  

        Make that the Key field.

        Then on the details side make that same formula for the Key field and use that to make a relationship. 

        The effect should be that once the child record calculates the Key field of the parent, the values will look up with no further selections required.


        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------