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.
Question
How do I perform a calculation (in a formula field) that uses a value from a field from another record in the same table?
This topic has been closed for replies.
+2Hi 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.
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.
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.
Referencing to another table is easy to wrap my mind around. Referencing a table to itself seems really confusing.
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?
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?
+2Waldy,
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.
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.
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.
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.
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.