Forum Discussion

BrianHunot1's avatar
BrianHunot1
Qrew Cadet
3 years ago

Formula field that is unique to each record in a table

Is there a way to have a formula field be unique to and have different values for, each record? Each of the 40 records in our Locations table has fields that describe the location and these are unique to each record. For instance, the address field. However, the formula fields in this table are 'universal' by Quickbase design; the formula field has the same value and performs the same calculation across all records using values in fields that change with each record. Is there a way though to have the ability to put a different formula in the same field in each record so that it will use the right numbers for each individual record?

We have 40 locations that have tanks for liquid storage. Every location has different size tanks. We measure the tanks with yardsticks, such that each inch of height equals a specific volume. I made a simple CASE formula that will return a volume for each tank given an inch measurement. I found that the numeric entry fields would change with each record but the formula field was the same, because there can only be one value in that field for the table.

In another table, with a somewhat similar scenario, I put an IF formula in front of the CASE formula that would only perform the calculation on the field if current record matched a certain Location name. The problem is that I would have to create over 120 of these fields, once for each tank at each location.
The other solution I thought of was just to have a formula field that housed the IF and CASE formulas for each location all in one field. This field however would have thousands of lines of data, which seemed excessive. 

So I figured that I would ask to see if there was a simpler solution.

------------------------------
Brian Hunot
------------------------------
  • I think you need to build a table of your 40 tanks.  You probably have this already.

    Then you should have a child table under each Tank with the conversions from inches to gallons.  That way you don't need to have crazy long formulas and the conversions are exposed to users.

    Then the user would select the inches from a drop down list off the relationship and it would lookup the Gallons.

    This is an unusual situation but I think you will need the relationships both ways.

    You will need the relationships where 1 tank has many conversions.  Then you will need a relationship where One selected Measurement has Many Tanks.
    The latter relationship would be a Conditional Drop Down so that when selecting a measurement, it only offers up choices from the correct Tank.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • BrianHunot1's avatar
      BrianHunot1
      Qrew Cadet
      Hey Mark, I haven't quite built all of the tables and fields to make this work, figuring out the best way to do it. There are a couple of different facets in the relationships. I was considering relating the table to itself, but hadn't thought about conditional drop downs.
      I've been hoping I wouldn't have to make a bunch of tables or fields.
      It would be convenient to have a formula field that acted like a standard text field, but that doesn't seem to be possible. Is there a way to have a formula field reference the contents of a text field and use it as a variable so to speak for the formula. This can be done in Excel. It's really the same basic CASE formula across all the calculations. And the inches / measurement component is always a number between 0 and 99.

      ------------------------------
      Brian Hunot
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        Are you just asking for an example of a single hard coded IF statement to do the conversions all in one long formula.  Sure that is possible.

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------
  • Brian,

    In the long run a table of Tanks is going to serve you best.  I suspect there are other unique things about them

    Serial Numbers
    Government Inspection Dates
    Foundation Material
    Single Wall/ Double Wall
    Hazard Ratings

    and so on. 

    Having a formula in the table that calculates volume from its unique geometry is just one more part of tracking these for the long term.



    ------------------------------
    Don Larson
    ------------------------------