Solved

PO Line/Release Ordered to Job - Simple (?) Query

rfreeman

All:

I may be having a major brain malfunction.  LOL

I have a request to build a simple dashboard of all the Job Materials that are Buy to Order, providing the PO Number.  Essentially, looking at all Open/Released Jobs, I want a list of all the BTO Job Materials and provide the PO Number if available - this would indicate if the PO has been placed for the material.

I keep circling back to JobMtl and PORel (JobNum join), but I keep getting additional erroneous records along with the one correct row.  It needs to be an outerjoin to pull in the Job Materials that do not have PO associated with it yet.  It would be ideal if PORel referenced the PartNum, I know that’s the problem.  

I don’t know if I am tackling this the right way.  It’s probably (hopefully) something simple that I’m not thinking of as I’ve built several dashboards using the Job Tables, etc.  I’m just drawing a blank at the moment.  

Any suggestions?

icon

Best answer by mdewey 5 May 2021, 00:47

View original

2 replies

rfreeman

Think I found something - WHICH TOTALLY makes sense:

 

 

M

I did one that looks like this:

for each JobMtl no-lock  where  JobMtl.BuyIt = True  ,  

each JobHead no-lock  where (JobHead.Company = JobMtl.Company and JobHead.JobNum = JobMtl.JobNum ) and  JobHead.JobClosed = False And JobHead.JobReleased = True  ,  

each PORel no-lock  where  PORel.OpenRelease = True  outer-join where (JobMtl.Company = PORel.Company and JobMtl.JobNum = PORel.JobNum and JobMtl.AssemblySeq = PORel.AssemblySeq and JobMtl.MtlSeq = PORel.JobSeq ).

Reply


Terms & Conditions