Table to Table with math
First post here! Been working with QB for awhile. We have a table, call it table A with some costs. Table A has a child table, table B. Costs are entered in table A. There's another table...
Forum Discussion
Ok, let's name them.
I have these four tables: Buildings, Building Cost Records, Sites, Site Cost Records
Sites to Buildings is one to Many
So, I spend $50,000 at the Site. Which has 5 Buildings. I add that $50,000 to Site Cost Records.
I want that $50,000 to go look up the buildings it's tied to, and divide evenly out to the the buildings. So 5 $10,000 records are added to the Building Cost Record table.
------------------------------
Jeff Hughes
------------------------------
I have these four tables: Buildings, Building Cost Records, Sites, Site Cost Records
Sites to Buildings is one to Many
So, I spend $50,000 at the Site. Which has 5 Buildings. I add that $50,000 to Site Cost Records.
I want that $50,000 to go look up the buildings it's tied to, and divide evenly out to the the buildings. So 5 $10,000 records are added to the Building Cost Record table.
------------------------------
Jeff Hughes
------------------------------
OK, so a Pipeline can do this.
But before we do that make sure that the Site has a summary field for the # of Buildings and look that up down to Site Costs. So The site cost in your example knows there are 5 Buildings. Create a new field called Building Allocation which will be the $50,000 / 5 = 10,000.
Trigger on New Event for the Site Costs table, but for now within that, only set trigger on record added.
Search for Building where the Related Site matches the Related Site of the Trigger record.
Then "For Each",
Create a building cost record being sure to attach it to the correct [related building] the result of the search step (probably {{b.id}}
and populating the field for the $10,000 Building Cost from the trigger record.
​
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
But before we do that make sure that the Site has a summary field for the # of Buildings and look that up down to Site Costs. So The site cost in your example knows there are 5 Buildings. Create a new field called Building Allocation which will be the $50,000 / 5 = 10,000.
Trigger on New Event for the Site Costs table, but for now within that, only set trigger on record added.
Search for Building where the Related Site matches the Related Site of the Trigger record.
Then "For Each",
Create a building cost record being sure to attach it to the correct [related building] the result of the search step (probably {{b.id}}
and populating the field for the $10,000 Building Cost from the trigger record.
​
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Ok this sounds perfect and I follow! I'll try it out. Really appreciate your insight.
Jeff Hughes
Founder
PointFlow Companies, LLC
Phone: 918-693-1835
Email: jeff@pointflow.co
