• There is a maximum of one Primary Key per table, whereas a table can have several alternate keys. The primary key is usually the type of key that other tables, called child tables, refer to when a foreign key field in those other tables need a relational identifier.

  • For new tables the default is a primary key based on the RecId field , incremented number or a completely meaningless number that is generated by the system surrogate key.

  • As The RecId data type, surrogate keys exist on a primary key table. As The RefRecId ETD, surrogate foreign keys exist on a foreign key table (Ex: Party field is on CustTable Table).

  • Alternate key can be chosen as the Replacement Key of a table that can display on forms instead of a meaningless numeric primary key value. Each table can have a maximum of one replacement key.

  • Natural key has meaning to people. A set of fields that uniquely identify a record and would have formed the primary key of the table, if not for the existence of a surrogate key.

  • Relations represents a foreign key.

Thank you for reading!

InventSum is needed to recalculate sometimes. We should use InventSumRecalcItem class in Dynamics AX.

InventSumRecalcItem InventSumRecalcItem;
;
InventSumRecalcItem = new InventSumRecalcItem("ITEM001", true, checkfix::fix);
InventSumRecalcItem.updatenow();

First parameter : ItemId

Second parameter : Show errors

Third parameter : Fix or only check

What if you want to calculate for all items:

InventTable InventTable;
InventSumRecalcItem InventSumRecalcItem;

WHILE SELECT InventTable
	WHERE (InventTable.ItemType == ItemType::Item) || (InventTable.ItemType == ItemType::BOM)
	{
		InventSumRecalcItem = new InventSumRecalcItem(InventTable.ItemId, true, checkfix::fix);
		InventSumRecalcItem.updatenow();
	}

Thank you for reading!

I got this error at version Dynamics 2012 R3 CU9, SQL Server 2014, Windows Server 2012 R2.

Those kind of errors will come when you move report from Dev Server to Live Server, even compilation in DP Class, Query, Contract, table … without any error

AX1004: Reference System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 was not a valid model assembly.

MSB3644: The reference assemblies for framework “.NETFramework,Version=v4.0” were not found. To resolve this, install the SDK or Targeting Pack for this framework version or retarget your application to a version of the framework for which you have the SDK

Cause

There is no reference Assemblies Version 4.0 under C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework on Windows Server 2012 R2 (only Version 4.5 it have)

Solution

Copy those Assemblies from your any where (Windows 7,8,10) to Server

Restore and compile again.

Thank you for reading!

In Dynamics AX, we have two way to filter the result set of records in joined query is using QueryFilter class and QueryBuildRange class.

So what’s difference between them? when do we use QueryFilter class? When do we use QueryBuildRange class?

Today, i will make a simple sample to show what is difference between them. You can download example project from here.

I have two table :

Table DuyDang_ParentTable which have 1 columns : ID.

Parent_Table

Table DuyDang_ChildTable which have 3 columns : ID, ParentID, Qty.

Child_Table

and they have relation like this : DuyDang_ParentTable.ID = DuyDang_ChildTable.ParentID.

I have the Outer Join query:

Use QueryFilter class:

static void DuyDang_QueryFilter(Args _args)
{
    Query query;
    QueryBuildDataSource qbds, qbds1;
    QueryRun queryRun;    
    DuyDang_ParentTable parentTable;
    DuyDang_ChildTable childTable;
    QueryFilter qFilter;
    QueryBuildRange qRange;
    struct structSet;

    structSet = new struct
        ("str ParentID;"
        + "str ChildID;"
        + "real Quantity"
        );

    query = new Query();
    qbds = query.addDataSource(tableNum(DuyDang_ParentTable));  
    qbds1 = qbds.addDataSource(tableNum(DuyDang_ChildTable));    
    qbds1.joinMode(JoinMode::OuterJoin); // Set join type.   
    qbds1.addLink(fieldNum(DuyDang_ParentTable, ID), fieldNum(DuyDang_ChildTable, ParentID));
    qFilter = query.addQueryFilter(qbds1, 'Qty');
    qFilter.value(queryValue(15));
    //qRange = qbds1.addRange(fieldNum(DuyDang_ChildTable, Qty));
    //qRange.value(queryValue(15));
    
    queryRun = new QueryRun(query);    
    while (queryRun.next())
    {
        parentTable = queryRun.get(tableNum(DuyDang_ParentTable));
        childTable = queryRun.get(tableNum(DuyDang_ChildTable));

        structSet.value("ParentID", parentTable.ID);
        structSet.value("ChildID", childTable.ID);
        structSet.value("Quantity", childTable.Qty);
        info(structSet.toString());
    }
}

and result info here:

Child_Table

Use QueryBuildRange class:

static void DuyDang_QueryFilter(Args _args)
{
    Query query;
    QueryBuildDataSource qbds, qbds1;
    QueryRun queryRun;    
    DuyDang_ParentTable parentTable;
    DuyDang_ChildTable childTable;
    QueryFilter qFilter;
    QueryBuildRange qRange;
    struct structSet;

    structSet = new struct
        ("str ParentID;"
        + "str ChildID;"
        + "real Quantity"
        );

    query = new Query();
    qbds = query.addDataSource(tableNum(DuyDang_ParentTable));  
    qbds1 = qbds.addDataSource(tableNum(DuyDang_ChildTable));    
    qbds1.joinMode(JoinMode::OuterJoin); // Set join type.   
    qbds1.addLink(fieldNum(DuyDang_ParentTable, ID), fieldNum(DuyDang_ChildTable, ParentID));
    //qFilter = query.addQueryFilter(qbds1, 'Qty');
    //qFilter.value(queryValue(15));
    qRange = qbds1.addRange(fieldNum(DuyDang_ChildTable, Qty));
    qRange.value(queryValue(15));
    
    queryRun = new QueryRun(query);    
    while (queryRun.next())
    {
        parentTable = queryRun.get(tableNum(DuyDang_ParentTable));
        childTable = queryRun.get(tableNum(DuyDang_ChildTable));

        structSet.value("ParentID", parentTable.ID);
        structSet.value("ChildID", childTable.ID);
        structSet.value("Quantity", childTable.Qty);
        info(structSet.toString());
    }
}

and result info here:

Child_Table

now you can see the difference between them

  • When you use QueryBuidRange class, the restriction is in the ON clause of the OUTER JOIN in the ANSI SQL select statement that is generated by the AOS for the underlying database system.
SELECT * FROM DuyDang_ParentTable(DuyDang_ParentTable_1)
	OUTER JOIN * FROM DuyDang_ChildTable(DuyDang_ChildTable_1) 
	ON DuyDang_ParentTable.ID = DuyDang_ChildTable.ParentID 
		AND ((Qty = 15))
  • When you use QueryFilter class, the restriction is in the WHERE clause of the OUTER JOIN in the ANSI SQL select statement that is generated by the AOS for the underlying database system.
SELECT * FROM DuyDang_ParentTable(DuyDang_ParentTable_1) 
	OUTER JOIN * FROM DuyDang_ChildTable(DuyDang_ChildTable_1) 
	ON DuyDang_ParentTable.ID = DuyDang_ChildTable.ParentID 
	WHERE ((DuyDang_ChildTable(DuyDang_ChildTable_1).Qty = 15))

Thank you for reading!

Useful functions to use in AX 2012

Thank you for reading!