Need help doing a calculation across two unrelated tables that are both parents to a third table.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
The Projects table is parent to TimeDetails table.   The Resource table is also a parent to Time Details.   However, Projects and Resources are not related.   

When adding new TimeDetail records, we enter the related project, the related resource, the work date, and the number of hours worked.    

What I need to calculate is as follows >>>      
#1 - The total number of hours worked per resource per project.   
#2 - The total number of hours worked per project by all resources on that project. This one I have already as a summary field in the Projects table.
#3 - Divide #1 into #2.  This results in a percentage that each resource contributed to each project.  
#4 - Multiply #3 by the net profit of the project.  This results in a $ amount contribution to net profit for each resource for each project.   The net profit numbers would most likely come from the Projects table since total revenue and total expenses are already summed up there.  
#5 - Need a running tally of #4 for each resource.
#6 - Need a running tally of the total net profit for all the projects that each resource has worked on.
#7 - Divide #5 into #6 which results in a percentage SCORE for each resource that we will use to compare our resources.    

To make matters worse, we have 5 years of Project, Resource, and TimeDetail data that I would like to apply these calculations to as well as future data.  

I.know this is a lot, but any guidance would be appreciated as I'm having trouble wrapping my head around this one.  Thanks
Photo of Robin CC

Robin CC

  • 220 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
This can be done.

But, you will need to introduce the new "many to many Join table" called Resource Assignments.  

1 Project has Many Resource Assignments

1 Resource has Many resource Assignments.

1 Resource Assignment will have Many TimeDetail records - ie the drop down will need to be changed such that choice of the Resource is from those Resources assigned to the project and not just any random Resource.

So on a going forward basis, you would then be able to summarize the time worked per project per Resource, as that is exactly what a Resource Assignment record represents, a unique Project-Resource combination.

Perfect, well perfect except for that problem of 5 year of history that you are not so keen to recode by hand.

I would suggest making a summary report of the TimeDetail records by Related Project by Related Resource.  Import that into that new many to many join table.

Great, so now magically we have 5 years of correct Project Resource Assignments. But how to connect them to those historical TimeDetail records.

I think that to do that we will need a one time excel manipulation.

Dump the TimeDetails to Excel sheet 1 capturing the Record ID#, Related Resource and Related Project.  Rename  that Sheet TimeDetails

Dump the Resource Assignments Table to Excel sheet 2 capturing the Record ID#, Related Resource and Related Project. Rename that Sheet Resource Assignments 

On both Excel sheets a concatenated column in the format related Project-RelatedResource, for example it would look like 123-45 of the Related Project was 123 and the related resource was 45.

Do a on the sheet for the TimeDetails do a vlookup (using the ,false option)  of into that concatenated field and bring back the Record ID# of the Resource Assignment Record ID# into the TimeDetails sheet.

Now, from the TimeDetails Sheet import just the Record ID# and the Record ID# of the Resource Assignment record into the QuickBase TimeDetails table.

That it!.

This would need to either be done after hours when your users are gone or else temporarily put the users into view mode for entering new time details.

Feel free to contact me via the information in my profile if you want personal assistance to do this transition for you. 
Photo of Robin CC

Robin CC

  • 220 Points 100 badge 2x thumb
Thanks Mark.  I've tested everything you described  and all will work great in its self, but there is one wrinkle to the story.  Will it be possible for me to relate FUTURE TimeDetail records to both the Resource Assignments Table and the Projects Table?    The reason being is that we already utilize a variety of reports/fields throughout the app that are based on the relationship between the Projects table and the TimeDetails table.   Would I be stuck with revamping all of those reports and fields that we use in this regard such that they are based on the Resource Assignments table?   To make matters worse, the TimeDetail records are actually entered through an editable embedded report on the TimeSheets form of the TimeSheets table, so form rules aren't an option.
Well, either you will need to redesign the app to the new method or else from time to time you would need to erase that Resource Assignments table and follow these steps described to reload it and update the TimeDetails.  I makes more sense to work through resigning the app so that the information you want is always available, live.