I’ve been working on a form using AX 2012 R3 which queries the SalesLine table and pulls various information from it. A few of my fields are display methods. Obviously this means I can’t manually filter or sort on them. I was wondering if someone could help me find a workaround.
There’s two of them but they both work in pretty much the same way so I’ll just give one example:
I created a method on the SalesLine table that counts the amount of times a specific line has been confirmed by querying the CustConfirmTrans table and running a count on the number of lines with a matching InventTransId and SalesId to the sales line. This method works fine and doesn’t seem to impact performance.
However, my employer would like to exclude from the form lines where this method returns 0 - ie. lines that haven’t been confirmed yet. As I can’t manually filter on non-data fields, I am stumped.
Sorry if I’ve missed any important information, thanks in advance for your help
SQL queries obviously can’t use X++ methods, therefore you can’t filter by them. You have a few options:
- Rewrite the logic in your display method to a query, either in AX or in SQL (you can call SQL in computed columns, which gives you some options that can’t be achieved in queries in AX).
- Instead of running your logic on display, run it when any underlying value (e.g. the amount) changes and maintain the result as a proper database field. Filtering will be trivial, nevertheless you pay the price related to denormalized data.
- Run filtering in AX instead of in database. Instead of fetching all data, you can first fetch IDs and amounts only, do the math and save passing IDs to a TempDB table, which you will join with the rest of data. It still won’t be fast, though.
Hi Martin, option 1 seems the ideal solution I would imagine - however I couldn’t find any documentation for how to create a computed column that pulls from a query, or where such a query would be stored?
Apologies, I’m still pretty new at this xD
A computed column is basically a piece of T-SQL code returning a value and put into a view. It’s just defined in a bit weird way, because table/field names in AX sometimes don’t match names in database, you don’t know names that AX will use for datasource (e.g. T1) etc.
You can see a few examples in my article about subqueries in views.