BPM | Prevent Duplicates with Multiple Criteria

THulbert · June 21, 2018, 6:41pm

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 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?

Aaron_Moreng · June 21, 2018, 6:48pm

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…

josecgomez · June 21, 2018, 6:51pm

Let us see the code

THulbert · June 21, 2018, 8:35pm

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.

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)

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();
  }
}

josecgomez · June 21, 2018, 8:56pm

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.

Banderson · June 21, 2018, 9:11pm

huh?

josecgomez · June 21, 2018, 11:29pm

Sorry only looked at the code… Oops

THulbert · June 25, 2018, 7:18pm

@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.

Banderson · June 25, 2018, 7:31pm

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.

timshuwy · June 25, 2018, 8:42pm

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.

THulbert · June 27, 2018, 7:22pm

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

Banderson · June 27, 2018, 7:30pm

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

josecgomez · June 27, 2018, 7:33pm

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.
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.

Banderson · June 27, 2018, 7:35pm

HA, good thing I gave you credit!!

Banderson · June 27, 2018, 7:40pm

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.

josecgomez · June 27, 2018, 7:44pm

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

Then you can use this guy

To Set the value of that variable

Like So

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

Banderson · June 27, 2018, 7:46pm

well isn’t that the long way around.

Good to know. Thanks.

josecgomez · June 27, 2018, 7:50pm

You can always say F* it all and go Code first it’s what I do…

Banderson · June 27, 2018, 7:50pm

josecgomez · June 27, 2018, 7:53pm

LoL