How to create Full Text Index in Dynamics AX 2012 with Example

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 FullTextIndexTextTable.

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

The table with index should look like below.

 

Here I’ve created a input string edit control  ( for user input ) and  a submit button.

CODE:

This is a code in form

public class FormRun extends ObjectRun

{

QueryBuildRange qbr;

str localValue;

}

This code is in Form Datasource override mehtod executeQuery()

public void executeQuery()

{

localValue = NameInput.valueStr();

if(localValue)

{

qbr = SysQuery::findOrCreateRange(FullTextIndexTextTable_ds.queryBuildDataSource(),fieldnum(FullTextIndexTextTable, Name));

qbr.rangeType(QueryRangeType::FullText);

qbr.value(localValue);

}

super();

}

This code in Button Clicked() override method

void clicked()

{

super();

FullTextIndexTextTable_ds.executeQuery();

}

Output:

inserting 10 records at a time with auto increment via job in a table in ax 2012

static void shiv_autoincrement(Args _args)

{

    auto_s auto; //table declaration

    int i ;

    ;

     for(i=0;i<=10;i++)

    {

        auto.Autoid=i; //inserting “i” value to the field “Autoid”

        auto.insert();

        info("inserted");

    }

}

How to use Reference Group in a form in AX

Ø  Reference Group control of a form will show the User needed fields like Name or any other specific field instead of a RecId but will save only RecId upon user selection in the backend. To use this Feature we need to use Replacement Key of a Table.

Ø  Index Specified in Replacement key of a table will be shown instead of RecId in the form. Following Example will briefly explain the creating and use of Replacement Key and Reference Group Field Control of a Form. 

Ø  Important: This concept will only work upon having the table relations with RecId, as it’s a MS Design Framework.

v  Create two tables

• Parent_Table
•  Child_Table

v  Create three fields in Parent_Table

•     AccNo(String)
•     Name(String)
•     Phone(String)

o    Create an index in Parent_Table

NameIdx

Properties:

     AllowDuplicates :NO

     Alternatekey      :Yes

o    Drag drop name field of Parent_Table into NameIdx index.

o    Parent_Table
Properties

                  Replacementkey    : NameIdx

v  Create Three fields in Child_Table

• Name(Int64)
•  Address(String)
•  ZipCode(String)

o    Create a relation between Name field of Child_Table and RecId of Parent_Table.

Relations of two Tables:

Set field                : Name
Set RelatedField  : RecId

v  Create a form

•   ReferenceGroupForm

o    Add Parent_Table and Child_Table to datasource of form.

o    Select join source to child datasource and set it to Parent_Table.

o    Create 2 grids (for parent_Table and Child_Table)

o    Set Grid1 datasource : Parent_Table

o    Set Grid2 datasource: Child_Table

•     Add fields of Parent_Table data source to grid1.
•    Add fields of  Child_Table data source to grid2.

NOTE: While creating referecegroup, use Int64 datatype for that field which is used. Because we need to create a relation between a field of child_table(Name) and parent_table(RecId). We know that RecId is Int64 type. Reference Group concept will only work on RecIds.

OUTPUT:  As we can see in form now, top grid is containing data from parent table and below grid

is containing data from child table. When we create new record in child table it will show all the

name record from parent table instead of RecId(Related field). But in  backend(Child_Table) it will save the recId of Parent_Table.

• Now, In picture it is clear how the value coming in front end (form) and save in backend (table)

String Functions in AX 2012

1.strAlpha Function:

Copies only the alphanumeric characters from a string.

Syntax:

str strAlpha(str _text)

Example:

info(strAlpha("Shiva89^&"));

Op:shiva89

2.strCmp Function :

Compares two text strings.

Note: 0 if the two strings are identical, 1 if the first string sorts earlier, or -1 if the second string sorts earlier.

Syntax:

int strCmp(str text1, str text2)

The comparison performed by this method is case-sensitive.

Example:

print strCmp("shiva","shiva");

        pause;

Op:  returns 0 if two strings are identical

print strCmp("shiva","SHI");

        pause;

Op:  returns 1 if first string is executed first;

print strCmp("sss","ggg");

        pause;

Op: returns 1 if the second string executed first;

print strCmp("hhh","ooo");

        pause;

Op: returns -1 if the second string executed first;

3.strColSeq Function:

Converts all uppercase characters to lowercase characters and also converts all special  design characters like (ÁÎ to ai) to lowercase characters

Syntax:

str strColSeq(str text)

Example:

info(strColSeq("AbçDËãBCdé"));

Op:abcdeabcde

 4.strDel Function:

Creates a copy of a string with the specified substring removed.

Syntax:

str strDel(

    str _text,

    int _position,

    int _number)

Example:

using strDel function Note: removes 2 letters from 4th letter onwards

info(strDel("ShivaKumar",4,2));

Op:ShiKumar

info(strDel("ShivaKumar",4,-3));

Op: SaKumar

Parameters

Parameter	Description
_text	The string to copy from
_position	The position at which to begin ignoring characters during the copy.
_number	The number of characters to ignore. A minus sign in front of the _number parameter indicates that the (_number-1) characters before the character at the_position parameter are to be removed along with the character at the _position.

 

5.strFind Function:

Searches a string for the first occurrence of one of the specified characters.

Syntax:

int strFind(

    str _text, 

    str _characters, 

    int _position, 

    int _number)

Parameter	Description
_text	The string to search.
_characters	The characters for which to search.
_position	The position in the string where the search begins.
_number	A signed number that indicates the direction of the search and how many positions to search in the string.

 Example:

print strFind("shivaKumar","v",1,10);

    pause;

OP: returns 4

print strFind("shivaKumar","i",6,-4);

    pause;

OP: returns 3

Note: returns 4 and 3 according to the position of given value;

6.strFmt Function:

Formats the specified string and substitutes any occurrences of % n with the nth argument.

Syntax:
str strFmt(str _string, ...)

Example:

static void Shiv_StringsFunction(Args _args)

    {

    str Ax= 'Axapta';

    str W ='World';

    ;

  

     {

       info(strFmt(" Welcome to %1,%2",Ax,w));

   

       }

}

Op: Welcome to Axapta,World

7.strIns Function:

Builds a string by inserting one string into another.

Syntax: str strIns(str _text1, str _text2, int _position)

 Example:

static void Shiv_StringsFunction(Args _args)

{

info(strIns("shKumar","iva",3));// Note:Inserting string  from 3^rd letter onwards

}

Op:shivakumar

8.strKeep Function:

Builds a string from only the characters in the first input string that are specified to be kept by the second string.

Or

Shows only specifield value of string

Syntax: str strKeep(str _text1, str _text2)

Example:

info(strKeep("shivaKumar","vaK"));

Op: VaKa

9.strLen Function

Calculates the length of the specified string.

Syntax: int strLen(str text)

Example:

print strLen("shiva");

    pause;

Op:5

10.strLine Function:

Retrieves a single line from a string that spans multiple lines.

Syntax:

str strLine(str string, int count)

Example:

str Ax="Hello\nDaxers\r\n@India\n@dhanush";

    {

        info(strLine(Ax,0));

        info(strLine(Ax,1));

        info(strLine(Ax,2));

       

    }

Op:

Hello

Daxers

@ India

11.strLTrim Function:

Removes leading blanks from a text string. Or removes  left side space of the string

Syntax:

str strLTrim(str text)

Example:

info(strLTrim("   Shiva   "));// op:Shiva

12.strRTrim Function :

Removes the trailing space characters from the end of a string. Or removes right side space of the string value

Syntax: str strRTrim(str _text)

Example:

info(strRTrim("   Shiva   "));// op:   Shiva

13.strLwr Function:

Converts all letters in the specified string to lowercase.

Syntax:

str strLwr(str _text)

Example: info(strLwr("SHiVa")); Op: shiva

14.strNFind Function:

Searches a portion of a text string for the first occurrence of a character that is not included in the specified list of characters.

Syntax:

int strNFind(

    str _text,

    str _characters,

    int _position,

    int _number)

Example:

print strNFind("shiva","shva",1,5); //op: returns 3(position of i)

    pause;

print strNFind("shiva","shIva",1,5); //op: returns 0(position of i)

    pause;

15.strPoke Function:

Overwrites part of a string with another string.

Syntax:

str strPoke(

    str _text1,

    str _text2,

    int _position)

 Example:

info(strPoke("shiva","kumar",4)); //op:shivkumar

16.strPrompt Function:

Appends to a string the specified number of period characters followed by a colon and space character.

Syntax:

str strPrompt(str _string, _int len)

Example:

print strPrompt("Shi",-1); //Returns "Shi ".

print strPrompt("Shi",3); //Returns "Shi ".

print strPrompt("Shi",4); //Returns "Shi: ".

print strPrompt("Shi",5); //Returns "Shi.: ".

print strPrompt("Shi",6); //Returns "Shi..: ".

    pause;

 

17.strRem Function:

Removes the characters specified in one string from another string.

Or

Removes the specified value of a string

Syntax: 

str strRem(str text1, str text2)

 Example:

io(strRem(nf"shiva","va")); //op:shi

18.strRep Function:

Repeats a string of characters.

Syntax:

str strRep(str _text, str _number)

Example:

info(strRep("shiva",5)); //Op:shivashivashivashivashiva

   

19.strScan Function:

Searches a text string for an occurrence of another string.

Syntax:

int strScan(

    str _text1,

    str _text2,

    int _position,

    int _number)

Example:

print strScan("shivaKumar","h",9,-10);//op:2

    pause;

print strScan("shivaKumar","iva",1,10);//op:3

    pause;

20.strUpr Function:

Converts all the letters in a string to uppercase.Syntax:

str strUpr(str _text)

 Example:

info(strUpr("SHiva"));// op:SHIVA

21.subStr Function:

Retrieves part of a string. Or retrieves we required value a part from the string

Syntax: str subStr(str _text, int _position, int _number)

Example:

info(subStr("ShivaKumar",3,6));//op:vakum

Note:Retrives String value from 3^rd value  onwards up to 6^th letter

info(subStr("ShivaKumar",6,-4));

Note: Retrives String value from 6th value  towards  to 4^th letter

22.match Function:

Searches for a string or expression within another string.

Syntax:

int match(str pattern, str text)

Example: print match("<shi","shivakumar");//op: returns 1

pause;

print match("^shi","shivakumar");");//op: returns 1

pause;