Share via


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

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.

Applies to:   Microsoft Dynamics AX
Original KB number:   906138

Note

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.

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()); 
}