Tuesday, March 30, 2010

How to filter records in a form by code

The standard filter functionality in Ax forms is a neat and powerful feature.
Using this filter functionality in your code is something you'll definitely use at some point in time as a programmer.

Although it's possible to do it in a single line of code, I prefer a 3 step solution. That way it's more flexible.
Let me show you by example. We'll filter the customers records in form CustTable, only showing customers with currency USD.

Step 1: Declare a class variable
In the ClassDeclaration method of the form, define a range.

QueryBuildRange CurrencyQBR;

Step 2: Instantiate the new range.
In the init method on the datasource of the form, you assign the range to a specific field (after the super call).

public void init()
{
super();

CurrencyQBR = this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,Currency));
}

Step 3: In the last step, you assign a value to the range.
This is done in the executeQuery method on the same datasource of the form. Before the super call. Like this:

public void executeQuery()
{ ;

CurrencyQBR.value(queryvalue('USD'));

super();
}

You're done! When you open the form, your customer records are filtered, you only get the customers with currencycode USD set up.




Like I said in the intro of this post, this can be done in one line of code as well.
In the init method of the form datasource, after the super call, place this code:

this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,Currency)).value(queryvalue('USD'));

But this way, it's fixed. If you choose the 3 step method, you could for example use a variable in the range value. The way to go would be to place an input field on your form, get the value from it and supply it in the executeQuery method.

For example like this:

public void executeQuery()
{ ;

CurrencyQBR.value(queryvalue(MyInputField.text()));

super();
}

Just make sure the executeQuery method is executed, thus applying the desired filter (maybe be using a button on your form to activate it).
Of course it's possible to combine multiple querybuildranges.

28 comments:

  1. Thanks! I have a question related to building queries on forms. I have a form with 2 datasources, say alpha and beta where beta is linked to alpha and alpha is the primary datasource. I have a button that opens a QueryRun object and builds a query from these two datasources (this is to allow users to not have to create their own custom query/filter). I can run it but it doesn't mark the datasource as being filtered. How might this be possible as I cannot use the alpha_ds.filter(...) method. Here is example code:

    void clicked()
    {
    Query q;
    QueryBuildRange qbr;
    QueryBuildDataSource qbds;
    QueryBuildDataSource qbdsSearchFIeld;
    QueryRun qr;
    ;

    super();

    q = alpha_ds.query();
    qbds = q.dataSourceTable(tablenum(alpha));
    qbds.clearRanges();

    if (q.dataSourceTable(tablenum(beta)))
    qbdsSearchField = q.dataSourceTable(tablenum(beta));
    else
    qbdsSearchField = qbds.addDataSource(tablenum(beta));

    qbdsSearchField.relations(true);

    if (qbdsSearchField.findRange(fieldnum(beta, SearchField)))
    qbr = qbdsSearchField.findRange(fieldnum(beta, SearchField));
    else
    qbr = qbdsSearchField.addRange(fieldnum(beta, SearchField));

    qr = new QueryRun(q);

    if (qr.prompt())
    {

    alpha_ds.query(qr.query());

    alpha_ds.executeQuery();

    // this works to filter down but does not display the Remove Filter button
    }

    }

    ReplyDelete
  2. I'm using Dynamics AX 2009

    I'll get in the following error in step3:

    super() is not allowed here


    what to do?

    ReplyDelete
  3. Extend your class in classDeclaration. Altough classes automatically extends RunBase it might help if you extend it manually.

    ReplyDelete
  4. Hi 'Anonymous'!

    Make sure you edit the executeQuery method of the datasource of the form. Don't add that method in step 3 as new method on the form.

    ReplyDelete
  5. Hi Willy,

    Thanks for posting this. However I'm not clear on how to create a button to activate the filter. Can you point me to the right direction please?
    I've been trying for the past week going nowhere :) Thanks in advance.

    ReplyDelete
  6. Hello Hendro,

    When you add a button, make sure you edit the clicked method of that button. Instead of calling super, make sure the executeQuery method of your datasource is called. This will trigger the applying of the filter. (Example: CustTable_ds.executeQuery())

    ReplyDelete
  7. Wonderful!
    Thank you so very much, it's working :)

    ReplyDelete
  8. This comment has been removed by a blog administrator.

    ReplyDelete
  9. Hello there, I must warn you if you set range by code simply by "addRange" and want to allow user to set their own filters too (advanced, filter by value, etc.)

    Example: If you use "addRange" to add filter on date column "1.1.2012..31.12.2012" by code, and that user will then use filter by value "1.4.2012", than there will be two ranges for the same columns in the background and only the last one of them will be applied !!! (check advance filter to see that - one is generated by code, one by user).

    Try to use SysQuery::findOrCreateRange() to be sure that you will just change value in existing range rather than adding another one. You don't have to check for existing range because the system will do so.

    Try to accommodate yourself with SysQuery class, this will prevent you from serious headache.

    ReplyDelete
  10. PS: I forgot to mention to be aware to addRange not from the method executeQuery on the datasource, but for example preparing the variables to change the range later from init method, ex. (AX2009 see the init methods on ForecastSales, ProjForecastEmpl, etc):

    qB = this.query().dataSourceNo(1);

    criteriaExpand_1 = qB.addRange(fieldnum(ForecastSales,ExpandId));

    ReplyDelete
  11. This is good technical blog which is good for new dynamics learner and problem's solutions

    Axapta ERP Technical blog-ALL Version

    ReplyDelete
  12. Thank you very much indeed. Clearly explained and bloody easy to do.

    ReplyDelete
  13. I just wanted to say that your blog is perhaps the most usefull source of AX info I've found.
    Thank you.

    ReplyDelete
  14. In the first example. What should you do when you want to see everything but the 'USD'?
    thx for helping!

    ReplyDelete
  15. Hi there. I'm trying to use a variation of this method on the query for the employee list page. My problem is, after setting the filter (I want the user to see only a subset of the employees, e.g. only the ones in the same department), the user can manually alter it afterwards. Is there some way of preventing this without completely removing the possibility of filtering for the user? Thx.

    ReplyDelete
  16. I suggest you have to add after addRange(fieldnum(...)).status(RangeStatus::Locked);

    ReplyDelete
  17. Nice post. You can visit following link to get Ax 2012 technical code and error solutions

    Ax 2012 Development and Coding

    ReplyDelete
  18. Very Useful for me as a beginner,Thanks

    ReplyDelete
  19. Hello,
    Here I understand that the range is for the Currency ,may be a string.
    But suppose their is an Enum again which is an unbound control, that is to have a filter.Upon the change in the enum value...Grid in the form chenges repectively.

    ReplyDelete
  20. Hello,
    do any one know how to filter the records in lookup by using from date and todate

    ReplyDelete
  21. Thanks, very useful.
    Regards

    ReplyDelete
  22. Yes, Its helped me, Nice dude :-)

    ReplyDelete
  23. hi ,
    how to get currentcompany details in form when opening form (form is listpage one).

    ReplyDelete