Full text indexes in AX 2012Continue reading
Dynamics AX 2012 uses full-text indexes maintained by Full-Text Engine for SQL Server.
Full-text indexes allow for quick searches over large volume of text data (Dynamics AX Memo fields) or even documents. You can build indexes over columns that store up to 2GB of data (nvarchar(max)).
Full-text index stores information about significant words and their location within one or more columns of a table. It is possible to define an SQL thesaurus in order to enable searches of words of similar meaning, e.g. you can search against word ‘vehicle’ and SQL will return records containing ‘cars’ and ‘bike’.
In AOT set TableGroup property to Main.
Under TableName > Full Text Indexes create new Full Text Index. Give it appropriate name and drop a field to this index. In the presented example field Notes of string size (Memo) is used.
Optional: please observe the full text catalogue created in AX Business Database.
Please edit a thesaurus for appropriate language, you will find them in
[SQL installation dir]\MSSQL11.MSSQLSERVER\MSSQL\FTData. For EN-US (1033) file name is tsenu.xml.
Find the contents used in this example below
<XML ID="Microsoft Search Thesaurus">
Load thesaurus to SQL by executing following T-SQL script
EXEC sys.sp_fulltext_load_thesaurus_file 1033;
Usage in X++ source code
To use full text indexes in X++ source code you need to create QueryBuildRange object with type FullText:
static void DevI_Ch2_FullTextIndex_Demo(Args _args)
query = new Query();
queryBuildDataSource = query.addDataSource(tableNum(DevI_Ch2_FullTextIndexed));
queryBuildRange = queryBuildDataSource.addRange(fieldNum(DevI_Ch2_FullTextIndexed, Notes),
queryRun = new QueryRun(query);
devI_Ch2_FullTextIndexed = queryRun.getNo(1);
Please observe that I search by ‘vehicle’ phrase which does not exist in the table. But thanks to thesaurus relevant records are found. If no thesaurus is employed full text searches will work but no similar words will be found.
Find data used in the example:
The outcome from script DevI_Ch2_FullTextIndex_Demo:
Please note that full text index rebuilding process takes place after transactional part of a T-SQL command. This means that when you query database immediately after inserting or updating, the engine will use ‘old’ full text index (will not find records or will find records that are not relevant anymore). Full text index is synchronised with database in asynchronous mode.
The retail industry is still dealing with the effects of the pandemic that lasted almost three years. In addition to this, further global events are causing disruption to the supply chain or financial stability of many retailers globally. As the industry confronts new challenges shaped by economic and geopolitical factors, it also faces trends influenced by changing customer expectations and needs. Here are 5 developments and trends to watch.
An interactive, AI-powered support for sales, customer service, marketing, and supply chain - Microsoft Dynamics 365 Copilot leverages generative AI and natural language processing technology to perform simple, yet time-consuming daily tasks that workload employees but can be automated.
Every company management facing a system change wonders what steps should be taken to ensure that this change does not cause turbulence to its business. Identical dilemmas are faced by any IT company that provides services to implement a new system. I will focus on these dilemmas today.
Artificial intelligence (AI) and machine learning (ML) technologies are changing business every day - just as new tools and services are frequently emerging to expand their use cases. PwC estimates that the impact of these technologies on the global economy will add approximately $15.7 trillion to its value by 2030 (1).