Author Topic: pull job cost  (Read 1102 times)

KingConst

  • Member
  • *
  • Posts: 1
pull job cost
« on: February 12, 2015, 02:39:00 PM »
what QB query should I use to pull the total actual cost for a job (customer)?  I have the ListID stored in FileMaker, so I can ID the job, but i don't know where to look for the total actual cost.  In quickbooks I would go to the job estimates VS actuals summary  report and filter it for the customer I want.  at the very least I need to pull the total cost for that job, ideally I would like to pull the actual cost for a job by item, similar to how it's shown in the job estimates vs. actuals detail report.

thanks,
Tobin

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: pull job cost
« Reply #1 on: February 12, 2015, 04:09:04 PM »
The answer depends on several factors, and may be beyond the scope of a forum thread, but here goes...

The Items and Expenses on Bills can be tied to a Job, for example, and may be marked as billable. Assume that all of a Job's Expenses appear on one (or more) Bill(s), are marked billable, and have been added to Invoice(s) for that Job. In that case, an Invoice Query that includes the EntityFilter:ListID for the target Job and also includes the IncludeLinkedTxns element with a value of true will return all the expenses for that Job. Note that it may also return expenses for other Jobs. The Response can be parsed to get the total Expense for the targeted Job.

However, it's possible that all of a Job's expenses may not appear on a Bill, or be marked as Billable to a Job, or may not (yet) have been added to an Invoice. In that case, issuing a GeneralDetailReportQueryRq request to create a JobProgressInvoicesVsEstimates report and parsing out the expenses may be an option. If that report is not sufficient, there are other GeneralDetailReport types that may do the trick entirely or fill in any gaps. Because GeneralDetailReports fit a much broader range of use cases, it's what I would use if a client didn't have the architecture in their system to get these values any other way.

Parsing a GeneralDetailReportQueryRq's response can be very complex/inefficient if you are not well versed in XPath filtering. If you go that route and need some help, I've provided tech support for several of our developer/colleagues that significantly shortened their learning curve.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852