Forum Discussion

WaldyDuester's avatar
WaldyDuester
Qrew Trainee
6 years ago

How do I perform a calculation (in a formula field) that uses a value from a field from another record in the same table?

I have a table that lists diameters of cylinders installed in a machine.  Each location where the cylinder is installed is a record in the table.  One of the fields in the location record is the currently installed diameter of the cylinder at that location.  I need to calculate the ratio of diameters on installed cylinders based with respect to another location record in the same table.

How do  reference a value stored in an 'installed diameter' field of another record in the same table to use in the new Ratio formula field in the location records?  The excel equivalent is doing calculations that reference a value in a fixed cell (who's value can change).

The formula would be:

Ratio = (Diameter value of current record) divided by (diameter value from different record in the same table)

Hope the above makes sense.
Formulas and functions
  • JeffRichey's avatar
    JeffRichey
    Qrew Trainee
    6 years ago
    Hi Waldy,

    You could create a table to table relationship where the table is related to iteslf. Doing this will allow you to pass the diameter of the second record to the first record so that you can do your calculation.


  • WaldyDuester's avatar
    WaldyDuester
    Qrew Trainee
    6 years ago
    Thanks for the quick reply but I'm confused.  How would you designate a specific reference to another record?
    Referencing to another table is easy to wrap my mind around.  Referencing a table to itself seems really confusing.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Waldy,

    Indeed a table can be related to itself. How do you propose that the record be linked as a child to the record with the the needed value for the calculation.  Does the record have the information to calculate the Key field of the Parent, or would the user need to select the parent manually?
  • JeffRichey's avatar
    JeffRichey
    Qrew Trainee
    6 years ago
    Waldy,

    I did some experimenting and came up with this.



    This is the formula I used for the Ratio field 
    [Widget 1 Installed Diameter]/[Related Widget Installed Diameter]

    I hope this helps get you started. To Mark's point, this solution requires the end user to manually select the related record, which could become cumbersome or difficult.  You will want to make changes to the Record Picker to make it easier to chose the correct related record. 

    I would look for a way to automate the creating the relationship, or at a minimum make the related Widget(record) field a conditional drop down.
  • WaldyDuester's avatar
    WaldyDuester
    Qrew Trainee
    6 years ago
    Thanks for the ideas guys.  In the end I went with a new table (Tachometers) and created double relationship between the two tables so I could look up information in two directions and then build a page that displayed a list of factors (ratios) based on search criteria.
    It's been about 6 years since I created the app and, seeing as I haven't edited the app since I created it, it took a long time to refresh myself on the relationship structures in quickbase.