Dear fellow community members.
First a small announcement as of first of December I am in a new job where new ideas and visions for our community will come to life. I am overexcited to share more on this later at LinkedIn, but as a true enthusiast I choose to celebrate this milestone with a fun knowledge sharing blogpost to the community. The community have always been there for me when I need knowledge and paying forward is how the community choose to reward each other.
Today I would like to show you a way to create smarter search in Dynamics 365 with some minor extensions. As you know, relevance search API is on the horizon, but I expect quite a few release iterations before this will materialize into the D365 F&S codebase.
First some context; I hope you have familiarized yourself with the fulltext search capabilities that exists under the “sales and marketing”.
This feature materializes itself on the sales order screen, where you can search for products across different and multiple fields. Some of the drawbacks of the standard solution is the Microsoft have limited the number of fields you can search on to only cover fields you have on the inventTable and a few other tables. Some requirements I often get is the ability to search for barcodes/GTIN, external item numbers, vendors, attributes, classifications etc. I also get the requirement that the user do not want to be restricted in the sequence of how the search terms are specified. They want “google search” capabilities, meaning that [A] + [B] results in the same results as [B] + [A].
With some minor adjustments we can make the search for products much more meaningful. First, I need to explain how standard is performing the item search to explain how you can adjust this. There is a table named MCRInventTableIndex, that consists of 2 fields. One as a reference to the product, and one field that is a concatenation of several fields. As shown under, the Searchtext field contains item number and item description, just concatenated together. Then there have been created a fulltext index on this field to speed up the search on this long string.
When typing in your search criteria there is added a wildcard “*Criteria*” making sure that the system can find the right criteria according to the search term. The drawback here is that the search terms must be specified in the right sequence. There is also the issue that the concatenated search team is missing lot of exiting terms to search for.
So, there are 2 problems that needs to be solved:
- Adding additional search terms
- Fixing that the search sequence is irrelevant.
Lets look into that:
Adding additional search terms
The way I choose to add additional search terms, is to create my own SearchIndex table, where I can concatenate all the search terms, and include the fields like barcodes/GTIN, external item numbers, vendors, attributes, classifications. The code here is just concatenating the SearchText into a very large string, and referencing this to the product/NOBBnr. It is also OK to add multiple barcodes or vendors.
This results in having a table that have much more information, and just visualized here to show that the string contains many kinds of information.
The next stem, is to include this string into the std D365 search algorithm, so that all my search strings are included in the search.
At this time, I can search on itembarcode vendor external item etc. just as I wanted. But our goal has not been met yet. Let us go the next challenge.
Fixing that the search sequence is irrelevant.
If I should visualize how I want the search to be conducted, let us take a concrete example. Here I am searching for a door (“dør”), that is white (“hvit”) and have the size (“10X20”). As seen below, this results in to possible results.
But what we want to achieve is that the search criteria sequence should be irrelevant. What we want is to have an inner join of the search criteria’s to only get those items relevant. We therefore have to split up the search criteria, and then perform an exists join for each search criteria based on each of the separate search elements.
To make this magic happen, I have the following code, that is an extension to the MCRInventSearch class that is building up a query for the search.
This allow me to get a much better search result:
This beneficial search algorithm also kicks into the MCRSalesQuickQuote form, and here I also also added a “iframe” directly toward an external product database NOBB, that shows all relevant data.
If you enjoyed this topic, please share it. And if you want to discuss it, please contact me
Take care and see you all in my next chapter.