Filtering by relative record ID

This should be easy enough – I want a list of customers that don’t have e-mail addresses.

Sales and marketing > Common > Customers > All customers shows me the phone number:

I know that I can add the e-mail address to the Contact information:

Back on the list grid, I can customise it to add the e-mail address, but if I try the ‘More…’ option on the filter it doesn’t give me the options I want.

More gives me:

But there’s no e-mail address on the customer.

The problem here is that we are adding these contact details to the Global address book record related to the customer, not the customer table record.

Right-click > Personalize gives me more table options:

Expand the Global address book table and I can add E-mail address to the list grid:

And for good measure, I’ve added the global address book party ID as well.

Always a good idea to close and re-open a form after personalisation. I see:

Easy! So now filter for customers that have e-mail addresses:

And filter for customers that don’t have an e-mail address:

OOPS. What’s going on? Well let’s take a look at some global address book records in the table browser:

The system isn’t storing an e-mail address on the global address book, but a record id. We come across this pattern more often now in Dynamics AX 2012 because of the implementation of surrogate keys. A good, non-technical, explanation of surrogate keys is here.

In this example the customer form is automatically translating the rec-id found on the global address record to the e-mail address (foreign key replacement).

So thinking about this a bit more, the filter works when I ask the customer form to filter by e-mail address, because the filter’s actually being applied first on the e-mail address table. But when there isn’t an e-mail address that filter can’t work, both because there aren’t any records to filter, and also because there’s no ‘record does not exist’ option in a form filter query.

However there is a solution. A while ago I posted some options for form filter queries. I had to be reminded of this by a colleague (Ievgen Miroshnikov), but one of the options is in the form of an SQL statement that will evaluate back to true or false between parenthesis (). Specifically here I want to search for global address book records where the record id linking to the e-mail address is zero. The syntax for this is (data source.fieldname == value). You must use double equals signs for ‘is equal to’ and ‘!=’ for ‘is not equal to’.

Probably best to put the filter on the key field of the record you’re trying to filter like this:

I can’t pretend that it’s easy to type a filter like that, but remember that you can name and save filters.

Yes, there’s a post on saving filters.

I think this is workable – but if you have a better solution, please leave a comment.

How to use advanced filter queries in AX 2012

In AX, almost all list gird forms can be filtered by the Advanced filter form.

On any form, go to File > Edit > Filter > Advanced Filter/Sort (Ctrl-F3):

You’ll know that you can select pretty much any Field to filter by, and in the ‘Criteria’ field you can use various simple wildcards like *, for instance:

Wildcard Description For example, if used to filter the Vendor account field as above
A specific value Any specific value Returns just the one record where that field matches the entered value
* Any character or any number of characters * = All accounts

A* = all accounts starting with A

*B = all accounts ending in B

*C* = all accounts with a C anywhere in the account

D*E = all accounts starting with D and ending in E

? Any single character ?????? = all six digit or six character account codes

??DE?? = all 6 digit accounts codes where DE and are the 3rd and 4th characters

??FG* = all account codes where F and G are the 3rd and 4th characters

.. (double-period) Between Happy .. Sad = All account codes greater than ‘Happy’ and less than ‘Sad’

.. Happy = All accounts less than or equal to ‘Happy’

Sad .. = All accounts greater than or equal to ‘Sad’

You’ll also know that you can combine and reverse all of these filters:

Wildcard Description For example, if used to filter the Vendor account field
, (comma) All filters specified as separated by comma Happy, Sad, Z* = Account code Happy, Account code Sad and all accounts starting with a Z.
! (explanation mark) Not / reversal !A* = All accounts except those starting with an A.

There is also one commonly used special filter:

Wildcard Description For example, if used to filter a Date field
T or today() Today’s date .. today() = all invoices with a date less than or equal to today’s date

However, many people don’t know that you can also define simple field comparisons. You can enter this in as the criteria on any field (not just the fields being compared):

SQL statement Description For example, if used to filter the Vendor account field above
(SQL Statement) An SQL statement that’ll evaluate back to true or false between parenthesis () (VendInvoiceJour.InvoiceAccount != VendInvocieJour.OrderAcount) will select all vendor invoices where the invoice account is different from the vendor account

There are also advanced filter queries based on the fact that there is a generic filtering class called SysQueryRangeUtil.

Here are some examples:

Date based queries Description For example
(Day(n)) Number of days .. (Day(-1)) = All dates up to and including yesterday

(Day(2)) .. = All dates greater than or equal to the day after tomorrow

(DayRange(n,m) Date range (DayRange(-30,30)) = Previous 30 days and future 30 days

The System query range utility is the foundation for a whole family of specific and useful filter queries, and it is possible to customise the method to include customised filters. The Cues found on the standard Dynamics AX role centres give a number of examples on how these filters can be used, for instance:

Table Field Example filter
Actions Person in charge (currentEmployeeId())
Actions End date/time (lessThanDate(14))
Activities Responsible ((ResponsibleEmployee == currentEmployeeId()) || ((ResponsibleEmployee != currentEmployeeID()) && (Sensitivity != 2)))
Activities End date/time (lessThanDate(0))
Order lines Ship date .. today()
Open customer transactions Due date (Day(0))
Net requirements Plan (currentstaticmasterplan())
Net requirements Reference (( ReqTrans.RefType == 8) || (ReqTrans.RefType == 33))
Employee dates Trial period (dayRange(0,30))
Loan Planned return ((lessThanDate(14))&& (schedReturnDate != “”))
Leads Owner ((OwnerId = currentEmployeeId())&& ( OwnerId != “”))
Activities Responsible ((ResponsibleEmployee == currentEmployeeId()) && (ResponsibleEmployee != “”))
Leads Date opened (greaterThanDate(-8))
Vendor invoice journal Due date (day(0))

For more information check out the on-line help page at: