How to make a summary report from multiple tables?

I'm 99% sure this can be done natively, but I want to understand your relationships.

Is this the setup

Project < Tasks > Standard Assemblies < Item Assembly Assignments > Item Masters

1 Project has Many Tasks
1 Standard Assembly has Many Tasks
1 Standard Assembly has Many Item Assembly Assignments.
1 Item master has many Item Assemby Assignments.

yes that is the setup, item Masters being the 5th table in the mix

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.

Hi Ben,

Please let me know if the following data represents the data in your question.



Projects Table
----------------------
Project1
Project2

Tasks Table
Task Project         Assembly
--------------------------------------
Task1 Project1 Assembly1
Task2 Project1 Assembly2

Assemblies Table
--------------------
Assembly1
Assembly2

Items Table
Item   Assembly
-----------------
Item1   Assembly1
Item2   Assembly1
Item3   Assembly2
Item4   Assembly2


Find Items required for Project1
-------------------------------------
Item1
Item2
Item3
Item4




Thanks,

Neal


NealPatil @ gmx.com

This is awesome, many many thanks!

Let me know when you get it working ....

I got it working yesterday, this is exactly what I needed.  The only thing I didn't figure out was how to make the report from the Items table show up on the Focus table entry, which is ok.  I believe it's because there is no relationship back to the items table, but I don't know if there is a way to create a relationship to that record via a quickbase formula field.   When I tried to change the field type of the related focus table from a numeric reference to a numeric formula it broke the relationship between the tables.  The only way I can think of doing it is through a dynamic form rule, but that doesn't help me for all of the existing records.  But I guess I can grid edit and update those records, and rely on the form rule from there.

Ah, it is a common misunderstanding that you need a Relationship to create a Report Link field.  One has nothing to do with the other.  But when you build a relationship QuickBase in its infinite munificence, creates a report link field on the left side of the relationship for you as a convenience.

A report link field only gets configured with two fields.  What is the field on the current record i am matching on and what is the target app, and target field to match records in another table?  So, while you can make a relationship based on a 1 =  1,  you don't need to.  The focus record ID is record ID# 1 as there is and only ever be one record.  so on the Items table make a formula field with the formula of 1.  Then set up a Report link field using it.  The report initially show all items, but then make a report to use on the form of just illuminated items.

Oh cool, yeah this is perfect.  Thank you again for all your help!