This blog post will walk you through on how to fix the error “Cannot create a record in Ledger journal table (LedgerJournalTable) record already exists” in Microsoft Dynamics 365 for Finance and Operations (D365FO).
Although this post will be written for D365FO you can do the same in Dynamics AX2012 and other Dynamics AX versions. Only difference is the shortcuts and the location of the table browser, which I will cover in this post.
Cause of the error
Number sequences are handed out in consecution. For determining what number needs to be handed out D365FO will use the next field on the number sequence.
When a number sequence is set to continuous, D365FO will try to keep it continuous. This means that besides the next field D365FO will first use a status list for numbers that are reserved but somehow not successfully assigned. This situation can occur when a session incorrectly ends, crashes, customizations or incorrect imports or by simply deleting draft records.
When this happens, the number is “locked” for as long as the process causing the lock is running. However, the next field in the number sequence is updated causing a gap in the sequence. Normally this situation is fixed automatically by D365FO because of the status list.
However, sometimes an error happens in assigning numbers to the status list and it happens that a number is assigned but remains on the status list.
So, in the case below the next number should be 667, however number 357 is on the status list. When creating a journal you will see that number 357 is used instead of 667. Because number 357 was already used for a journal the error below will show up.
Solution for the error
This error can happen for any number sequence set to continuous however most commonly this happens for journal batch numbers. As this sequence is used in all journals used throughout D365FO this is the most error prone number sequence.
To fix this error go to the General Ledger Parameters and then go to the number sequences tab. In the number sequences tab open the number sequence for the journal batch number by clicking on the sequence or by right click, view details.
This opens the number sequence form. From here you can first run the manual cleanup, I suggest running the current one. You won’t directly solve the issue with this, but it is recommended to run this function first. This basically makes sure all the number sequences on the status list are set to status free / action none.
After running the manual clean up you can open the status list, in here you will see the number sequences that are reserved. To solve this issue, we will have to manually delete the reserved number sequence from here. But before we do that it is recommended to check whether it is justified that the number sequence is on this status list or not.
To do this navigate to the table browser in D365FO by going to the link:
https://<yourenvironmentlink>/?cmp=<company>&mi=systablebrowser&tablename=ledgerjournaltable
and open up the ledgerjournaltable. In AX go to the AOT by pressing CTRL + D and navigate to the table browser and open the ledgerjournaltable.
In here find the column journalnum and filter on the number sequence from the status list you are intending to delete. If you can find this number, you can delete it from the status list. To delete the number press ALT + Delete for D365FO and ALT+F9 for AX.
If you can not find the number then the notation of the number on the status list is justified, do not delete it because this will cause inconsistency in the number sequence.
It is recommended to check up the journal number in the ledger journal table. This can give you valuable information on the possible cause of the error, whether it is a one-time issue or if it is recurring in the same area. This can be an indicator to check the processes around those specific journals.
Conclusion
You now should be able to solve number sequence errors in a controlled fashion in Dynamics 365 for Finance and Operations. We have discussed the actions which are recommended to perform first and how to perform these actions.