BPM | Prevent Duplicates with Multiple Criteria

Epicor ERP 10
1

I am working on a Parent/Child UD Table (UD100) and would like to prevent duplicate child records from being created. I am using a BPM with a query condition > 0 that links ttUD100 to UD100 :scream: The 3 fields used in the relationships are my criteria for determining whether a record is a duplicate or not. If the query returns a row then the condition evaluates as True which fires off an alert stating the record is a duplicate blah blah blahā€¦ This is a fairly common way to check for duplicate records in Epicor (at least thatā€™s what my friend Google tells me) so I know Iā€™m not the only one out there doing this.

After attending a certain session at Insights this year it was revealed to me that the seemingly harmless BPM I created was actually a ticking time bomb capable of reducing the app server to a steaming pile of ash. In order to avoid this (and the impending ā€˜Batslapā€™ from @josecgomez) I would like to change my BPM, but Iā€™m not sure what way I should go. Does anyone have any suggestions?

2

Iā€™m a little curious what you mean by duplicate records. Are you using all 5 key fields? The SQL itself will not allow duplicate records to be added, but maybe Iā€™m interpreting it wrongā€¦

3

Let us see the code :slight_smile:

4

I failed to mention that the purpose of this BPM is to autonumber the child key when a new one is created. Here is what the BPM looks like.

BPM%20Design
BPM Design.png874Ɨ464 41.8 KB

This is what the Duplicate Check condition query looks like. If all three fields match between the tt and the db table then the row is considered a duplicate. (Note: Key1 is the Parent Key)

BPM%20Query
BPM Query.png1079Ɨ757 154 KB

Show%20me%20the%20code
Show me the code.jpg460Ɨ523 115 KB

This is the code

foreach (var MyUD100A in (from ThisUD100A in ttUD100A where
  ThisUD100A.RowMod ==  IceRow.ROWSTATE_ADDED 
  select ThisUD100A))
{ 
  using (var txScope = IceContext.CreateDefaultTransactionScope())
  {
    foreach(var MyUDCodes in (from ThisUDCodes in Db.UDCodes.With(LockHint.UpdLock) where
      ThisUDCodes.Company == Session.CompanyID &&
      ThisUDCodes.CodeTypeID == "AutoNumber" &&
      ThisUDCodes.CodeID == "UD100A"
      select ThisUDCodes))
    {
      int NextCustNum;
      int.TryParse(MyUDCodes.CodeDesc,out NextCustNum);    
      MyUD100A.ChildKey1 = NextCustNum.ToString();    
      NextCustNum += 1;    
      MyUDCodes.CodeDesc = NextCustNum.ToString();  
    }
    Db.Validate();
    txScope.Complete();
  }
}
5

Well you are not joining to the ttTables so you are ok in that aspect. I havenā€™t had a chance to look at the restā€¦ but from your original concern you are fine.
For what is worth Epicor has facilities built in to keep track of AutoSequence numbers at the Company Level. @timshuwy made a post about it a while back. So you donā€™t have to find the next one yourself.

1 Like
6

huh?

image
image704Ɨ496 160 KB

7

Sorry only looked at the codeā€¦ Oops :flushed:

1 Like
8

@josecgomez Thanks for the link to Timā€™s post. Iā€™m using method 4 right now, but will take a look at implementing the latest method in my customizations.

My concern isnā€™t so much how to autonumber, but how do I verify that the new record being created isnā€™t a duplicate entry based on specific criteria that I define. The BPM query Iā€™m using right now works perfectly, but its the tt table join that has me concerned.

9

if you remove the join, then add a criteria to eh ice.UD100A table, for each of those things, you can return rows for when those things match. That will work better than the join for TT table.

Plus, That join to the TT table has been used by a lot of things for a long time. If you arenā€™t having problems with it now, I donā€™t think I would worry about it too much. If you start having performance problems, thatā€™s when I would start looking at that.

1 Like
10

We need to start another discussion around the TT Table joinā€¦ I have done some internal research hear at Epicor, and I think that there may be too much made over thisā€¦ I will start the new thread.

2 Likes
11

@Banderson Thank you for your suggestion. This works perfectly.

BPM%20Query%20-%20No%20Join
BPM Query - No Join.png830Ɨ582 29.1 KB

1 Like
12

So whatā€™s the rule on how long you have to know something that you learned from someone else before you get to take credit for it? (since we learn pretty much everything from someone else at sometime right?) I learned this from @josecgomez :+1:

1 Like
13

Hi @Banderson as a note @timshuwy and I did some more testing on this this week and that approach is not any better. Epicor in its infinite wisdom does a CROSS Join in the back end where 1==1. :frowning:
See

Though you did learn that from me, now un-learn it. The best approach is to assign the ttKey1, etc to a variables and then use the filter.

14

HA, good thing I gave you credit!!

1 Like
15

Iā€™m not sure I am understanding what you mean by this. Can you give me a quick explanation? I was looking through the other post, and I didnā€™t quite get it.

16

In the BPM you can create variables and assign the current ttXX. Value to that variable then use that variable in your BAQ Condition.

image

Then you can use this guy
image

To Set the value of that variable
image
Like So

image
image.png740Ɨ519 30.2 KB

image

Then in your BAQ Condition, Add your table (not the ttTable) and use that variable in your condition (filter)

image
image.png655Ɨ529 31.9 KB

1 Like
17

well isnā€™t that the long way around.

Good to know. Thanks.

18

You can always say F* it all and go Code first itā€™s what I doā€¦

1 Like
19

image
image.jpg508Ɨ508 86.5 KB

20

image
LoL

1 Like