Surrogate Key

Surrogate Key

When a primary index is not specified, Microsoft Dynamics AX uses a Surrogate Key as the primary index. This key is the RecId field and, if the table is saved per company, the DataAreaId. The surrogate key is used on many relations between tables.

Form Query

Use the query object to modify the query of a form data source. The query object can be retrieved using either <name of data source>_Q or FormDataSource.Query()

To make a permanent modification of the query, this is typically implemented in FormDataSource.Init() after the call to super().

To filter records in a form perform the following steps.

1.      In the ClassDeclaration, declare the relevant QueryBuildRange or QueryFilter objects.

2.      In FormDataSource.Init, initialize the range object.

3.      In FormDataSource.ExecuteQuery, assign the actual values to the ranges before call of super().

Combine the sorting with some aggregated fields, which makes the data source display aggregate information from the table instead of transactions. Perform the following steps to show the sum of quantity of inventory transactions shown per Item Id:

1.      Group the data by item id using the addGroupByField on the datasource.

Add Sum(Qty) as a SelectionField

public void init()
{
    QueryBuildDataSource dataSource;

    super();

    dataSource = this.query().dataSourceTable(tableNum(InventTrans));
    dataSource.addGroupByField(fieldNum(InventTrans, ItemId));
    dataSource.addSelectionField(fieldNum(InventTrans, Qty), SelectionField::Sum);
}

Args

If the caller is activating the called object by a menu item the Args object is automatically initialized and sent as a parameter to the object called. AOT properties of the menu item will be used.

If the called object is a form or a report, it can automatically apply to the information send by the Args object. Forms will automatically try to make a delayed synchronize with the args.record().

Query -- Exists Join

Combine records from one table whenever a value exists in a common field in another table.

Display inventory items the providing vendor who has been blocked.
    InventTable inventTable;
    VendTable vendTable;
    ;

    ttsbegin;

    while select forupdate inventTable
     exists join vendTable
      where vendTable.AccountNum == inventTable.PrimaryVendorId &&
vendTable.Blocked == CustVendorBlocked::All
    {
        inventTable.PrimaryVendorId ="";
        inventTable.update();
    }
    ttscommit;

Display only those customers which have at least one sales order in sales table. 

Query                   query;

QueryBuildDatasource    datasource,SalesDataSource,CustomerDataSource;

QueryRun   queryrun;

CustTable custtable;

SalesTable salesTable; 

query = new Query();

CustomerDataSource  = query.addDataSource(tableNum(CustTable ));

SalesDataSource  = CustomerDataSource.addDataSource(tableNum(SalesTable ));

SalesDataSource.joinMode(JoinMode::ExistsJoin   );

SalesDataSource.relations(false);

SalesDataSource.addLink(fieldNum(CustTable, AccountNum),

fieldNum(SalesTable  , CustAccount ));

queryrun = new QueryRun(query);

while(queryRun.next())

{

custtable = queryRun.get(tablenum(CustTable ));

Info(custtable.AccountNum);

}

Query

The queryBuildRange object contains a limitation of the query on a single field.

The queryFilter object is used to filter the result set of an outer join. It filters the data at a later stage than 

the queryBuildRange object and filters the parent table based on the child table results.

The queryBuildDynaLink objects can only exist on the outer data source of a query. The objects contain information about a relation to an external record. When the query is run, this information is converted to additional entries in the "where" section of the SQL statement. The function is used by forms when two data sources are synchronized. The subordinate data source contains DynaLink(s) to the master data source. The function is used even if the two data sources are placed in two different forms, but are synchronized.

The queryBuildLink objects can only exist on inner data sources. The objects specify the relation between the two tables in the join.