Tuesday, November 16, 2010

Programmatic choice of select statements and the Next operator


Occasionally I’ve had situations where using a while select statement to loop through a set of records and needed the fields used in the selection criteria to be dynamic or the entire selection statement to be chosen dynamically based on arguments passed to the method.  A simple example would be for a set of logic to sometimes act on a group of records but other times act on a single record.  Dynamics AX X++ offers a slick way of dealing with this that is used in the standard application code but is still pretty obscure.  The following method demonstrates using a variable select statement based on passed in arguments and using the NEXT keyword to move the record set forward.  In this example we’re just outputting the Item ID to an info log but you could get much more creative….

void SelectNextSample(salesID _salesID, inventtransID _inventTransID = '')
{
    salesLine salesLine;
    ;
    
    if(_inventTransID)
    {
        select salesLine 
            where  salesLine.SalesId == _salesID
                && salesLine.InventTransId == _inventTransID;
    }
    else
    {
        select salesLine
            where salesLine.SalesId == _salesID;
    }
    
    while(salesLine)
    {
        info(salesLine.ItemId);
 
        next salesLine;
    }
}

I realize that the above is a simplistic example but it does demonstrate the ability in X++ to determine at run time the exact select statement that will be looped through against your Common object, although in reality the above example does not require variable select statements at all, a bad example in practical use but does show the general construct.  Below is another example, again very simple output of an info log but the variance in the select statements is greater, here it is a difference of acting logic on all sales lines with particular item ID vs. just sales line with the given item ID for a specific warehouse.   In the case below the content of the inventDim source is not needed in the logic portion of the code so when criteria restriction on inventLocationID is not needed we can have a simpler selection statement.   We could enjoy the same dynamic flexibility at run time using a query objects but the point here is to really demonstrate the use of the Next operator with variable selection statements.


void SelectNextSample2(itemID _itemID, inventLocationID _inventLocationID = '')
{
    salesLine salesLine;
    inventDim inventDim
    ;
    
    if(_inventLocationID)
    {
        select salesLine 
            where  salesLine.itemID == _ItemID
        Join tableID from inventDim
            where  inventDim.InventLocationId == _inventLocationID
                && salesLine.InventDimId == inventDim.inventDimId;
    }
    else
    {
        select salesLine
            where salesLine.ItemId == _itemID;
    }
    
    while(salesLine)
    {
        info(strFmt('%1, %2', salesLine.SalesId, salesLine.ItemId));
 
        next salesLine;
    }
}

No comments:

Post a Comment