Call a specific field from a specific child record from a Formula field in parent record?

  • 38
  • 1
  • Question
  • Updated 6 months ago
  • Answered
  • (Edited)
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.
Photo of Josh Weeman

Josh Weeman

  • 694 Points 500 badge 2x thumb

Posted 6 months ago

  • 38
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
If it's numeric fields you are trying to float up, then I would just a make a Summary Maximum (or min or average since you will only flag 1 child perm project as special building 1) where the building is flagged as special building 1.  

If you also need to float up text values, too up to the Parent Project,  I would keep it native and use a reverse relationship.
Photo of Josh Weeman

Josh Weeman

  • 694 Points 500 badge 2x thumb
So, still using summary fields?  Is there is no way to just call a specific value from a specific record into a Formula field using script?  

The summary field method has worked well for me so far, but it feels like there should be a cleaner more direct way to get at these values (like calling them directly into a formula)...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I have no idea if it can be done by script in a way that would also work on reports.  Often, these scripts only work on a form and I have be triggered in a non native method.

My own approach is to stick with native QuickBase whenever possible. The app will be a lot more supportable in the future if you do not needlessly use script.

I would save your scripting adventures for where there is not an easy native solution.
Photo of Josh Weeman

Josh Weeman

  • 694 Points 500 badge 2x thumb
Ah, I see.  I wasn't aware that some script will work on a form but break when trying to run a report.  Thanks for the heads up.  In that regard, summary fields do seem like the safer bet.

Thanks for the quick feedback.