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?
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.
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.
Please let me know if the following data represents the data in your question.
Task Project Assembly
Task1 Project1 Assembly1
Task2 Project1 Assembly2
Find Items required for Project1
NealPatil @ gmx.com
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.