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

Continue Reading ...

Useful functions to use in AX 2012 part 2

Continue Reading ...

The workflow process moves purchase requisitions through the review process, from an initial status of Draft to a final status of Approved. When a purchase requisition is submitted for review, the workflow process is started. After a purchase requisition is approved, a purchase order can be generated for the purchase requisition lines and submitted to the vendor for order fulfillment.

We will use AIF standard service for import PR from outside, service name PurchReqImportService.

in AxPurchReqTable class and setPurchReqId() method

protected void setPurchReqId()
{
    NumberSequenceReference numberSequenceReference;

    if (this.isMethodExecuted(funcName()))
    {
        return;
    }

    if (this.isFieldSetExternally(fieldNum(PurchReqTable, PurchReqId)))
    {
        if (this.isSetMethodsCalledFromSave())
        {
            if (!this.purchReqTable())
            {
                numberSequenceReference = PurchReqTable::numRefPurchReqId();
                this.checkNumber(numberSequenceReference.numberSequenceTable(),fieldNum(PurchReqTable,PurchReqId),this.parmPurchReqId());
                if (numberSequenceReference.NumberSequenceId && numberSequenceReference.numberSequenceTable().Continuous)
                {
                    NumberSeq::newReserveNum(numberSequenceReference).reserve(this.parmPurchReqId());
                }
            }
        }
    }
    else
    {
        if (this.isFieldSet(fieldNum(PurchReqTable, PurchReqId)))
        {
            return;
        }

        if (!this.parmPurchReqId())
        {
            if (this.isSetMethodsCalledFromSave())
            {
                //this.parmPurchReqId(NumberSeq::newGetNum(PurchParameters::numRefPurchReqId()).num());
                numberSequenceReference = PurchReqTable::numRefPurchReqId();
                if(numberSequenceReference)
                {
                    this.setField(fieldNum(PurchReqTable, PurchReqId), NumberSeq::newGetNum(PurchParameters::numRefPurchReqId()).num());
                }
                else
                {
                    this.setField(fieldNum(PurchReqTable, PurchReqId), this.parmExternalSourceID());
                }
            }
        }
    }
}

Base on this method, you could know how System get PurchReqId.

How to do

Go to Inbound ports form to create new service with NETTCP adapter, choose service operations likes below:

Then active AIF inbound service

Consume Pruchase requisition service

Open visual studio and create new console project.

Add service reference

http://DEV-ERP:8101/DynamicsAx/Services/MavPurchaseRequisition

This one just for demo, so I just create code base on required fields of AIF.

Here is the code in main method

PurchReqImportServiceClient client = new PurchReqImportServiceClient();
CallContext context = new CallContext()
{
	Company = "BGR",
	Language = "En-us"
};

AxdEntity_PurchReqLine purchReqLine = new AxdEntity_PurchReqLine()
{
	Requisitioner = "000007",
	BuyingLegalEntity = "BGR",
	ItemId = "110329",
	PurchUnitOfMeasure = "Box",
	CurrencyCode = "KRW",
	PurchQty = 100,
	PurchQtySpecified = true,
	PriceUnit = 1,
	PriceUnitSpecified = true,
};

// Create an instance of the document class.
AxdEntity_PurchReqTable purchReqTable = new AxdEntity_PurchReqTable()
{
	PurchReqId = "",
	PurchReqName = "Purch Req by Max",
	ExternalSourceID = "PR002",
	ExternalSourceName = "PR002",
	AutoSubmitToWorkflowRequired = AxdEnum_NoYes.No,
	StatusToBeSaved = AxdEnum_PurchReqCreationStatus.Draft,
	RequisitionStatus = AxdEnum_PurchReqRequisitionStatus.Draft,
	RequisitionStatusSpecified = true,
	RequiredDate = new DateTime(2016, 12, 30),
	RequiredDateSpecified = true,
	TransDate = new DateTime(2016, 12, 30),
	TransDateSpecified = true,
	PurchReqLine = new AxdEntity_PurchReqLine[1] { purchReqLine }
};


// Create instances of the entities that are used in the service and
// set the needed fields on those entities.
AxdPurchReqImport purchReq = new AxdPurchReqImport()
{
	PurchReqTable = new AxdEntity_PurchReqTable[1] { purchReqTable }
};

try
{
	client.create(context, purchReq);
}
catch (Exception e)
{
	Console.WriteLine(e.ToString());
	Console.ReadLine();
}

Run it and check result in AX

Please prefer previous post for another operations

Thank you for reading!

In previous post, I already show how to create purchase order through AIF with NETTCP or HTTP adapter. In this post, we will get little deep more about action on line of order.

The following code sample shows how to insert, update, delete a line of an existing purchase order through AIF, currently I’m using C#.NET console project for demo.

As Partial update, we must include just the fields to change and any fields required by the document (you can check Data policies in AIF Service ports form for that).

Also, notice how action properties are specified – no matter we do with the line, which means updating the order.

I’m giving you an idea how it looks like, here is the code

The first method will handle the key of AIF Service

private static EntityKey[] EntityForPurchId(string purchId)
{
	KeyField field = new KeyField()
	{
		Field = "PurchId",
		Value = purchId
	};

	EntityKey key = new EntityKey()
	{
		KeyData = new[] { field }
	};

	return new[] { key };
}

create Line, delete Line, update line in Purchase order

EntityKey[] entityKeyList = EntityForPurchId("BGR-000054");

CallContext callContext = new CallContext();
callContext.Company = "bgr";

Mav_PurchOrderServiceServiceClient client = new Mav_PurchOrderServiceServiceClient();
AxdMav_PurchOrderService purchOrders = client.read(callContext, entityKeyList);

//Define which line need to be update or delete
var lastLine = purchOrders.PurchTable[0].PurchLine.Last();

var purchLine = new AxdEntity_PurchLine()
{
	ItemId = "110329",
	PurchQty = 1,
	CurrencyCode = "KRW",
	RecIdSpecified      = true,
	LineNumberSpecified = true,
	action              = AxdEnum_AxdEntityAction.create,
	actionSpecified     = true

	//for delete
	//RecId               = lastLine.RecId,
	//RecIdSpecified      = true,
	//action              = AxdEnum_AxdEntityAction.delete,
	//actionSpecified     = true

	//for Update
	//RecId               = lastLine.RecId,
	//RecIdSpecified      = true,
	//action              = AxdEnum_AxdEntityAction.update,
	//actionSpecified     = true
	
};

var purchTable = new AxdEntity_PurchTable()
{
	_DocumentHash = purchOrders.PurchTable[0]._DocumentHash,
	OrderAccount = "101-01-75441",
	LanguageId = "en-us",
	CurrencyCode = "KRW",
	PurchName = "AIF PO Test",
	action = AxdEnum_AxdEntityAction.update,
	actionSpecified = true,
	PurchLine = new[] { purchLine }
};

AxdMav_PurchOrderService purchOrder = new AxdMav_PurchOrderService()
{
	PurchTable = new AxdEntity_PurchTable[] {purchTable}
};


client.update(callContext, entityKeyList, purchOrder);

Thank you for reading!