BPM trouble. Not allow part to be changed to Non-Stock if inventory exists

[rturrentine (Rick Turrentine)]

I’m working on a BPM to stop someone from changing a part from Stock to Non-Stock if there is inventory quantity of that part. I’m making a pre-processing method on the Part Update business object. I have 2 conditions, the ds.Part.Nonstock field is changed to True and my BAQ returns a line.
I set up my query for Part and PartWhse with a criteria on the PartWhse where OnHandQty > 0.
The BPM seems to fire on all parts instead of parts with just inventory.
I am guessing my query isn’t right. Any ideas? TIA

[rturrentine (Rick Turrentine)]

Self fix. I needed to link the ds.Part table to the ERP.PartWhse table. Once I did that the rules worked as I needed them to.

[Mark_Wonsil]

You may want to read this thread about the inefficiencies of linking memory tables (ds.) and database tables. It may or may not make a big difference, but just in case.

Joining "TT" records to regular DB records in BPMs

I am opening up this topic, because it was raised at #Insights2018. Please join in on the discussion if you have examples that prove this wrong!!! I just want to curb any urban legend that may be building out in the community based on misunderstood ideas. There was a recommendation made during one of the sessions that JOINING a TT record to a regular database record was bad practice. Instead of joining, you should simply add conditions in your query for any of those links between TT and non-TT…

[CSmith (Clint Smith)]

TL;DR

Agree, he could(should) have used his first query against the both ERP. tables and set a condition using the ds.Part.PartNum as a query condition on his first attempt and this will be more efficient in terms of the actual query code generated by linq against the SQL DB.

[rturrentine (Rick Turrentine)]

I read the thread and I get the gist of it but I don’t know how else to create the query to trigger the exception. Is there a better method?

[JasonMcD (Jason McDermott)]

You set a variable (like immediately after the Start widget) for the part number, equal to ds.Part.PartNum (or whatever it is).

Then in the query window

• Only have one table (Erp.PartWhse, I guess)
• Don’t use a ds (or tt) table
• Give the Erp table(s) a criterion of Part = MyPartVariable

[Edited for clarity.]

[rturrentine (Rick Turrentine)]

I get it now. I don’t use variables much in BPMs so it was a new learning curve but that works like I need it to. Thanks

[Ernie (Ernie Lowell)]

Gotta be carefui… NonStock also exists in the PartPlant table and can be changed there too.

[rturrentine (Rick Turrentine)]

Fortunately we only have 1 plant so I made the Non-Stock checkbox read only on the site tab. So they can only change it on the Part tab. Trying to account for all types of users.