Can someone please help with a multi-table relationship?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I am trying to track actual labor invoiced vs. projected labor costs by project. I have 4 tables that all relate in some way: Projects Table- contains the name of the project etc., Rate Card Table-contains a line item by line item rate for each task, Invoices Table-pulls the project name from Project, the Item # and Description from Rate Card and calculates total invoiced based on quantity input,  the Projected Labor Table-contains projected costs per line item in the rate card per project (for example the line item A-1 (dig hole) appears once per each project, but each project only has one A-1). The invoices are input on a daily basis. I'm trying to create a "draw-down" report that shows projected labor costs vs actual invoiced labor costs on a per line item and per project basis. What I really need is to somehow summarize each invoiced line item on a per project basis in the Invoices Table and subtract that from the Projected Labor costs in the Projected Labor table on a per line item per project basis. I hope I have summarized this correctly I've been trying to figure it out all day and am now thoroughly confused!
Photo of Tom

Tom

  • 0 Points

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
This is definitely possible to do, but non-trivial. ie, it's not easy to see the changes required.

Depending on your QuickBase skill level, you may need a bit of one on one handholding to make these changes.

If this us a production app, I would first make a Cooy if the app and make your changes there in a test environment.

You will need to change the Key field of the project Lines table to be a compound field of Related Project - Related Rate.  E.g. If the project is Record ID 123 and the Rate is Record ID 10, it would be 123-10

Now that would be not nice to users to have to enter that, so the solution is to make a formula field do that

List("-", Related Project], [Related Rate])

Alas, but a formula field may not be a Key field if a table so you will need a form rule to populate a text field when the record us saved.  Ie, move that calculated resuktvinti a regular text field.

You will also need to populate that new Ket field initially for all existing records,

Once you do that, the rest us easy.  On you invoices, each invoice line needs to know the Related Project and Related Rate. Then make a similar formula field List("-", Related Project], [Related Rate].

Then use that as the reference field in the right side of a relationship to link each invoice line to its Project line!