SBX - Search With Button

SBX - Forum Post Title

Duplicate Records Validation Required before inserting data into a table using batch job class

Microsoft Dynamics AX Forum

Muhammad_Ali asked a question on 9 Nov 2018 12:47 PM
My Badges

Question Status

Verified

Hi -

  I have a batch class which reads the info log messages from BatchHistoryTable and inserts them into a Custom Regular table using insert(). The job works fine. I just need to put a validation that the records don't repeat depending on these three fields.

Document #

Employee ID

Assignment Date

I want to have a check in my batch class which reads the data in a Custom table before inserting.

If Document#, EmployeeID and Assignment are not found in a table for a single record then insert() or else no. In other works these three fields should always be unique for every row in a table.

How can I achieve this functionality thourgh X++ ? or f there is any other way of doing this ?

Please let me know if my question is not properly asked and I will do my best to elaborate further.

Thanks

Reply
ievgen Miroshnikov responded on 9 Nov 2018 1:28 PM
Verified Answer

Hi Muhammad_Ali,

First of all, create new index with these 3 fields and set allowDuplicates = No. This will prevent any code inserting a duplicate and throw an error. Now when you have index in place before inserting data into the table do a select by these 3 fields and if there is nothing in DB then do insert.

Reply
Muhammad_Ali responded on 9 Nov 2018 3:15 PM
My Badges

Thanks Ievgen  

  I made indexes for the three fields and did as you said. Now my job runs fine and inserts data accordingly. I have tried sending the duplicate data to the table for testing and duplicates were not inserted. As they shouldnt!!

Thanks

Reply
faiz7049 responded on 9 Nov 2018 11:37 PM
My Badges

Hi Muhammad_Ali,

I have same scenario . Can you share Job X++ code please.

Reply
Muhammad_Ali responded on 10 Nov 2018 2:33 PM
My Badges

Hi Faiz

I can do it monday morning. I dont have access to the system as of now.

Thanks

Reply
Muhammad_Ali responded on 10 Nov 2018 2:34 PM
My Badges

X++ code for exactly which part ? Code validation ?

Reply
faiz7049 responded on 11 Nov 2018 12:03 AM
My Badges

Hi Ali,

I need both. Insert info message to Custom table and validation.

Reply
ievgen Miroshnikov responded on 9 Nov 2018 1:28 PM
Verified Answer

Hi Muhammad_Ali,

First of all, create new index with these 3 fields and set allowDuplicates = No. This will prevent any code inserting a duplicate and throw an error. Now when you have index in place before inserting data into the table do a select by these 3 fields and if there is nothing in DB then do insert.

Reply

SBX - Two Col Forum

SBX - Migrated JS