Report generated in one child table that needs data from the master and another child of the master.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
My master table is called projects. Each project has many invoices and many foreman assignments. Foremen are measured by tracking how much revenue they generate during a date span. I need a report that shows me the invoices that went out during the date span that were for projects assigned to a particular foreman. I envision <ask the user> for the foreman name, date on or after and date on or before. The Foremen table is also a master to foreman assignments. Column headings would be Project Name, Project #, Invoice date, Invoice Amount. Group and sort by Foreman name. I am stuck. Thanks for any ideas.
Photo of coachDave

coachDave

  • 0 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Interesting quiz.

I can see two native solutions assuming that the Foremen Assignments each only last a single day.

The easiest way would be that when you enter an invoice, you are required to choose a Related Forman. There is probably a way to limit the choices to the firemen who were assigned that day to that project. In other words there would only be one choice.

A step beyond that would be to change the Key field to the Foreman Assignments table to be a compound hyphenated Key of Related Project - Date. That would be first calculated by formula and then by form rule, it would copy to a data entry field so the key field was correct when saved.

Then on the invoice record you would calculate that same value of Related Project - Date and thus be able to make invoices be children if the Foreman Assignment table and thus lookup the Forman.

Then you could run you report off the invoice table simply based on <ask the user> on date ranges
Photo of coachDave

coachDave

  • 0 Points
Thanks Mark. The second option seems to be my best shot.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
OK, let me know if you run into any problems  You will need to create that field, grid edit copy the new Key field. I suggest trying this first in a Copy of the app or at least making a fall back copy before you make these changes.