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?
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…
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)
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.
@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.
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.
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.
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
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.
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.