Call a specific field from a specific child record from a Formula field in parent record?
I have a parent table, PROJECTS, in a one-to-many r'ship with its child table, BUILDINGS. (Each Project typically has 5 to 10 Buildings.)
Within the Project, I am doing many calculations on data rolled up from its associated Buildings (using summary fields), and some of those resulting calculations I pass back down to the Building records (via lookup fields).
In my next enhancement, I want to do a numerical comparison of many different fields associated to two specific Buildings that I will mark as "special building 1" and "special building 2" (via a checkbox attribute on the building record). While I could roll the values I need up to the Project record the same way I've done in the past, by building Summary fields that filter for that specific "special building" flag, I feel like there must be a better way to do this...
So I've I created two fields in the Project record, called "special building 1" and "special building 2", and I've used QB Automation to automatically populate the record numbers of my two special buildings into these two Project fields when the corresponding checkbox is checked on a building record. So now the Project record knows the record numbers of the two Buildings I want to compare. What I want to do next is create a series of Formula fields in the Project record, such as "Special Building 1 - Total Square Footage", and have it pull in the corresponding value from the Building record. Surely this can be done via script somehow? In the formula, I essentially want to say "pull the value from Field Number ###, from Record Number ###, from Table Code ########"
Any help or references to existing posts or code snippets is greatly appreciated!
TIA.
Within the Project, I am doing many calculations on data rolled up from its associated Buildings (using summary fields), and some of those resulting calculations I pass back down to the Building records (via lookup fields).
In my next enhancement, I want to do a numerical comparison of many different fields associated to two specific Buildings that I will mark as "special building 1" and "special building 2" (via a checkbox attribute on the building record). While I could roll the values I need up to the Project record the same way I've done in the past, by building Summary fields that filter for that specific "special building" flag, I feel like there must be a better way to do this...
So I've I created two fields in the Project record, called "special building 1" and "special building 2", and I've used QB Automation to automatically populate the record numbers of my two special buildings into these two Project fields when the corresponding checkbox is checked on a building record. So now the Project record knows the record numbers of the two Buildings I want to compare. What I want to do next is create a series of Formula fields in the Project record, such as "Special Building 1 - Total Square Footage", and have it pull in the corresponding value from the Building record. Surely this can be done via script somehow? In the formula, I essentially want to say "pull the value from Field Number ###, from Record Number ###, from Table Code ########"
Any help or references to existing posts or code snippets is greatly appreciated!
TIA.
