I did what you said. First, we do not link our Jobs to our Sales Orders, so would that make a difference? Secondly, I received 3 lines of data - all with erroneous set-up hours and unit prices?
If you don’t link orders to jobs, then you have to make some assumptions about the unit cost. The unit cost only exists in the sales order line (OrderDtl). You either have to have a link already made (like in JobProd), or you have to make some assumption about the link. For example, a job is setup to run part 123456789. The part number has a sales order, line, and release, but they are not linked to your job as demand. In this case, as long as you only have one job, and one part you can try linking on part number. Then you can at least see the unit price from the order as well as the job details. However, without a solid link, you have to really trust that the assumptions you make are the same as the assumptions everyone else makes regarding which orders relate to which jobs.
We created UD fields in our Job entry to record the Sales Order # and Line number associated with the job.
This allow us to link job to Sales order and line.
1 Like
You may need some combination of subquery and outer joins, but maybe not. Est and Actual Costs are summed in JobAsmbl in (estimate) TLESetupHours, LLESetupHours and (actual) TLASetupHours, LLASetupHours, so if you filtered for AsmSeq 0 and your Jobhead UD fields get you to OrderDtl it can be done in just the main query.
I’ll look into that - Thanks!
I love the Table JobAsmbl cool way to get total set-up hours. However…when I add the OrderDtl Table I get three lines of data, two with an erroneous unit cost.
Any idea’s?
Do you have criteria for AssemblySeq = 0? Add the SysRowID of OrderDtl to see if they are the same or more details to see where the extra rows are coming from.
Ah…that was stupid of me…my BAQ is pulling EVERY order ever taken for the part number I’m specifying
1 Like
One more question then I’ll leave you alone!
If I want the BAQ to only pull the unit price from ONE SO, how would I do this?
If you have an inner join from your JobHead UD fields to the OrderDtl company, ordernum, orderline it should only give you one price.
Um…I don’t know how to use or define UD’s 
The UD fields only mater if you had set them up ahead of time to capture the relevant sales order information, like @lcousins did. In your case, there is no link to the sales order from the job. So you have to make one up using your tacit knowledge. The easiest link would be from Sales Order Line Part Number to Job Head Part Number. You will have to test to see if this linking works for you. It should give you one price per job, unless you have more than one sales order line with the same part number, with different prices. Then you have to decide how you want the system to choose the price. Do you want it to choose the lowest price? The first price based on release date? The average of all prices?
Sorry, I missed that it was @lcousins who had them. If @NateS link doesn’t work, I would make a subquery of open sales order lines grouped by company and partnum. @knash goes over how to do it here. You just need subquery 1. There is also a unit price field in the part master and if you have that you an just link to part and not use orders at all.
Working on these suggestions…I’ll let you know…
Interesting! How does the unit price get updated in Part Master? In our table, only a dozen parts have a price listed in the Part table.
user entry only.
1 Like
Ahh that makes sense. If we knew all of the unit prices for parts when we set them up ahead of time, then this would probably be filled in already. Since we set our unit prices at the sales order level, we have never needed to use the unit price at the part table.
I wonder how other companies use the unit price at the part table level.
I am using JobAsmbl to capture my Estimated and Actual Set-Up hours.
I am using OrderDtl to capture my UnitPrice
OrderDtl and JobAsmbl don’t communicate well…I have them Linked using PartNum. When I do this I receive four responses even though I have the calculated field Max(OrderDtl.OrderNum).
What am I missing?
Nevermind I figured it out - Duh! Geesh!
Thank you so much for your patience!
1 Like
Awesome.