BPM unchecking Purchase Direct on JobMtl

I made this BPM today that unchecks PurchaseDirect (dspBuyIt) if the material is in stock. What I don’t like is that it is currently setting RowMod = U for all the rows, not just the ones I am changing. I don’t see another way how to set the RowMod, as this column isn’t available in the Set Field widget.
Am I missing something, or is this an acceptable way to do it?





image

Also, I don’t see how the MyQuery on the Update Table by Query has any effect… can someone explain how this is supposed to work?
Thanks much as always

Hi Terry,

A couple of thoughts. First, if the standard handling of the part is to stock it, wouldn’t it be good to uncheck nonstock, so you do not get purchase direct on it on jobs?
Second, could you get the mtl seq for the job similar to your getjobbyid and use it as criteria in your query update to force update only on subject material? I haven’t used the RowMod binding like you have before… IDK if that works like that.
I have difficulty getting the query update via configured mapping to work too, but once I’ve gotten it, I have found it working fast and robust. This one below just takes a UD field on the part and defaults it into the PO line. You might want to put a condition first so that it only runs when you have buy direct checked.

It is on the preprocessing POUpdate. Pehaps it could be helpful to see use of query to update w configured mapping working example.

Nancy

1 Like

Do not join ttTable to a Real table. Epicor will pull in ALL Database records into memory before it filters it.

As for will Epicor ever fix the behind the scenes generated code… unsure cc: @timshuwy

Thanks Haso;

Ah ~ no attempted good deed goes unpunished! :rofl:
Obviously, I have this here and have never gone back and fixed after the Jose post telling us not to do this (years after it was done and working ok with no noticed slowdown or complaint). I promise my newer bpms don’t do it!
So Terry, please ignore my tt to table join, but maybe the example bpm use of “use the query to update rows of table with configured mapping” might be helpful.

Nancy

1 Like

Most folks won’t notice it. I did the exact same thing at a Company with a database size of 20GB I mean 5 seconds was ok.

You start to notice it when you have a 200-800GB database and 100+ Users. Then it’s 45 seconds vs 3 seconds.

1 Like

@Nancy_Hoyt thanks much for sharing. I see how you’re using the UpdateTableByQuery, and I got that working now, making my BPM simpler and logical. Thanks! Here is what I am now using:


I don’t see how to just get one material from the job, only the whole job. There is a SearchMtl BO, but that wasn’t a fun rabbit hole to go down (can’t use it to update anything).

Question - if you are not using RowMod=U, how are you pushing your changes to the main database? Your example just shows updating the temporary table?

Also FYI - we’re a highly custom shop and most all of our parts are unique to the job and thus nonstock, ie we buy them all to a job. So our materials add into the job defaulted to PurchaseDirect. Still, on a job with say 500 parts, maybe we have 15 of them available as leftovers from prior jobs. We then identify the on-hand parts and manually uncheck BuyDirect. So I think what we’re doing makes sense given the way I understand Epicor handles stock vs nonstock. Just automating that now. If curious, the BPM triggers when the job is scheduled.

@hkeric.wci thanks for advice and links on the joins! If I was just doing this one material at a time then I could do it with a variable. But as I’m doing the whole job at once, and my filtered PartBin table only has a couple thousand rows (compared to ~500 on the job), I think the inefficiency isn’t detrimental. I did leave a note in the BPM linking to your examples with the “In (List…)” Linq code should we ever need to make it a little faster. So thanks!

1 Like

Hi Terry,

I think because it is pre-processing on Update for only added rows, that it’s naturally giving me only my new row data in PODetail.
What is your directive on? Is it a method or data directive? I’d be leaning towards trying to use JobEntry GetNewJobMtl method directive or JobMaterial Update data directive. I think these would naturally provide only the subject rows of data needed in JobMtl.

Nancy

Ah gotcha thanks. Method directive, triggered on job schedule. Job may be set up one day but not released for production until a later date; inventory may change meanwhile. And will update every time they reschedule a job.

2 Likes

I have talked to Dev about this issue… I hope that we can get at LEAST a warning message into the system when you connect a memory table to a DB table.

4 Likes