3

I have created a new table QueryCodes and used the new surrogate key functionality in 2012.

A Foreign key relationship has been added to SalesTable using a RefRecId as per the new RecID foreign key guidelines. This all works great and I can now select a QueryCode on the SalesTable by a descriptive field rather than a drop down full of RecIds.

However - Filtering! In any grid showing all sales orders (SalesTable) I cannot filter for "Blank" query codes, ie SalesTable records without a query code attached. I can filter the sales table perfectly using the descriptive field from QueryCodes (for the record it is called "name").

For example, if I filter by QueryCode "Q_1" on SalesTable I see all "Q_1" queried sales orders.

When filtering by "" (blank) I see all orders, both with and without a QueryCode populated.

Is this a limitation in the new RefRecId functionality or is there something I need to change to be able to filter a blank foreign key reference field?

Edit;

The Customers list page can be used to see a standard example of this issue, personalize the grid to add "employee responsible" (the second in the list, the first is deleted). This is referenced by rec id and filtering the field by blank does not work. Filtering by employee name or not blank does (!"").

AnthonyBlake
  • 2,334
  • 1
  • 25
  • 39
  • Can you demonstrate using a standard RecId foreign key instead? – Jan B. Kjeldsen Jan 11 '14 at 13:34
  • @JanB.Kjeldsen had a brief search for an example, let me know if you think of any – AnthonyBlake Jan 11 '14 at 18:20
  • I believe the issue is that you're not searching for blanks so much as AX is attempting to search for blanks instead of 'doesn't exist'. I've had this issue for filtering in the past. I solved it by spinning my own filter functionality that combined searching for `""` and the `NotExist` join. I hope this helps. – Kevin DeVoe Jan 13 '14 at 14:40
  • its starting to look as though I am going to have to go back to the old way of having a "queryid" as the foreign key which is a shame – AnthonyBlake Jan 14 '14 at 08:30
  • @JanB.Kjeldsen Hey Jan, I have found a standard example and added to my question – AnthonyBlake Jan 15 '14 at 11:11

1 Answers1

3

Okay, I am of the opinion that this is a bug. However I have found a partial workaround.

In the example above a filter for a specific Employee Responsible name (including wildcards) will work, however a NOT filter or a blank filter will not.

To filter for blanks I would type the following in the filter field to query for a zero RecId directly;

(CustTable.MainContactWorker == 0)

or non blanks;

(CustTable.MainContactWorker != 0)

This does not fix the issue where filtering for something like !Anthony* does not include blanks, but it will allow a list of blanks.

AnthonyBlake
  • 2,334
  • 1
  • 25
  • 39
  • You can select non-blanks: !"" – Jan B. Kjeldsen Jan 16 '14 at 13:31
  • @JanB.Kjeldsen that is exactly the problem I have posted. you CANT when the field is a reference group linked by recid. the filters are broken. – AnthonyBlake Jan 16 '14 at 13:32
  • You cannot select blanks: "", but you can select non-blank (!""), if you do it by filtering (ctrl-k). If you use the filter-box you can use exact match only, not !?* etc. – Jan B. Kjeldsen Jan 16 '14 at 13:42
  • ah okay yes, !"" does work, i misread the crucial "!" there. The part of the filter which is broken is any which should return blanks as part of the result set. – AnthonyBlake Jan 16 '14 at 13:53
  • In effect the search of a field in a reference group is translated as an `inner join` of the corresponding reference data source. The search for blank should be translated to a `notexists join`. Whether this is a bug or not is open for interpretation. – Jan B. Kjeldsen Jan 16 '14 at 13:54
  • @JanB.Kjeldsen Sure it is - the user doesnt care about database joins, the user whats !"xyz" to include blanks, as it does using the old method of linked ID, which I can absolutely understand. – AnthonyBlake Jan 16 '14 at 14:05
  • Feel free to report the issue to Microsoft through the partner channel. – Jan B. Kjeldsen Jan 16 '14 at 14:52