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

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

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
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    4 years ago
    Right, my solution assumes that every combination of Stem Country and Region will be loaded.
    
    But the drop down could also be made Conditional so as to limit the choices to valid combinations.
    
    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------