Line items from an estimate to a project

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a table called estimates that has a one to many relationship with a table called items. This allows our project managers to add line items when they are quoting an estimate. I want to have a table called Projects that takes the information from an estimate and turns it into essentially a work order. I have been able to populate the Projects with all the information that I need from an estimate, however, I cannot get the items from the estimate to the related projects. Any help is greatly appreciated.
Photo of Justin Pauga

Justin Pauga

  • 390 Points 250 badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
Are you trying to duplicate the Estimate line items as new records n the Work Order or just show those same records on the Work Order.
Mark
Photo of Justin Pauga

Justin Pauga

  • 390 Points 250 badge 2x thumb
I would like to just show the line items without the price information of the item as the project will go to our workers in the field and they just need the item name and description
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
Does your Work Order have the Record ID# of the estimate?  For example is it a child of the estimate?
Photo of Justin Pauga

Justin Pauga

  • 390 Points 250 badge 2x thumb
Yes, the work order is a child of the estimate


My gut says a project is parent to estimates, and Estimates is parent to items, (makes sense). Since it sounds like you want to "Clone" the same items to a project, I would send the project number down to Estimate, and again to Items. Once you've done that, let's say the field for this is called "Related Estimate - Related Project ID"

Once we have a way of "auto-pushing" the project to Items, we can then relate Items to projects, BUT before you save the relationship, set the "Reference" field to "Related Estimate - Related Project ID" rather than the default. 

Doing this will replace your typical "Drop-Down" on the items table with an automatic relationship to the same project that the estimate relates to.

Sounds harder than it is, but the next question is what other functionality will you need in terms of items now that they're attached to a Project/work order..., ie status, scheduling and resource assignments, Change orders, etc?

Hope this helps!
(Edited)
Photo of Justin Pauga

Justin Pauga

  • 390 Points 250 badge 2x thumb
Right now an estimate is acting as the parent to everything else. From the estimate I want to be able to create a project/work order that will go to the workers with the list of line items without the prices that need to be completed. I'll check into your solution and see if it works for what I have set up
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
If Justin has  a Work Order with the Estimate record ID# on it, then all he needs is a simple Report Link field and no relationship.  I'm just waiting for Justin to respond.
Yep, just working on the assumption there'd be additional data to capture beyond that, but I could be wrong.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
OK, so Yes, the work order is a child of the estimate
Then the answer is 

No Problem.

Make new field on the Work Order of Type Report Link.

On the when you configure the matching fields in the field properties, on the left side put the field probably called [Related Estimate].  On the right side navigate back to your app and then to locate the field in the table called something like Estimate Line items  and locate the field [Related Estimate]

Put that Report Link field on the Work Order form and set the FORM properties to show the records directly on the report.  Then make an unfiltered report of all Estimate Line Items and select the columns you want to show on the Work Order and the sort.  call it "used on Work Order' and make it visible to no Roles.

Go back to the FORM Properties on the Work order and set that Report Link field to use that new report.

Y'er Done!
Photo of Justin Pauga

Justin Pauga

  • 390 Points 250 badge 2x thumb
This worked perfectly! I appreciate the help!