Wednesday, September 14, 2011

Update Query Range at Runtime in Dynamics AX

     In Dynamics AX we can update the Query Range at run-time in code,  This is similar to "Filter by Grid" feature except that we are handling this in code. For example my user asked me to provide a Button on Sales LineItems which would toggle between displaying "Current SalesLineItems" VS "All SalesLineItems". By Current SalesLineItems i mean displaying LineItems with more than zero quantity, other addendum to original requirement is to preserve the existing user filters on the form.

In this image you can see a SalesOrder with 3 LineItems (notice that one LineItem has zero quantity), blue arrow pointing to new button("Show Only Current LineItems"):


If the User clicks this new button then :
a) LineItems with zero quantity will be filtered out, in the image below you can see only 2 LineItems
b) the label of the button will be updated to "Show All LineItems" , see the following image:
      
      
Let's start DAXing now - All i need to update for this requirement is SalesTable form:
1.  Add a new boolean variable to ClassDeclaration method
     boolean showOnlyCurrentLineItems;

2. If the user wants to display only current LineItems by default when the form loads, then initialize our boolean variable to true in the Form->init()
    showOnlyCurrentLineItems = true;

3. Now, let's add our query range just before the query is exceuted by overriding the SalesLine DataSource's executeQuery() method (SalesTable -> DataSources->SalesLine->Methods->executeQuery() ) .
Before the super()  method is called we need to add our range to the SalesLine query, AX form maintains two instances of the query objects at run-time (reference) :
 a)  formDataSource.query()  - original datasource query added at the design time to the form's datasource node
 b)  formDataSource.queryRun().query()   - the query originated from a) +  filters applied by the User
So it is better to update both the queries to cover all the scenarios. Here is the code to write within the executeQuery():


public void executeQuery()
{
  // instances for query type b)
 Query  queryForSalesLine;
 QueryBuildDataSource qbdsForSalesLine;
 QueryBuildRange qbrForSalesLine; 

 // instances for Query type a)
  Query  origQueryForsalesLine;
  QueryBuildDataSource origQbdsForSalesLine;
  QueryBuildRange origQbrForSalesLine;
   
  boolean updateOrigQuery;
 
  // check if query type b) exists, if so use it otherwise use Query type a)
  if(salesLine_DS !=null && salesLine_DS.queryRun() != null && salesLine_DS.queryRun().query() != null)
   {
        queryForSalesLine = salesLine_DS.queryRun().query();
        updateOrigQuery = true;
   }
   else
        queryForSalesLine = salesLine_DS.query();
         
   qbdsForSalesLine = queryForSalesLine.dataSourceName("SalesLine");
   qbdsForSalesLine.clearRange(fieldnum(SalesLine, SalesQty));
   qbrForSalesLine = qbdsForSalesLine.addRange(fieldnum(SalesLine,SalesQty));
 
   // update both the queries in both the cases
    if(showOnlyCurrentLineItems)  // this is initialized to true and we will update this variable variable thru a button
    {
        qbrForSalesLine.value(">0");
        if(updateOrigQuery)
         {
           origQbdsForSalesLine =  salesLine_ds.query().dataSourceName("SalesLine");
           origQbdsForSalesLine.clearRange(fieldnum(SalesLine, salesQty));
           origQbrForSalesLine = origQbdsForSalesLine.addRange(fieldnum(SalesLine,SalesQty));
           origQbrForSalesLine.value(">0");
         }
    }
    else
     {
       qbrForSalesLine.value("*");
       if(updateOrigQuery)
         {
           origQbdsForSalesLine =  salesLine_ds.query().dataSourceName("SalesLine");
           origQbdsForSalesLine.clearRange(fieldnum(SalesLine, salesQty));
           origQbrForSalesLine = origQbdsForSalesLine.addRange(fieldnum(SalesLine,SalesQty));
           origQbrForSalesLine.value("*");
         }
     }
 
    super();
} 
 
4. Add a new Button to the SalesLine section which will allow the User to toggle the Query's range
a)  Add a new Button named "UpdateQueryButton" at SalesTable Form->Designs->[Group:Line] -> [ButtonGroup: ButtonLine]
b) Set its Text property to "Show All Line Items"
c) Override the button's clicked event method with this code:


void clicked()
{
    super();
    if(showOnlyCurrentLineItems)
    {
        showOnlyCurrentLineItems = false;
        this.text("Show Only Current LineItems");  // Update button's text
    }
    else
    {
        showOnlyCurrentLineItems = true;
        this.text("Show All LineItems"); // Update button's text
    }
    
    salesLine_DS.research(true); // this will call executeQuery method that we overrode earlier
}


That is it, you can verify that it preserves the User added Filters/Ranges on the form.
Happy DAXing
-San

No comments:

Post a Comment