Need to bring info from one table to another

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Acknowledged
I have the following tables and fields
Project
Project Name
Project #
Budget Hours
Budget Dollars
Tasks
Project Name
Project #
Task #
Task Name
Task budget hours
Task budget dollars
Labor
Task #
Employee #
Employee Name
Employee Budget Hours
Employee Budget Dollars
Actual Hours
Actual Dollars
Bill Rate
Remaining Hours

Employees
Employee #
Employee Name
Labor Rate

I want to bring the Labor Rate from employee table to the labor table. Then have the labor table calculate the labor cost by multiplying the labor rate by the actual hours.
I want to bring the total actual hours and actual dollars for all employees to the task table. Then bring all of that to the Project table.

I set to the table relationships and tried doing the lookup and summary (both ways) but regardless of what I try I can't get it to work. I also watched a bunch of videos but it still isn't working. Can someone please help??
Photo of BERNADETTE MERCER

BERNADETTE MERCER

  • 150 Points 100 badge 2x thumb
  • frustrated

Posted 4 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
Bernadette,
I offer one on one assistance to help my Clients to become more self sufficient with Quick Base. Feel free to contact me via the information on my website. QuickBaseCoach.com
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
Hi Bernadette,

If you are looking for some help with troubleshooting your relationship set up you can also get in touch with the Quick Base Care team via a support case. You can create a support case by clicking on the white question mark that appears in the upper right hand corner of your screen when signed into a Quick Base application and selecting Manage Support Cases. You can also open a support case directly here

One of the Care team reps can set up a screen share session with you and walk through the relationship and see if we can help you find the place where the information is not smoothly passing up where you would like.

Alternatively, could you give me a little more information on the relationship set up you have in your application? Do you currently have your relationship set up so that Projects have many Tasks and then Tasks have many Labor Records? Then an Employee could have many Labor Records as well so that everything sort of meets in Labor Records?
(Edited)
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
I can see part of the issue with that relationship information. Your relationship is being informed by a field that was created called Related Employee that brings down your Employee records entirely separate from your existing Employee Name field. The relationship isn't populated currently. You would need to edit that record and using the labor rate select the correct employee record and then see if it is bringing down the right values. 
(Edited)
Photo of BERNADETTE MERCER

BERNADETTE MERCER

  • 150 Points 100 badge 2x thumb
I got it to work!!!! you are amazing!!! now I need to figure out how to put in a calculation of the labor rate multiplied by the actual hours! 
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
Excellent I am glad to hear that worked. Setting up relationships can take some getting used to when you start adding look up fields and summaries. 
Photo of BERNADETTE MERCER

BERNADETTE MERCER

  • 150 Points 100 badge 2x thumb
one more quick question. can you tell me how I can add a field that will multiply the actual hours by the labor rate?
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
Hi Bernadette you would want to make a new field that is a formula-numeric field type. Then once you have that new field you can open up the field's settings and put in your formula which would look something like 

[Employee - Labor Rate]*[Name of actual hours field] 

Then you can see what results you get and if they are close to what you are expecting or if the formula needs some more adjustment.
Photo of BERNADETTE MERCER

BERNADETTE MERCER

  • 150 Points 100 badge 2x thumb
This is what I have in the Employee to Labor relationship. 
(Edited)
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
Does the labor rate display correctly for your in your Labor records under the Employee - Labor Rate field? Then from there when you use it in the formula field is the formula result coming up close to what you are looking for? From there once you have the formula field for calculating the actual labor cost do you have that field summarized up from the labor records to the related Task record as a summary field in the relationship between Tasks and Labor?