Introduction

There is this cool feature in Dynamics 365 for Finance and Operations: the product attributes. You can assign an arbitrary number of flexible attributes to a product. Free text, lookup from a list, integer, real, boolean values are supported. The users are super excited and their first question is going to be: “Can I search by an attribute”?
No, you cannot. At least, not in the D365FO browser UI but only at a retail Point of sales. The excitement fades, and you should quickly choose a more pleasant topic for your demo.

In fact, you can. It is super tricky to configure but once configured and saved as a query, filtering by attribute becomes pretty straightforward.

Configuration

Disclaimer: the below walkthrough only works with product attributes assigned through a procurement hierarchy. Those imposed by a Retail hierarchy through attribute groups cannot be configured for the search. Which is a pity, because the Retail hierarchy is a candy. It can be used as a collection of item templates, by far more powerful than a usual product record template.

What we need here is the CatProdSerchableAttrFilterMaterialized (sic!) table. To get that table populated, you need a procurement hierarchy and a procurement catalogue.

1. Create an attribute, for example of the a text attribute type: Product information management > Setup > Categories and attributes > Attributes.
2. Make sure there is a category hierarchy given the Procurement category hierarchy role (Product information management > Setup > Categories and attributes > Category hierarchy role associations).
3. In Procurement and sourcing > Procurement categories, select a category and add the attribute into the list of Product attributes.
4. It is essential to set the Searchable mark in this EcoResCatalogControl record. This declares a subset of attributes available for searching.
5. Choose a few released products, assign them to the above procurement category. The attribute should appear under the Product attributes button. Enter or import the attribute values.
6. Open Procurement and sourcing > Catalogs > Procurement catalogs. Create a new dummy catalog, for instance “AttributeSearch”.
7. Use the Publish catalog button. It is important to update the catalog on a recurring basis as you provide more products with the attribute.
8. Now you should get your hands on the Synchronize product search data periodic function in the Procurement catalog menu. It may be disabled, but you can invoke it by the https://xxx.operations.dynamics.com/?mi=SysClassRunner&cls=CatProductFilterRefreshCacheBatch URL.
9. This program populates the CatProdSerchableAttrFilterMaterialized table. You can configure it for recurring batch execution.

Design an advanced filter

1. 1. In the Released product list, open the Advanced filter… (Ctrl-Shift-F3) query.
   2. On the Joins tab, locate the “Products” and join the “CatProdSerchableAttrFilterMaterialized” to it.
   3. To the “CatProdSerchableAttrFilterMaterialized”, add 2 joins side by side: “Attribute” and “The base table for other value tables that each stores values of a different data type”. What a name!
   4. Finally, join the latter with one of the “The value of the Text data type for the attributes” tables depending on the type of the attribute, since every value type is stored in a separate table.
   5. By now, you should have gotten something like this:
      
   6. The goal is close. On the Range tab, apply a filter to the “Attributes (6)” and “The value of…” tables as shown below. The attribute name filter let the system search in this attribute only; there may be many searchable attributes with the same value type.
   7. Save the query for re-use and apply the filter. Enjoy the result!