Dynamics Business Central / NAV Developer Digest - Vol. 488
ArcherPoint’s Developer Digest focuses on Microsoft Dynamics 365 Business Central and Dynamics NAV development. This week’s volume includes semantic search in Business Central, streaming query data as JSON output, and overcoming the perils of tri-state locking.
The Dynamics 365 Business Central community, consisting of developers, project managers, and consultants, collaborates across various platforms to share valuable insights. At ArcherPoint, we greatly value their dedication and expertise. To ensure widespread access to this technical knowledge, we created Developer Digest.
Semantic search in Business Central
Last February, Stefano Demiliani, Dmitry Katson, and Jeremy Vyska teamed up to develop semantic search in Business Central at the BC AI Hackathon. Semantic search looks beyond finding items within the literal search prompt and uses AI to search for items that are semantically similar.
Demiliani explains how they were able to achieve semantic search using embeddings, vectors, and cosine similarity in his blog, Semantic Search and Vectors support on Dynamics 365 Business Central: this is the right moment.
In his words, “Semantic search is a search engine technology that interprets the meaning of words and phrases. The results of a semantic search will return content matching the meaning of a query, as opposed to content that literally matches words in the query.”
For example, a search for “desk” currently only returns items with the word “desk” in them. Using semantic search, a prompt for “furniture” would return items with words like “desk,” “chair,” “table,” etc.
Demiliani concludes, “If we want to have a serious AI in an ERP box, only having AL wrappers around Azure OpenAI REST APIs is not enough. But if AI features are embedded in the platform (SQL layer or exposing complex objects like Semantic Kernel as AL objects), things can really change.”
Streaming query data as JSON output
Query functions in AL have a SaveAsCSV and SaveAsXml, but there is no SaveAsJson.
Erik Hougaard shows how to take the CSV output from a query and convert it to a JSON-compatible format. He also addresses including header information and converting CSV data from text to the proper JSON variable type: Boolean, decimal, date/time, etc.
Check out A forgotten corner of AL, saving Query Output.
Overcoming the perils of tri-state locking
Microsoft has introduced tri-state locking in Business Central 2023 wave 2. Users can set the transaction isolation level to control the locking behavior during Transact-SQL reads and writes issued by a connection to SQL Server.
Mads Gram points out the motivation for this change in his post on tri-state locking: “Based on statistical data from SaaS Business Central, roughly 50 percent of all lock timeout occurs comes when reading data, not modifying data. There is no doubt that a significant amount of time in SQL can be attributed to waiting for locks.”
Duilio Tacconi encountered an issue when upgrading from BC 21.5 to 23.6: Tri-state locking using READCOMMITTED caused a client to crash.
Tacconi shows how to identify and correct this crash in his blog, The Dark Side of Tri-State Locking.
++++++++++++++++++++
Are you interested in Dynamics NAV and Business Central development? Check out our collection of NAV/BC Development Blogs.
Read “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV and Dynamics 365 Business Central.