In SQL I produce job numbers for quick job entry using something like this (obviously with other criteria):
select
d.partnum,
d.orderqty,
CONVERT(varchar,d.ordernum)+'-'+CONVERT(varchar,d.orderline) jobNum
from
Production.erp.OrderDtl d
where
CONVERT(varchar,d.ordernum)+'-'+CONVERT(varchar,d.orderline) not in
(select jobnum from Production.Erp.JobHead)
Now automating this, and I’ve been fiddling with this on and off for days - haven’t been able to figure out how use a calculated field in a where NOT in select statement in a BAQ.
I’m telling everyone (with Great Sincerity ®) to stick to Epicor tools so I have to figure this out!
A real easy way to handle is to wrap up the main body in a subquery, then select your top query against it with your specific exclusion on the calculated field.
Ah yes, I’m an idiot. I Always forget how subqueries work. I didn’t do it exactly like you said but basically the same. And with the perennial disclaimer now shipping with E10 (and left in just to tease the worthy Epicoreans who often help out here), I have
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[SellingQuantity] as [OrderDtl_SellingQuantity],
(convert(varchar, OrderDtl.OrderNum) + '-' + convert(varchar, OrderDtl.OrderLine)) as [Calculated_qjJobNum]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.OpenLine = '1' and OrderDtl.VoidLine = '0' and OrderDtl.RequestDate <= dateadd (day, 5, Constants.Today))
and (not (convert(varchar, OrderDtl.OrderNum) + '-' + convert(varchar, OrderDtl.OrderLine)) in (select JobHead_JobNum from (select
[JobHead].[JobNum] as [JobHead_JobNum]
from Erp.JobHead as JobHead) as existingJobs))
You can also do a left join and return where the jobnum is null if you don’t want to do a subquery.
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[OrderDtl].[LineType] as [OrderDtl_LineType],
[OrderDtl].[PartNum] as [OrderDtl_PartNum]
from Erp.OrderDtl as OrderDtl
left outer join Erp.JobHead as JobHead on
(convert(varchar, OrderDtl.OrderNum) + '-' + convert(varchar, OrderDtl.OrderLine)) = JobHead.JobNum
where (JobHead.JobNum is null)
You can get it pretty close using the SubQuery method as @Aaron_Moreng suggested but was actually curious. You guys use the Order Number/Line number as the Job Number? That’s a pretty cool way to do it.
yeah, for years - it’s a real old issue with sales kits - they’re a great sales tool, but they only talk to stocked items, not made-to-order. So I made a dashboard that creates a grid matching the DMT fields for quick job entry, using ordernum-orderline as the job number. Every day the planner runs that dashboard, dumps it into excel and sends it to me (actually through a smartsheet form I insist on). I DMT it before MRP runs and MRP creates child jobs etc. If the job number exists, DMT fails and I just shoot the planners the error file.
Now I’ve made an Epicor function to create quick jobs and am just doing an updatable dashboard for the planners instead. But I want to flag unusable job numbers before calling the function, so they can just edit a character or check if someone has already manually created the job.
I actually thought of creating demand links so we could ship from WIP instead of inventory, but decided it didn’t meet my “do all surgery with a very sharp knife” criteria
