How to make a summary report from multiple tables?

  • 1
  • 4
  • Question
  • Updated 3 years ago
  • Answered

I have four tables.  A Project table, a Task table, an Assembly table and an Item table.

Each record in the Project table has many related tasks.

Each record in the Task table has a numeric field dictating the number of assemblies needed for the task. It also has a dropdown to choose an assembly from the Assembly table.

Each record in the Assembly table has a list of many related items from the Item table.

Each record in the Item table has a field dictating the number of items needed to make the assembly it is related to and the Item description.

I want to make a report that summarizes the total number of each unique item descriptions needed to complete the project.

So: For each task, total the # items individually per assembly multiplied by the # of those assemblies needed for the task.  Then combining the totals for items of the same description across all of the tasks of the project.  Is there a way to do this either within native quickbase or using external tools?  If external tools are required do you have any recommendations of what to use?

Photo of Ben

Ben

  • 30 Points

Posted 3 years ago

  • 1
  • 4
Your luck.  I love these puzzles!

I will explain the low tech solution which is probably good enough unless you have many simultaneous users hammering away at this. (In which case I have a slightly enhanced setup).


I call this my "shine the light" solution.  We will shine the light across the relationship from left to right.

Make a single record in a single table called Focus Project.  Create a numeric field.  Set the table permissions so no one can add another record.  The record will be record ID# 1

Make a formula field on the project to update that record with the project record ID# and then display the Focus project record ([Record ID #1])

(In a separate discussion I can help you with that if you need help.

Make a field on projects as a formula numeric formula field with a formula of 1 and make a relationship back to the Focus project record.

Lookup the focus project down to the projects record.

Enter a value in the focus Project record for a test project record ID.

Make a formula field on the Projects field called [Project is in Focus] with a formula of 

if([record ID#]= [Focus project record ID#],true)

Check that the Focus Project record is "lit up", ie [Project is in Focus] is checked! 

Lookup up that [Project is in Focus] field down to the Tasks records.  The Tasks for the Focus Project are also now lit up! 

On the Standard Assemblies table create a Summary field of the [Total # of Assemblies Required for Focus Project] for illuminated tasks, ie subject to the filter that  [Project is in Focus] is checked.  Each respective Std assemblies now know how many Assemblies are required for the Focus Project!

Look up the [Total # of Assemblies Required for Focus Project] down to the items Assignments.

On Item Assignments, make a calculated field of the [Total # items required] with a formula of [Total # of Assemblies Required for Focus Project]  * [Items Req'd per Assembly]. Now the Item Assignments each know how many are required!  but we have duplicate items.



Summary the Total # of items required up to Items.  Now the Items know how many are required!



Lastly to make this elegant, on the Focus Project record table, make make a report link field to show all items on that record.  The record ID is 1 =so just make a field on the Item table with a formula of 1.

Put the report link field on the Focus project record and show the embedded table on the form.  Set up a report to filer where # items required >0! 



So when you enter the Focus project record ID# and Save, the light will shine form left to right and then the record you are sitting on will display the items counts required as an embedded table for the focus project.  You will probably also want to do a relationship between the focus Project record and Projects to bring across the Project name.