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...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
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
3 years ago
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
    Qrew Champion
    3 years ago
    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
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Qrew Champion
      3 years ago
      If you did want to have a giant formula it would look like this.

      var number TankOne =Case([Inches],
      1, 19,
      2, 116, etc);

      var number TankTwo =Case([Inches],
      1, 19,
      2, 116, etc);

      var number Tank Forty = Case([Inches],
      1, 19,
      2, 116, etc);

      Case([Tank],
      "Tank 1", $TankOne,
      "Tank 2", $TankTwo,
      etc
      "Tank 40", $TankForty)






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