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.

I have added few records for testing purpose as shown below.

The following X++ Job uses a full text index that exists on a table and field that it queries.

Hope it will help you to understand Full Text Index in Dynamics AX.........

Happy DAXing...... :)

Sample XPO :
https://docs.google.com/file/d/0B7UC6CtWeBQTMVdwWXExc2xnVTA/edit?usp=sharing

Fixed Field Relation in AX 2012

Hello Friends,

This post we continue with Fixed Field Relation in Dynamics AX 2012. We use Fixed Field relation when we have 1:N relation. On certain condition one of the relation become active & other will remain deactivate. Let me use the same theme as MSDN - Clothing.

Lets say you have 4 tables

1. ChildernClothesTable
2. MenClothesTable
3. WomenClothesTable
4. ClothesOrdersTable

Create BaseEnum CollectionTypeId with the following elements:

0 - Men
1 - Women
2 - Children

Create Extended DataType CollectionTypeEDT of BaseEnum CollectionTypeId. Now use this EDT in your ClothesTable.

ClothesOrdersTable has the following fields:

1. ClotheId,
2. Name ,
3. CollectionTypeId and

4. Qty 

ChildernClothesTable, MenClothesTable and WomenClothesTable has the following fields:
1. ClotheId,
2. Name 

Case 1:

On ClothesOrdersTable we create a new relation to MenClothesTable and specify the follwing two relations:

1. Normal = ClotheId to ClotheId and

2. Fixed Field ClothesOrdersTable.CollecTionTypeId = 0 and SourceEDT = CollectionTypeEDT


This shows that the lookup to the MenClothesTable should show all clothes with the same ClotheId (Define in Normal Relation) AND clotheId will only bring from MenClothesTable as we are using type Men (Define in Fixed Field Relation) because we want to activate relationship for mens' clothes. We use 0 because Men is element 0 in the Enum.

Case 2:


On ClothesOrdersTable we create a new relation to WomenClothesTable and specify the follwing two relations:

1. Normal = ClotheId to ClotheId and

2. Fixed Field ClothesOrdersTable.CollecTionTypeId = 1 and SourceEDT = CollectionTypeEDT


This shows that the lookup to the WomenClothesTable should show all clothes with the same ClotheId (Define in Normal Relation) AND clotheId will only bring from WomenClothesTable as we are using type Women (Define in Fixed Field Relation) because we want to activate relationship for Womens' clothes. We use 1 because Women is element 1 in the Enum.

Case 3:


On ClothesOrdersTable we create a new relation to ChildernClothesTable and specify the follwing two relations:

1. Normal = ClotheId to ClotheId and

2. Fixed Field ClothesOrdersTable.CollecTionTypeId = 2 and SourceEDT = CollectionTypeEDT


This shows that the lookup to the ChildernClothesTable should show all clothes with the same ClotheId (Define in Normal Relation) AND clotheId will only bring from ChildernClothesTable as we are using type Childern (Define in Fixed Field Relation) because we want to activate relationship for Childern clothes. We use 2 because Childern is element 2 in the Enum.

Hope it will help you to understand Fixed field Relation in Dynamics AX.........

Happy DAXing...... :)

Sample XPO Link :https://docs.google.com/file/d/0B7UC6CtWeBQTWnVSWUNvUEkyZVU/edit?usp=sharing

Related Fixed Field Relation in AX 2012

HI friends,


It's not that hard to understand Relation in Dynamics AX 2012. Let me use the same theme as MSDN - Clothing.

Lets say you have 4 tables

1. ClothesTable
2. MenClothesOrder
3. WomenClothesOrder
4. ChildernClothesOrder

Create BaseEnum CollectionTypeId with the following elements:

0 - Men
1 - Women
2 - Children

Create Extended DataType CollectionTypeEDT of BaseEnum CollectionTypeId. Now use this EDT in your ClothesTable.

ClothesTable has the following fields:

1. ClotheId,
2. Name and
3. CollectionTypeId

MenClothesOrder, WomenClothesOrder and ChildernClothesOrder has the following fields:
1. OrderId,
2. ClotheId,
3. Qty

Case 1:

On MenClothesOrder we create a new relation to ClothesTable and specify the follwing two relations:

1. Normal = ClotheId to ClotheId and

2. Related Fixed Field 0 = ClothesTable.CollecTionTypeId. and SourceEDT = CollectionTypeEDT


This shows that the lookup to the clothes table should show only clothes with the same ClotheId (Define in Normal Relation) AND clothes that are of type Men (Define in Related Fixed Field Relation) because the our table deals with order for mens' clothes. We use 0 because Men is element 0 in the Enum.

Case 2:


On WomenClothesOrder we create a new relation to ClothesTable and specify the follwing two relations:

1. Normal = ClotheId to ClotheId and

2. Related Fixed Field 1 = ClothesTable.CollecTionTypeId. and SourceEDT = CollectionTypeEDT


This shows that the lookup to the clothes table should show only clothes with the same ClotheId (Define in Normal Relation) AND clothes that are of type Women (Define in Related Fixed Field Relation) because the our table deals with order for Womens' clothes. We use 1 because Women is element 1 in the Enum.

Case 3:


On ChildernClothesOrder we create a new relation to ClothesTable and specify the follwing two relations:

1. Normal = ClotheId to ClotheId and

2. Related Fixed Field 2 = ClothesTable.CollecTionTypeId. and SourceEDT = CollectionTypeEDT


This shows that the lookup to the clothes table should show only clothes with the same ClotheId (Define in Normal Relation) AND clothes that are of type Childern (Define in Related Fixed Field Relation) because the our table deals with order for Childern's clothes. We use 2 because Childern is element 2 in the Enum.

Hope it will help you to understand Related Fixed field Relation in Dynamics AX.........

Happy DAXing...... :)

Sample XPO Link :
https://docs.google.com/file/d/0B7UC6CtWeBQTeW9MY0hqZE5BejQ/edit?usp=sharing