Creating a View In Ms Dynamics Ax

Create a View:

---

1. In the AOT, expand the Data Dictionary node, right-click Views, and then click New View.
2. Add tables as follows:
   1. Click the view that you created in step 1, and then expand the Metadata node.
   2. Click the Tables node in the Data Dictionary, and then click Open New Window.
   3. Create a parent data source by dragging a table from the new window to the Data Sources node, which is below the Metadata node. Existing views and maps can also be used as data sources in a view.
   4. Create a child data source by clicking the parent data source, and then drag a table from the new window to the Data Sources node, which is below the parent data source node.
      Repeat steps 2c and 2d to add more tables.
3. Set up relations by using one of the following procedures:
   1. To automatically add a relation between the child and parent data sources, right-click the child table that you added in step 2d, clickProperties, and then set the Relations property to Yes. Microsoft Dynamics AX follows this procedure when it automatically adds a relation. If a table relation exists between the parent and child tables, the new relation is based on the existing table relation. If there's no existing table relation between the parent and child tables, the new relation is created based on an existing relation on an extended data type.
      –or–
   2. To manually add a new relation, click the child table added in step 2d, right-click Relations, and then click New Relation.
   3. Right-click the new relation, click Properties, and then select the fields you want to link together by setting the Field and RelatedFieldproperties.
4. Add fields to the view as follows:
   1. Expand the view's Data Sources node, and then navigate to the table (or the map or view).
   2. Expand the data source Fields node.
   3. Drag a field from the data source Fields node to the Fields node under the view. Repeat steps 4a through 4c to add more fields.

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