Avoiding inserting duplicate data into table in MS Dynamics AX

Question

I have a custom table that I'm inserting data into. I do not want duplicate data to end up there, so I created a unique index consisting of 20ish fields that I wish to be unique. As expected, when I run my job to insert data it of course fails and tells me it was trying to insert a duplicate record and stops the job there. If I wrap a tts around it the whole thing fails.

My question is, how can I make it so that the jobs still continues and only just stops the duplicates from inserting? Note, like I mentioned above, I have 20ish fields that make up the key, it'd be cumbersome to write up something that checks for existing records with data matching all 20 fields.

score 3 · Accepted Answer · answered Mar 12 '14 at 20:06

I found it, keeping the unique index on the table, I wrapped it around a try catch, which apparently has its own Exception type for this, in place of just the insert():

try
{
    customTable.insert();
}
catch (Exception::DuplicateKeyException)
{
    //clears the last infolog message, which is created by trying to insert a duplicate
    infolog.clear(Global::infologLine() - 1);
}

score 1 · Answer 2 · answered Mar 12 '14 at 23:01

Man, I wouldn't delegate the management of this to exception control. If it's only in a Job, it's ok, but if you plan to manage records in other points, we warned that if you use nested try-catch blocks, the control will go to the outermost try-catch block, avoiding internal ones. Well, there are two or three exceptions that aren't (check programming manual, I don't remeber them now, they were related to DDBB record blocking and so on).

I would create a static Exists method in table, and be careful in selecting only recid for performance purposes. Yes, writing 20 fields in a select is a pain, but you will do that ONCE, and in long-time terms it's the best and maintaineable focus.

public MyTable exists(Type1 _field1, Type2 _field2...)
{
    boolean ret = false
    if (_field1 && _field2 && ...) //mandatory fields
    {
        ret = (select firstonly RecId from MyTable 
                where MyTable.Field1 == _field1
                   && MyTable.Field2 == _field2 ...).Recid != 0; 
    }

    return ret;
}

In general I wouldn't use this method in insert() or update() except if there's a good reason for this (in that case, It can be interesting to set AllowDuplicates == Yes if performance is critical, beacuse you're managing duplicates manually - be careful with doupdates/doinserts or external inserts/updates). I would use this method in your job or other places to check duplicates before inserting/updating.

Yes, http://stackoverflow.com/questions/12535992/how-to-catch-inner-exception/12552633#12552633 — Jan B. Kjeldsen, Mar 14 '14 at 16:42

score 1 · Answer 3 · answered Apr 04 '14 at 23:22

1

Why don't you implement a validate write method and avoid to insert the duplicates?

if (table.validateWrite())
   table.insert();
else
   log

answered Apr 04 '14 at 23:22

carrerdedalt

11
4

I only want to catch and clear the duplicate key issue, this will catch EVERY thing and I won't know why it failed, just that it did fail. – codemann8 May 05 '14 at 05:02

Avoiding inserting duplicate data into table in MS Dynamics AX

3 Answers3