Joining JobHead and OrderDTL to showing job costing and price history in excel

I cannot for the life of mean figure out this join process. Any takers?

JobHead.Company = JobProd.Company
JobHead.JobNum = JobProd.JobNum

JobProd.Company = OrderDtl.Company
JobProd.OrderNum = OrderDtl.OrderNum
JobProd.OrderLine = OrderDtl.OrderLine

1 Like

I am so sorry. I mispoke. That is great. I was talking about invoicedtl to get the price per the part.

I am trying different joins but as I was afraid of, it is not pulling the price because we receive everything to stock, then ship.

I am trying to show job costing and price paid. Unfortunately, the price isn’t the same throughout the life cycle.

Do you use Lots? Or Serial Numbers?
You may be able to use that.
Otherwise, nope.
I’ve worked with a number of companies (job shops) who don’t know their real cost of any job because they purchase to stock, receive jobs to stock and use standard cost. A few jobs can be walked through manually, but no BAQ.

1 Like

I can get the cost of the jobs. That part is the easy thing. The hard thing is to marry it up to the sell price of the part. We use avg costing (fyi) if that makes a difference. I am googling some ways to do a look up in excel with a date range, where x is the lowest and below the next date. I have 2 separate tabs. One for job costing. The other for the price. We shall see what I can pull out but so far no luck.

There’s no straightforward join to make it simple, but the PartTran table may help tie up costed transactions into and out of stock, because you can match up job to stock (and the associated job number) with the stock to customer (and the associated shipment number leading to the order number).

This was the approach I was looking for with an easy filter on transactions type. Thank you