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:

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:

Filter and Sort the records of Grid in Ax 2012 with Example

My Table:

I have a table named Bill_TabMethod with many fields. But I’am using two fields to filter. one is the name of the customer and another one is the name of the item which is purchased by the customer. Now iam going to filter the records by Customer Name as well as Item name specified by the user. for that i am using one string edit control, one button and one radio button control/

Second thing is for Sorting, for sorting i took one radio button with two items like Ascending and Descending.

CODE:

This is under form

public class FormRun extends ObjectRun

{

QueryBuildRange custname;

QueryBuildRange itemname;

}

Write this code under Datasource init() method

public void init()

{

super();

custname=this.query().dataSourceNo(1).addRange(fieldNum(Bill_TabMethod,CustName));

itemname=this.query().dataSourceNo(1).addRange(fieldNum(Bill_TabMethod,ItemName));

}

Write this code under datasource executeQuery() method

public void executeQuery()

{

if(FilterString.valueStr())

{

if(ChoiceButton.valueStr()==”CustName”)

custname.value(FilterString.valueStr());

else if(ChoiceButton.valueStr()==”ItemName”)

itemname.value(FilterString.valueStr());

}

super();

}

This is under Radiobutton Selection change Override Method

public int selectionChange()

{

int ret;

ret = super();

if(SortButton.valueStr()==”Ascending”)

{

Bill_TabMethod_ds.query().dataSourceNo(1).sortClear();

Bill_TabMethod_ds.query().dataSourceNo(1).addSortField(fieldNum(Bill_TabMethod,CustName),SortOrder::Ascending);

}

else if(SortButton.valueStr()==”Descending”)

{

Bill_TabMethod_ds.query().dataSourceNo(1).sortClear();

Bill_TabMethod_ds.query().dataSourceNo(1).addSortField(fieldNum(Bill_TabMethod,CustName),SortOrder::Descending);

}

SubmitButton.clicked();

return ret;

}

This is under Button Clicked Override method

void clicked()

{

super();

Bill_TabMethod_ds.executeQuery();

}

OUTPUTS:

Filter by Item name                                              
         

Filter by Customer Name

 

Sort by Descending                                                    
      

Sort by Ascending

 

Example for Table Methods in Ax 2012

My Table is:

I did Two methods Init value, ValidateField.

CODE:

For Init value Method:  Here iam initializing a default value to all new records which is going to create.

public void initValue()
{
super();
this.Vat=14;
}

For Validate Field Method: Here iam restricting each filed by some condition

public boolean validateField(FieldId _fieldIdToCheck)
{
boolean ret;

ret = super(_fieldIdToCheck);

if(ret)
{
switch(_fieldIdToCheck)
{
case fieldNum(Bill_TabMethod,Quatity):
if(this.Quatity <5)
{
error(“Should give more than FIVE”);
this.Quatity=0;
}
break;
case fieldNum(Bill_TabMethod,Price):
break;
case fieldNum(Bill_TabMethod,Discount):
if(this.Discount>45)
{
this.Discount=0;
error(“Discount Should be Less than 45”);
}
break;
}

}

this.TotalAmt=(this.Quatity * this.Price);//-this.Discount;
this.TotalAmt=this.TotalAmt-this.TotalAmt*(this.Discount/100);

this.NetAmt=this.TotalAmt+this.TotalAmt* (this.Vat/100);

return ret;
}

This is the Output Error while checking the condition on Discount Percentage.

Financial Dimension Customization

Disable financial dimensions in AX 2012 through code

Link:http://shankardynamicsax.blogspot.in/2013/11/disable-financial-dimensions-in-ax-2012.html

Only show required Financial demensions in AX 2012 R2

Link:http://shankardynamicsax.blogspot.ae/2014/04/only-show-required-financial-demensions.html

Importing Workers Image using Excel in AX 2012

Link:http://shankardynamicsax.blogspot.in/2013/08/importing-workers-image-using-excel-in.html

How to add a new financial dimension in AX 2012 and link to existing table.

Link:http://shankardynamicsax.blogspot.in/2012/06/how-to-add-new-financial-dimension-in.html

Custom Lookup for Dimensions in AX 2012 – for User Defined Dimensions

Link:https://paruvellas.wordpress.com/2013/04/19/custom-lookup-for-dimensions-in-ax-2012-for-user-defined-dimensions/

Code for inserting 10 records in a table for an integer field and string field through the job

static void shiv_autoincrement2(Args _args)

{

 auto_s autoinc;

    int i;

    int j;

    int k;

    int l=1;

    str alpha;

    ;

    j=97;

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

    {

        autoinc.Autoid=i;

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

        {

        alpha+=num2char(j);

        }

        autoinc.autoname=alpha;

        alpha="";

        j++;

        autoinc.insert();

    }

    info("inserted");

}

code for inserting 10 records at a time with auto increment via job in a table

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");

    }

}

Form Control Properties in AX 2012

EDT Properties and EDT field Properties in a Table & Table,FormDataSource ,Form Design Properties in ax 2012

SNO	EDT properties	Table Field Properties of EDT
1	Alignment	Adjustment
2	Analysis Default Sort	AliasFor
3	Analysis Default Total	AllowEdit
4	Analysis Grouping	AllowEditOnCreate
5	Analysis	AnalysisDefaultTotal
6	Usage	AnalysisLabel
7	Array Length	AnalysisUsage
8	Button Image	ConfigurationKey
9	Collection Label	CountryRegionCodes
10	Configuration Key	CountryRegionContextField
11	Country Region Codes	ExtendedDataType
12	Display Length	GroupPrompt
13	Enum Type	HelpText
14	Extends	ID
15	Form Help	IgnoreEDTRelation
16	Help Text	Label
17	ID	Mandatory
18	Label	MinReadAccess
19	Model	Model
20	Name	Name
21	Presence Class	RelationContext
22	Presence Indicator Allowed	SaveContents
23	Presence Method	StringSize
24	Reference Table	Type
25	Style	Visible

SNO	Table Properties	Form Data Source Properties	Form Design Properties
1	Abstract	Allow Check	AlignChild
2	Analysis Dimension Type	Allow Create	Align
3	Analysis Identifier	Allow Delete	Children
4	AOS Authorization	Allow Edit	Allow Docking
5	Cache Lookup	Auto Notify	Allow Form Company Change
6	Cluster Index	Auto Query	Allow User SetUp
7	Configuration Key	Auto Search	Always On Top
8	Country Region Codes	Counter Field	Arrange Method
9	Country Region Context Field	Cross Company Auto Query	Arrange When
10	Created By	Delay Active	Background Color
11	Created Date Time	Index	Bottom Margin
12	Created Transaction Id	Insert At End	Caption
13	Create RecId Index	Insert If Empty	Color Scheme
14	Developer Documentation	Join Source	Columns
15	Entity Relationship Type	Link Type	Column Space
16	Extends	Name	Data Source
17	Form Ref	Only Fetch Active	Font
18	ID	Optional Record Mode	Frame
19	Is Lookup	Start Position	Height
20	Label	Table	Hide If Empty
21	List Page Ref	Valid Time State Auto Query	Hide Tool Bar
22	Model	Valid Time State Update	Image Mode
23	Modified By		Image Name
24	Modified Date Time		Image Resource
25	Modified Time		Label Font
26	Name		Left
27	Occ Enabled		Left Margin
28	Preview Part Ref		Maximize Box
29	Primary Index		Minimize Box
30	Replacement Key		Mode
31	Report Ref		Model
32	Save		Right Margin
33	Data		Save Size
34	Per		Scroll Bars
35	Company		Set Company
36	Save Data Per Partition		Status Bar Style
37	Search Link Ref Name		Style
38	Search Link Ref Type		Title Data Source
39	Singular Label		Top
40	Support Inheritance		Top Margin
41	System Table		Use Caption From Menu Item
42	Table Contents		View Edit Mode
43	Table Group		Visible
44	Table Type		Width
45	TitleField1,		Window Resize
46	TitleField2		Window Type
47	Typical Row Count		Workflow Data Source
48	Valid Time State Field Type		Work flow Enabled
49	Visible		Workflow Type