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
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.
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
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.