I can successfully get the data into staging. However, when I "Copy Data to Target", I get the following error: Cannot create a record in Inventory journal lines (InventJournalTrans). Journal: <Journal ID>, 1,0000000000. The record already exists.
Note: In my testing file, I have 10 records for each Journal line number. No records are inserted into Target and I receive an error message for each line number I'm attempting to insert.
The actual Journal ID mentioned in the error message has no other entries in the table. At first, I thought it might have been an issue with an unpopulated index field. A colleague mentioned that the Voucher might be causing some problems.
Has anybody encountered this issue before and if so, how did you fix it?
The InventJournalTrans table has three indexes which needs to be unique:
- LineIdx: JournalId + LineNum
- VoucherIdx: JournalId + Voucher + LineNum
- TransIdIdx: InventTransId
Check the records if Per journal Id the Linenum and Voucher + LineNum combinations are unique.
The InventTransId can be provided within the staging table as well, but you can also enable 'Run Business Validations' and 'Run Business Logic in Insert or update methods on target entities and processing group settings to have this taken from the standard logic.
André Arnaud de Calavon | Microsoft Dynamics AX Solution architect | My blog | My company
This post is my own opinion and does not necessarily reflect the opinion or view of my company, Microsoft, both its employees, or other MVPs.
Thanks for your response.
In my staging table, I've got 10 records with:
- the same Journal ID
- Line Numbers 1-10
- Voucher column is blank
Does it matter that the Voucher column is blank? Unless I'm mistaken, it will get auto-populated based on a setting once each record is moved into the target table.
I think indeed the voucher field remains empty. Check if you have enabled the business validations on both the processing group details and the target entity. Then this field should be popilated automatically. If this is not working, you can also specify in the field mappong of the processimg group that the foeld should be fillwd from a nimber sequence. During import from the source the staging table will then have the voucher field filled. Note that if you need to rerun the source import the number sequence will draw new numbers and will not use the first set unless you adjust the number sequence manually.
You can also specify you own voucher numers in the sourfe file.
When I checked the "Run Business Logic..." checkbox, I managed to get the data into Target successfully. The only thing, and this might be a separate issue with some incorrect/missing configuration, is that the Voucher column still seems to be empty. However, at least I got the data into the Target table.
Thanks Andre, After marking 'Run Business Validations' and 'Run Business Logic in Insert or update methods on processing group settings data is inserting into Target table.
But I had another concern, I couldn't import the data with new Item ids, I had to give the existing item ids...?
Thanks Andre, After marking 'Run Business Validations' and 'Run Business Logic in Insert or update methods' on processing group settings data is inserting into Target table.
But I had another concern, I couldn't import the data with new Item ids, It is throwing error *** item id is not available in InventTable. So I had to give the existing item ids...
And What compiler will do If we mark those check boxes.....?
I would like to know the functionality.....
When you normally enter information in an inventory journal, the Item number should be in the system. This is not different when you use DIEF for importing the transactions. So indeed make sure your item numbers exists.
I don't understand the real answer behind the question: "And What compiler will do If we mark those check boxes.....?". Whart do you mean with the compiler?
Thanks for reply Andre,
What will happen if I mark "Run Business Validations' and 'Run Business Logic in Insert or update methods" Check boxes.
Many tables in AX contain business logic on tables. These methods are called e.g. 'validateField', 'Insert' or 'Update'.
The logic takes care of e.g. number sequences or other fields/tables to be maintained after input of a value in a certain field.
The checkmarks 'Run Business Validations' and 'Run Business Logic in Insert or update methods', will call the methods if checked. If unchecked, these methods are not executed.
If you have source data with all the required fields and no other fields needs to be executed, you can have these check boxes off. E.g. When creating a customer, depening in the customer group the default Terms of payment and Default tax group can be initialized. When you already have provided these fields in the source data, you can skip the business validations.
Thank you Andre. You explained very clearly.