Over the years many Dynamics NAV customers and Microsoft partner have developed their own solutions for full text search in Dynamics NAV.
The challenge is as simple as it can be: One search field for typing in keywords getting a list of entries over multiple tables and columns as a result. The usability should be like what is known from Google or Bing search sites.
When using what Dynamics NAV offers, the programming of such feature comes with certain limitations in comfort or expanding expenses in development. When you rely on the good old C/AL, you must define upfront in the code, which tables and Columns you want to include in the search. Also, the composition or search strings comes close to re-inventing the wheel. In the end, the performance of this search solution is limited by Dynamics NAV as a platform as well as through the local hardware infrastructure of the Dynamics NAV Server and the SQL Server.
Taking a shortcut through C/AL-territory by using the Dynamics NAV .net libraries for SQL queries, you can use some commands like “join” that is unthinkable in C/AL. Also in using SQL syntax, the handling of search strings might be easier than in C/AL too. Direct SQL queries skip the Dynamics NAV Service tier architecture, and therefore eliminates one of the bottlenecks.
But ultimately, when search strings become more and more complex, the tables and columns multiply or the hours of development pile up, the result is always the same. Performance decreases and user most likely spend a good amount of their workdays watching the sand watch turn, while waiting for their search results.
Luckily, Microsoft has solved this problem and serves it on a silver platter: Microsoft Azure search. This SaaS offer by Microsoft enables us to implement complex keyword search functions easily and allows to waive performance and algorithm optimization issues. Tables are mirrored into the service creating indexes on which Azure Search performs its “magic”. When an API request is sent to Azure Search, the result is delivered in a fraction of a second, all through the power or the Azure Cloud. These results are then matched with data from the Dynamics NAV Database, ensuring that only primary keys are delivered in the JSON response, not the whole dataset.
Initializing the search index takes a couple of minutes, especially with large databases like the one our customer runs. Synchronizing the data can be done on the event of change in the data or through job que in NAV
By using Azure Search, we could implement a powerful search even in an On-Premise-Installation for the customers NAV 2016 update without the need of a large Invest in new hardware. At the same time our customer is ready for more growth, as Azure Search is highly scalable on demand. Also, the user-productivity has gone up significantly and sustainable as search results are now answered in real time.
