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 onthe 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’ columnand unmark ‘blanks’ in the display of the cells’ values
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 !
Great Kim !
Very nice! Useful when we need to know which tables have changed after some operation.
Nice tip :)
So obvious ! Lovely
Good and new way to look the solutions
a nice trick :)
Nice little trick, love it!
Very nice tip but did not work in my case when I tried to find out which tables were updated when I entered a record of Employee in HR Module.
Very Nice n Useful ,Loved it. Thanks lot
I know this post is old but it does not tell you what has been updated, it tells you any NEW records being created.
This is one area of AX which is so hard to actually figure out correctly.
What we need is a tool, which tells you number of records per table has been:
Almost like task recorder, this way this can be used to do proper sizing of the SQL Disk backend in regard to IOPS
Perhaps you can use the database log for the operations you are looking for?
Nice post :-)