At times when troubleshooting Microsoft Dynamics AX you need to know which tables were updated by a particular operation, for example after performing a posting. This could be if you want to be sure that all the right tables are updated correctly after an upgrade, if you have unexpected results on one customer/vendor etc or if you have made an update the system and you want to be sure that everything is ok.
It is possible to use the SQL tracer for this, but for functional people it can be at times be a bit challenging to interpret these results, so here is a tip for how you can easily find out which tables were updated when performing an operation in AX. In this example we will show the effect on tables from a Sales Invoice posting in Microsoft Dynamics AX 2012 as this is quite a complex process that affects many tables.
The whole technique here relies on using a standard report ‘Size of company accounts’ in a novel way. This report returns an output of all the tables in AX that have data in them. In this article we will see how we can use this report to print a snapshot of the tables before and after an operation so that when we print it ‘after’ we will see which tables got data populated into them from an AX operation.
The steps are as follows
1. Go to the AOT and locate the Class ‘SysCompanySizeDP’ and open up the method called ‘insertIntoTempTable’. Change the highlighted function from ‘tableid2Pname’ to instead ‘tableid2name’. The reason we are changing the function is that it is easier to locate the tables in the AOT when the report prints the tables using the technical names. If we didn’t do this, the report would for example print the name of a table like ‘Customer Invoice Journal’ instead of ‘CustInvoiceJour’
2. Now go to System Administration\Reports\Database\Size of company accounts and run the report. Note the technical names
3. Export this report to Excel
4. Open up the saved Excel sheet and notice that the report content is copied over
5. Now let’s make a sales order invoice posting
6. We go back to System Administration and run the company size report again and once more save it, this time under a different name, for example ‘Company Size 2’
7. Now we copy the tables in the sheet from the last run ‘Company size report’ to the first saved Company size Excel sheet
So that we have a sheet with 2 different ‘Company size’ report executions – Before and after the Sales Order invoice posting
8. Now add a new column ‘Difference’ with a simple calculation extracting cell ‘C’ from cell ‘H’ and copy it across the rows as highlighted on
the screenshot in red below. Notice also the black box, that shows already some tables that have been affected
the screenshot in red below. Notice also the black box, that shows already some tables that have been affected
9. Now in order to easily identify which tables were affected by the posting we can do this trick, apply a filter in the ‘Difference’ column
and unmark ‘blanks’ in the display of the cells’ values
and unmark ‘blanks’ in the display of the cells’ values
RESULT
We can now see easily which tables were updated by the Sales Order posting. The same technique can naturally be used by any other kind of operation in AX, such as a Journal posting or a Master Planning run – all it relies on is just tracing which tables were updated since the last run of the report. Have fun !
No comments:
Post a Comment