BPM - Check condition from a separate table


(Andris Skulte) #1

Hi guys,

How do you get a BPM condition to check a table that isn’t one of the temp tables brought in from the Update medthod?

I’ve got a pre-processing BPM on SalesOrder.Update, which has a condition to check if a specific TermsCode is used. If it is, then I’d like the BPM to see if a UD field (Customer.LastThree_c) is empty. If it’s empty, I want to raise an exception.

Problem is I can’t figure out how to access the Customer.LastThree_c UD field .

I’ve read through both the basic and advanced BPM edu pdf’s hoping for an example I could modify, but struck out.

I’m guessing C# code?

Thanks for the help!


(Dan Edwards) #2

You can do this using Invoke BO to Customer and base the condition on the returned dataset. I can attach an example shortly – or use C# code.


(Dan Edwards) #3

You could also do a condition “Number of rows in the designed query” and setup the query to look at the field


(Andris Skulte) #4

Hi Dan,

Jackpot!!! Thank you. The “Number of rows in the designed query” worked great once I linked the OrderHed temp table to the Customer table.

By the way - I didn’t see “Invoke BO” in the callers section of the Pre or Post-processing, just “Invoke External Method”. We’re on 10.0.700.4. Could it be lurking elsewhere?


(Andris Skulte) #5

I’m almost there!

When I add criteria to the query, the BPM has a compilation error of '“An object reference is required for the non-static field, method, or property”.

Without criteria to the query, it works fine. What’s causing the compile error? I’ve created the same query in a BAQ, and it runs OK with a hard-coded CustNum instead of taking it from the temp table. I’ve added Company and CustNum fields in a message box, and they are getting passed from the temp table, so it should be getting to the query.

Thanks again for the help.


(Dan Edwards) #6

For Matches use something like

 "%123%"

(Andris Skulte) #7

Dan for the win! Thanks.

It’s weird how the same thing in an Epicor BAQ doesn’t work in the BPM query. :stuck_out_tongue:


(Monty Wilson) #9

Dear Andris,

If it would be more efficient to use C# code, consider the example below. IT IS NOT FOR YOUR SPECIFIC TABLES but it gives an example of looking at the temp table added/changed record, and using the fields from that record to query from another table, only the linked record needed. I have another example that goes through an iteration of more than one matching record instead of using the FirstOrDefault() function to get the single linked record.

HTH
…Monty.

// Clear backflush for FEs
// Custom code for data directive for JobMtl
// Trigger on updated rather than added JobMtl because it is created blank then filled
// If part class of cur assembly starts with 'FE' then set backflush to false
// 2017-04-18 MW initial code
// 2018-02-28 MW translate to C#
//


Erp.Tables.JobAsmbl JobAsmbl = null;
Erp.Tables.Part Part = null;

var ttJobMtl_xRow = (from ttJobMtl_Row in ttJobMtl
    where ttJobMtl_Row.RowMod == "A" || ttJobMtl_Row.RowMod == "U"
    select ttJobMtl_Row).FirstOrDefault();
    
if (ttJobMtl_xRow != null)     // added or updated JobMtl record exists?
{
    JobAsmbl = (from JobAsmbl_Row in Db.JobAsmbl
        where JobAsmbl_Row.Company == Session.CompanyID 
        && JobAsmbl_Row.JobNum == ttJobMtl_xRow.JobNum
        && JobAsmbl_Row.AssemblySeq == ttJobMtl_xRow.AssemblySeq select JobAsmbl_Row).FirstOrDefault();
        
    if (JobAsmbl != null)   // found matching job assembly record?
    {
        Part = (from Part_Row in Db.Part
            where Part_Row.Company == Session.CompanyID
            && Part_Row.PartNum == JobAsmbl.PartNum
            && Part_Row.ClassID.StartsWith("FE") select Part_Row).FirstOrDefault();
        if (Part != null)   //found matching part record?
        {
            ttJobMtl_xRow.BackFlush = false;
            Db.Validate();
        } // end if matching part record found
    } // end if found matching job assembly record
} // end if added or updated JobMtl record exists

(Dan Edwards) #10

Not sure how this would be more efficient than a few objects with no code required to do this? The condition he has will work perfect and will only return a single customer record to memory for the join. I think efficiency depends on the skillset.


(Monty Wilson) #11

Dan I’m not sure it would be more efficient but if Rob’s point is correct, then it could be way better than pulling an entire table into memory. I can’t say for sure because I haven’t tried the query/join method.


(Dan Edwards) #12

I know what @rbucek is saying but it not the case with this specific example.


(Andris Skulte) #13

@rbucek - How can I see what’s happening under the hood? Would a trace with the BPM enabled show how the query steps through, and what it’s loading? We’ve got over a million customers in our db, so I’d like to be efficient with the techniques as I learn more about programming. It didn’t seem too bad during testing…

I’ve got my BPM set up so the first Condition looks for TermID=“NRS”, and if true, goes to the next condition with the query. This should skip the (possibly intensive) query on the majority of orders, no? The query is drawn with an inner join (it started off that way, then stripped the name in the “join” box, and doesn’t give me an option to change it.

@danbedwards - I changed the query criteria to LastThree_c MATCHES “%” constant with the intent of giving me a row when the field is not empty. The vast majority of our records have that field empty. Is this an effient way of doing it?

@MontyMan - Learning C# is next on my list to help improve my Epicor development. I was thinking these courses would be a good intro so I can start following along with the examples everyone here posts.

Thank you all for your advice! I’m learning a ton. Hope to contribute when I have something to give back (or at least get you all a beer at Insights!)


(Dan Edwards) #14

You could change MATCHES and instead check the box NOT and select ISNULL

For reviewing performance you can enable BPM Logging in the server properties and use SQL profiler. You can also use the PDT and trace logging to help diagnose performance. Between these tools, you can see exactly what is going on behind the scenes.


(Andris Skulte) #15

@danbedwards - I’ll check out BPM logging and the SQL Profiler, and have asked our dba to get me set up. Haven’t used either before, but happy to add tools to the toolbox!

Fixed it!!! Replaced MATCH "_%" with >= "0" and it works! It seems odd to use > and numbers in a string type field.

Thanks again for the help.

Things that didn’t work (in case it helps others):

  1. My BAQ test doesn’t return any null results, so I wouldn’t expect that to work. Epicor made the field empty when no data is in it (on a new record, or when I’ve deleted the numbers and saved).

  2. I modified my query MATCH to “_%”, thinking I’d catch all instances when something is in the LastThree_c field. It didn’t work - it always returns false, regardless of the LastThree_c field being empty or containing data.

  3. I tried MATCH “[0-9]%”, but that didn’t return a row either.

  4. If I change the query MATCH to “123”, I get true when the customer has 123 in the LastThree_c field, and false if it’s 456 or empty (so that works).

Not sure why those didn’t work, but the BPM now works correctly, and hopefully as I get better at this it’ll be less trial and error and more skill.


(Dan Edwards) #17

Did you try just using a condition like this?