An X++ query example for Microsoft Axapta that you can use when you want to use multiple criteria

Article ID: 906138
Microsoft Business Solutions-Axapta 3.0 is now part of Microsoft Dynamics AX 3.0. All references to Microsoft Business Solutions-Axapta and Microsoft Axapta pertain to Microsoft Dynamics AX.
Expand all | Collapse all

INTRODUCTION

This article contains a Microsoft Business Solutions Axapta - X++ query example that you can use in Microsoft Business Solutions - Axapta when you want to use multiple criteria, such as Date and ID or multiple IDs.

MORE INFORMATION

The following code provides the same output as the while select statement that is indicated in the comments.
static void Query_Example(Args _args)
{
    Query q;
    Queryrun qr;
    QueryBuildRange qbr;
    QueryBuildDataSource qbds;
    InventTrans iv;
    Real Total;
    str range;
    
    /* The following query produces the same results as:
    while select sum(qty) from inventTrans
        where (inventtrans.ItemId == "OL-1500") || inventtrans.ItemId == "OL-1000"
            join inventDim
                group by inventBatchId
                where inventDim.InventDimId == inventTrans.InventDimId */

    // Instantiate the query class.
    q = new query("Inventory_Transactions"); 
   
    // Create a data source by using the InventTrans table.
    qbds = q.addDataSource(tablenum(InventTrans));
 
    // Select only the Qty field, and then sum the Qty field.
    qbds.addSelectionField(fieldnum(InventTrans,Qty),selectionfield::Sum); 

    // Set the range to the ItemId field.
    qbr = qbds.addRange(fieldnum(InventTrans,ItemId));  

    // The range for the where statement specifies an 'or' statement.
    qbr.value(strfmt('((%1 == "%2") || (%1 == "%3"))',fieldstr(inventtrans,ItemId),'OL-1500','OL-1000'));

    // The following is the alternative way to enter the range.
    // This also limits the selection on the ItemId values.
    range = strfmt('((ItemId == "%1")||(ItemID =="%2"))',queryvalue('OL-1500'),queryvalue('OL-1000'));
    qbr.value(range);    

    // Create the join to the InventDim table.
    qbds = qbds.addDataSource(tablenum(InventDim));
  
    // Specify the table relationship.
    qbds.relations(true);
  
    // Indicate the order mode as the grouping.
    qbds.orderMode(ordermode::GroupBy);
 
    // Specify the grouping on the InventBatchId field.
    qbds.addSortField(fieldnum(InventDim,InventBatchId)); 

    // Instantiate the QueryRun class for the form.
    qr = new QueryRun(q);  

    // If the user clicks OK, continue.
    if (qr.prompt())  
    {

        // While there are records in the query, continue.
        while (qr.next())  
        {
            
           // Set the value of the query to the iv table. 
           bufferiv = qr.get(tablenum(InventTrans)); 

            // Create the value of the total field.
           total =  iv.Qty;  
        }
    }
    // Specify the quantity for the item.
    info(strfmt("Quantity: %1",total));  
    // Indicate the SQL string that is used for the query.
     info (qr.query().dataSourceNo(1).toString());  
}

Properties

Article ID: 906138 - Last Review: July 26, 2011 - Revision: 2.0
Keywords: 
kbmbsmigrate kbinfo KB906138

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com