Skip to main content

Notifications

Microsoft Dynamics AX forum
Answered

Find the SQL Query behind an Ax table

Posted on by 9
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 !
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 225,000 Super User on at
    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 Profile Picture
    YE-10060814-0 9 on at
    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 Profile Picture
    Martin Dráb 225,000 Super User on at
    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 Profile Picture
    YE-10060814-0 9 on at
    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 Profile Picture
    Martin Dráb 225,000 Super User on at
    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 Profile Picture
    YE-10060814-0 9 on at
    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 Profile Picture
    Martin Dráb 225,000 Super User on at
    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).

Helpful resources

Quick Links

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Blog subscriptions now enabled!

Follow your favorite blogs

TechTalk: How Dataverse and Microsoft Fabric powers ...

Explore the latest advancements in data export and integration within ...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 283,682 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,000 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Featured topics

Product updates

Dynamics 365 release plans