Joins and Link Types in AX 2012

Joins and Link Types in AX 2012

Here I have created Two Tables 1.Master 2.JoinDetail

I have created a Form using the above two tables. This form demonstrates the effects of joining two datasources in a form.

  Setting the property JoinSource and LinkType in form JoinDetail DataSources level 

InnerJoin: Retrieves a record from the main table that matches records in the joined table and vice versa.

There is one record for each match. Records without related records in the other data source are eliminated from the result.

OuterJoin: Retrieves records from the main table whether they have matching records in the joined table.

ExistJoin: Retrieves a record from the main table for each matching record in the joined table.

The differences between InnerJoin and ExistJoin are as follows:

• When the join type is ExistJoin, the search ends after the first match has been found.
• When the join type is InnerJoin, all matching records are searched for.
  
  
  NotExistJoin: Select records from the main table that do not have a match in the joined table.
  
• 
  
• 
  
  Active: The data source is updated immediately when a new record in the parent data source is selected. Continuous updates consume lots of resources.
  
  
  

Passive: Linked child data sources are not updated automatically. Updates of the child data source must be programmed on the active method of the master data source.

Delayed: A pause is inserted before linked child data sources are updated. This enables faster navigation in the parent data source because the records from child data sources are not updated immediately.

 For example, you can scroll a list of orders where you do not want to review the lines associated with the order until you stop scrolling.

Temporary tables in AX 2012

In Ax 2012 we have 3 different types of tables. One type of temporary table is a TempDB table. We call them TempDB tables because their TableType property value is TempDB. This value comes from the TableType::TempDB enum value. The TableType property value can be set at AOT > Data Dictionary > Tables >MyTempDBTable > Properties > TableType.
Regular - a standard physical table
InMemory - the type of temporary table which existed in the previous versions of Dynamics Ax. Such tables are held in memory and written to a local disk file once they grow beyond a certain point
TempDB - a new option in Ax 2012. They are "physical" temporary tables held in the SQL Server database.

The new TempDB tables operate in a similar manner to InMemory tables but support more features of standard physical tables:
More powerful joins with physical tables are possible, and are properly supported by the database
Can be per-company or global
Support for normal tts transactions

To create a new instance link (populating data/copying reference) from one table instance variable to the other with Temporary type tables:
For InMemory tables, by using the setTmpData() method
For TempDB tables, the linkPhysicalTableInstance() method
replaces the setTmpData() call.

For more details on TempDB capabilities, Limitations, How to use, Its lifetime please check here - http://msdn.microsoft.com/en-us/library/gg845661.aspx

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