New AX Tip: Queries – Not Really All or Nothing

New AX Tip: Queries – Not Really All or Nothing

dynamics-ax-technical-tip

By Rick Scruggs, Fullscope, Technical Solution Architect

When developers think about queries, they think the queries are limited for the most part because the combined ranges are all AND conditions.  Not so.

A recent requirement related to the CustOpenInvoicesListPage query surfaced. I needed to add an additional range on the custtrans data source for the field transtype. After going through the standard development practices and taking a look at all of the points of modification, I was befuddled by what I saw: multiple occurrences of the transtype field.

At first I didn’t think running the query would produce any records unless somehow the query was recreated in code at runtime. I searched for code that wasn’t there. A colleague gave me the idea to hover over the data source to get a look at the SQL statement. And there it was, right in front of me … (see Figure 1) Multiple ORs surrounding all of the transtype field conditions then an AND for the additional field not named transtype.

figure1

Figure 1: Shows the where clause when hovering over the custtrans data source

I recreated the same scenario on the SalesTable as a datasource to a junk query. I added the range for payment twice, and then added one for another field.  This also created an OR condition in the AOT query (see Figure 2). I have been involved with AX since it was called Axapta 3.0. This is the first time I have seen an AOT query with any type of OR condition not inside a special expression.  I was a little surprised.

Figure2

Figure 2: Shows the select statement when hovering over the data source in the junk query with only two fields added

 Have you had a similar “a ha” AX moment? Share it here or shoot me an email [email protected].


ERP, AX Technical Tip, AX Training, microsoft dynamics ax, Microsoft Dynamics AX ERP, AX ERP Implementation, ERP Implementation, ERP tips, erp trends, Fullscope, Microsoft Dynamics AX 7.0