Forum Discussion

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 ta...

Formulas and functions

Qrew Trainee

4 years ago

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
Qrew Member
4 years ago
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
  Qrew Legend
  4 years ago
  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
  ------------------------------
  - JohnCrosland
    Qrew Trainee
    4 years ago
    Mark, correct me if I am wrong, but the only way to do conditional drop down is by using table-to-table relationships, right?
    
    Paul, do you need it to be a cascading series of conditional drop downs? In Mark's solution, then I don't see how there would be a guarantee that combination exists, and I would think that would lead to data integrity issues. If you're using Record ID#s as the key field in Table A, then your users updating the Date 1 Value is going to present massive data integrity issues if you use that Calculated Date formula. Your users should only ever add a value into this type of table. In which case, you can have a Pipeline or an Automation Add it to the driver table(s).
    In the limited dataset it seems like Stem is the record ID and each region only has a single country. Is that correct? What are Date 2 and Date 3 used for?
    
    ------------------------------
    John Crosland
    Software Developer
    Inglett & Stubbs
    Mableton GA
    ------------------------------