Find the SQL Query behind an Ax table

(1) Share

Report

Posted on by YE-10060814-0

Hello,

I would like to know how can I find what is the query behind an Ax Table ?

For example, if I take the 'On-hand stock' table on Axapta. How can I know what is the exact query that displays this table ?

How can I find the different source tables, the link between them, the clauses... ?

Thanks !

All responses (7)

Answers (1)

Verified answer

Martin Dráb 230,214 Most Valuable Professional on at

Like (0)

Report

Find the SQL Query behind an Ax table

If I remember it correctly, Personalize is the context menu label (Personalise in British English) and Personalization is the title of the form opened by the menu item.

Yes, I know how to use the debugger. If you want to learn it too, you should talk to developers in your company, if you have any. If not, you can look into AX 2012 documentation (e.g. Install the debugger and Microsoft Dynamics AX Debugger). If needed, you can create a new thread (with an appropriate title) and ask a question about debugging there.
YE-10060814-0 9 on at

Like (0)

Report

Find the SQL Query behind an Ax table

Thanks for the precision.

Do you know where is the 'Personalization' form ? I can only find the 'Personalise' but I think its not what you are talking about.

And also, do you know how to use the Debugger ? Im new to this tool Im sorry
Martin Dráb 230,214 Most Valuable Professional on at

Like (0)

Report

Find the SQL Query behind an Ax table

Sorry, this isn't what I meant. There is a way to get an actual query string used by the form, not just this graphical representation. Unfortunately it's so many years since I used AX 2012 for the last time that I don't remember how to do it. I think it can be done somewhere in the Personalization form. If not, you can use the debugger to see that or use code (to call toString() of the root data source and put the result to infolog, for example). It will give you psedo-SQL code, not the actual T-SQL code sent to database, but it's still very useful.

Your SQL code doesn't resemble the query used by the form at all. Most importantly, you're missing all aggregation functions and grouping by dimensions. Another serious problem is that you forgot to filter the data by DataAreaId (and Partition). It's done automatically by F&O but you need to do it manually if you bypass the application layer and you write SQL queries directly against the database.

Also, F&O queries don't support the distinct keyword.
YE-10060814-0 9 on at

Like (0)

Report

Find the SQL Query behind an Ax table

I did understand the difference between the table and the form, thank you !

Let's keep the example of 'On-hand stock' form :

On the left, there is the query tab of the form. It gives the different tables involved in the form (the tales that the form get the data from).

On the right, I tried to translate this query in SQL.

The problem is that I don't get the same result between the form on Ax and the table i get from the SQL query in SQL Server.

Moreover, the field 'Product Name' is not in any table presented in the Query Tab of Ax (neither InventSum nor Inventable). I had to find this field in EcoResProductTranslation, and then it works. But this table isn"t mentionned in Ax at all.

So I I kept digging in that direction and I found out that I get very close to the result I want when I filter on the LanguageID field of EcoResProductTranslation.

Again, how am I supposed to know that some filters have to be applied to this table, when it's not indicated in the Query table of Ax ? Are there some 'hidden filters' ?
Martin Dráb 230,214 Most Valuable Professional on at

Like (0)

Report

Find the SQL Query behind an Ax table

Tables are data structures that stores data, usually in the underlying SQL Server database. But that's not what users see; they don't connect to the database server and execute SQL commands. AX uses forms and reports to present data to users. Yes, forms and reports usually takes data from tables, but it doesn't mean that it's the same thing. They often show data from several different tables, they provide more meaningful visualization than just a list of fields, forms offer buttons for actions on the data and so on.

Everything you see in the UI are forms and reports, therefore yes, 'On-hand stock', 'All product and products masters' and 'Main accounts' are forms, not tables. Forms use queries to fetch data from database tables (joining multiple tables, applying filters and so on) and present the result to users.

You'll need to share more information about your query before we can suggest why it doesn't do what you want.

Regarding development tools, AX can modified by developers, e.g. they can create new tables and forms or change existing ones. In AX 2012, it's done in the development workspace, but you need a license to access it.
YE-10060814-0 9 on at

Like (0)

Report

Find the SQL Query behind an Ax table

Thanks for your answer!

I have the Ax 2021 R3 version.

Im new to this tool, so I don't really understand the different between a form and a table.

So you are saying that element such as 'On-hand stock', 'All producut and products masters', 'Main accounts'.... are forms and not tables ?

I tried to go Personalise > Query, it helps me but when I try to imitate the query with my real tables in SQL Server, I dont find the exact same result.
I feel like there are some filters that are 'hidden', or some part of the query that I can't see. Could you help me on that ?

Also, what are the development tools ?

Thanks for your help !
Suggested answer

Martin Dráb 230,214 Most Valuable Professional on at

Like (1)

Report

Find the SQL Query behind an Ax table

There is On-hand stock table in AX; you must mean a form. Showing a query used by a form is possible, but the exact steps depend on your version of AX. Please add this information.

For example, in AX 2012, you can right-click the form, go to Personalization and then switch to the Query tab. Note that a form can have several queries, e.g. the form for sales orders uses separate queries to fetch order headers and lines.

You may also want to look at the form definition (using development tools).

Find the SQL Query behind an Ax table

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

Find the SQL Query behind an Ax table

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates