Pankaj Chaturvedi Blog: Full Text Index in AX2012

Friday, April 19, 2013

Full Text Index in AX2012

Hello Friends,

A full text index contains location information about each significant word in a string field of a table. Full text index queries can run more efficiently and complete much sooner. These are queries that search for words that are embedded in the middle of string fields in the table. Full text index can use this Index on Memo fields and also Extended data type.

A regular index on a long string field can help a query complete quickly only if the query searches for the word that is at the start of the string field. In X++ SQL this is done with the like keyword and the * wildcard character, such as in the code phrase like "*Dynamics*".

This Post describes how to create a query that uses a full text index in Microsoft Dynamics AX 2012. A full text index can improve the speed of queries that search for words that are embedded in string and memo fields on tables.

The QueryRange class has a rangeType method. You can direct a QueryRange object to use the full text index by passing the QueryRangeType::FullText enum value to the rangeType method.

Note :

1. If the value you are searching for is a two word phrase with a space between the words, the system treats the space as a Boolean OR.

2. X++ select statements cannot use a full text index.

Let me explain full text index with an example. Create a new table as shown below. In this example, my table Name is FullTextSampleTable. Once you are done with your table, Go to FullTextIndex Node >> Right click and create a new FullTextIndex. Rename it to FullTextIndex. Drag and drop TopicName field from the fields to the newly created index. Now table with index should look like below.