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.