Have you been trying to get a massive data file into D365 and found out that 150,000 records were imported successfully but about 600 errored out?
Then you start thinking, “ugh, I'll have to go over each one of those records and figure out why D365 rejected them. Why isn’t there a way to generate a file with all the errors and the records we tried to import?!”
Well, guess what? There is a way to do it. Get your Chrome browser ready, a “vlookup” and… voila!
Let’s do it!
First, download this cool Chrome extension called Table Browser Caller for D365FO (I know, I'm using Chrome, but unfortunately this is not available for Edge or Internet Explorer).
Once installed, configure the Table Browser Caller to point to as many environments as you wish. Once you’ve selected your environment and the right company, click on Table list. (If you know the name of the table you are looking for, just past it in the search bar and hit enter.)
The table that holds the staging errors for all the Data Management jobs is called "DMFStagingValidationLog".
This table might not be exposed on the table list, so just go ahead and type the name on the Table Browser Caller search bar and hit enter.
We'll focus on the four table fields below:
- ErrorMessage: Contains the import error for each record.
- Execution ID: A Data Management Import job generates a Job ID each time it is used. This ID can be found under the Job History form (navigate to the Data Management workspace, open the Data Project and click Job History).
- Staging Table Name: This is the second table we'll need to extract in order to match the error message with the staged record. The "StagingTableName" will change based on the entity used for the import.
- Staging RecID: Identifies the staged record. Will be used to “vlookup" primary key to match an error message with its staged record.
Use the column "ExecutionID" to filter the Job ID you're looking for.
In our example, the "StagingTableName" is "VendInvoiceJournalLineStaging," as we are trying to import AP Invoices via an AP Invoice journal.
Using the Table Browser Caller, open the staging table and use the "ExecutionID" table field to filter on a specific executed job. Then use the "RecID" table field to match the record with its error message.
So there you have it, with the help of the D365 Table Browser, you can export all the staging data error messages and their associated records. Happy exporting!
