BPM to Check Issued Qty at Job Completion

I’m trying to create a BPM that will check to make sure the all job material has been issued before the job can be completed. I’ve tried creating a Pre Processing directive on both the Pre Close Job and On Change Job Completion methods. The statement I’m using is:

The ttJobMtl.IssedQty field of all rows is less than the ttJobMtlRow.Required Qty

If the statement is true I have it set to raise an exception. I’m not receiving any errors but the BPM doesn’t stop me from completing the job when I test it.

Any ideas what I could be missing?

To confirm the method that you’re tagging is being called at all, can you try connecting your start and the raise exception block in your test environment?

Once you’ve confirmed that the method is firing and the exception is stopping the job closure, then we can take a look at the condition.

I connected it directly to the Raise Exception block and my error message did pop up.

Try putting in a C# block with no condition or error blocks and paste in this code on the first line:
throw new Exception(String.Format("MtlRowCount: {0}",ttJobMtl.Count.ToString()));
Click on details down in the inner exception to see if “MtlRowCount: 0” or if there is more than one row. My hunch is that the ttJobMtl table is not populated when that method is called, which is why your condition is evaluating to false.

Yes, this is what came up in the Inner Exception: MtlRowCount: 0

Okay, great. I figured I’d take this a step further to be helpful. I think you’ll find that the ttJobClosing will have rows in it, but the ttJobMtl does not. So, what you’ll want to do is to write the query like this:

The number of rows in the (query) is more than 0 will give you the error condition in this situation. We assume we’ve got one ttJobClosing record and we want to join it to JobMtl that exists in the database. Typically in a BAQ we join the two tables on Company and JobNum, then have the criteria IssuedQty < RequiredQty. However, due to a best practices discussion from last Insights, you’ll want to put the join within the table criteria for better performance. The details behind that have to do with the way Linq joins objects with the dataset, which would return too many records from JobMtl to work, so if you pop in the criteria like I have it there, it should work. As a disclaimer, I didn’t actually test this.

Thanks for putting that together for me! My error message came up but when I issued material and retested the error is still there.

Can you write a BAQ to examine the IssuedQty and RequiredQty fields on all JobMtl rows for the Job you’re trying to close in testing?

It’s just one that I’ve tested so far. I set up a job in our TEST environment and tested it after issuing QTY:

image

Upon closer inspection, the code calls PreClose, then Close, so you’ll actually want to put this directive on close, and focus on updated rows in ttJobClosing where JobClosed = true. However, the selection criteria within the criteria query seemed to work for me. Try this out [in your test system] and let me know if it works.JobMtlNotIssued.bpm (30.4 KB)

1 Like

This worked! Thank you so much for your help, I really appreciate it!