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 ta...
Formulas and functions
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
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's avatar
    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's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew 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
      ------------------------------
      • PaulTria's avatar
        PaulTria
        Qrew Member
        4 years ago
        Hi Mark,

        Yes, we are planning to load Table A with multiple combinations based on Stem, Region and Country.

        However, the RecordIDs should retain the default QuickBase IDs. So, in our case, combining the (Stem RegionCountries) fields and making them to be the Key fields should not happen.

        Hi John,

        Only me(and some other Administrators) will be the only ones who update Table A, so the data integrity concern can be ticked off. The RecordId should still be the QuickBase genereated IDs and should not be changed. 

        The problem really is how we really can lookup the Dates values from Table A based on the Stem, Region and Country fields selected from the details table B. The Date fields(Date 1, 2 and 3) value should be looked so we can calculate the Calculated Date field in the details table.

        Thank you guys for your inputs, btw.

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