Free text index

Full Text Index in Dynamics AX 2012 – X++

August 9, 2011 — Sreenath Reddy

Friends,

Full text index supports to quickly query words that are embedded in the middle of a string field of a table. Well, this is a very nice enhancement to query on Database table fields for the developers who work with the latest vesion [Microsoft Dynamics AX 2012]

Good thing is we can use this Index on Memo fields and also Extended data type.

Let me explain with an example. Create a new table as shown below and add a new field of type string “Name” to it – On the field use EDT – Name. In the below example, my table Name is SR_FullTextExample.

Once you are done with your table, Go to FullTextIndex Node >> Right click and create a new FullTextIndex.
Rename it to NameFullTextIndex. Drag and drop Name field from the fields to the newly created index.

The table with index should look like below.

I have added some dummy records/data for testing purpose as shown below.

Now, let us see how to use this FullTextIndex for searching a field with the words.

Please Note: X++ select statements cannot use a full text index

A full text index can improve the speed of queries that search for words that are embedded in string and memo fields on tables. [MS help]

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. [MS help]

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 [MS Help]

Below job will help to search the strings with in a full string in the fields.

static void SR_FullTextQuery(Args _args)

{

    Query                   query;

    QueryBuildDataSource    qbds;

   

    QueryBuildRange         queryBuildRange;

    QueryRun                queryRun;

    SR_FullTextExample      sr_FullTextExample;

   

    query = new Query();

    qbds = query.addDataSource(tableNum(SR_FullTextExample));

    queryBuildRange = qbds.addRange(fieldNum(SR_FullTextExample, Name));

    queryBuildRange.rangeType(QueryRangeType::FullText);

    // The space character is treated as a Boolean OR.

    queryBuildRange.value(“Sreenath Kumar”);

    queryRun = new QueryRun(query);

    while (queryRun.next())

    {

        sr_FullTextExample = queryRun.get(tableNum(SR_FullTextExample));

        info(sr_FullTextExample.Name);

    }

}

As you can see, the results displayed is purely based on the search using ‘OR’ condition.
==============================================================

Example:

/*
   Two important rules for creating Full Text Indexes

[Technet]
TableGroup property.
    A table can have a full text index only if the TableGroup property
    is set to Main or Group on the table.

    A full text index can only be created on a table with RecId index.
    So make sure 'CreateRecIdIndex' property flag on table is set to Yes
*/

static void FullTextIndexesDemo()
{
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDatasource;
    QueryRun                queryRun;
    QueryBuildRange         queryRange;
    FullTextIndexesDemo     fullTextIndexes;

    queryBuildDatasource = query.addDataSource(tableNum(FullTextIndexesDemo));
    queryRange           = queryBuildDatasource.addRange(fieldNum(FullTextIndexesDemo, 
                 AxVersion));
    queryRange.rangeType(QueryRangeType::FullText);
    /*
    [Technet] Space characters are treated as implicit Boolean OR operators.
    There is a space character in the string parameter in call
    queryBRange4.value("dynamics 0");.
    */
    queryRange.value('dynamics 0');

    queryRun = new QueryRun(query);

    while (queryRun.next())
    {
        fullTextIndexes = queryRun.get(tableNum(FullTextIndexesDemo));
        Debug::printTab(DebugPrintTab::ActiveX, fullTextIndexes.AxVersion);
    }
}