Monday, December 15, 2014

Filtering records

Filtering records

Filter the records is the important part to display the data. By using the filter we can display the necessary data and avoid showing the unnecessary data based on the requirement.

In AX 2012, there is a default filter functionality. To use default filter select the form grid and press the ctrl+g shortcut to show/hide the filter options in the grid and then you can enter the values and press the enter key to filter the record.




We can achieve this functionality by using the x++ code.

For example, we need to filter the student results by pass, fail and all option.
I have added one combo box and grid in the form. Based on the combo box selection, I am filtering the student exam results.


Step 1:

Add the unbound combo box with the enum type. So, we can filter the records based on this combo box selected value.

Step 2:

Declare the query build datasource and query build data range variables in the form declarations section.
public class FormRun extends ObjectRun
{
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
}

Step 3:

Add the filter code in the combo box selection change event.
public int selectionChange()
{
    int ret;
    ret = super();

    qbds.clearRange(fieldNum(Hari_ExamResult, ExamResult)); //Clear the filter field range
    if(ExamResultCombo.valueStr() != '') //Check the combo box has value or not
    {
        qbr = qbds.addRange(fieldNum(Hari_ExamResult, ExamResult)); //Set the filter field
        qbr.value(ExamResultCombo.valueStr()); //Set the filter value
    }
    Hari_ExamResult_ds.executeQuery(); //Execute the form datasource query to apply the filter

    return ret;
}




Some other useful functions like
qbds.clearRanges(); //To clear all ranges

qbr = qbds.findRange(fieldNum(Hari_ExamResult, ExamResult)); //To find the field range already exists or not



Once you understand this code, you can apply this code for different scenarios.

Please try the below filtering records functionality to improve your knowledge in the filtering records.
  1. Equal
  2. Not equal
  3. Like
  4. Like after
  5. Between
  6. Or
  7. And
  8. Empty
  9. Not empty
  10. Get all datasource names
  11. Get all datasource ranges
  12. Clear all ranges
  13. Clear particular datasource range
  14. Less than
  15. Greater than
  16. Less than or equal to
  17. Greater than or equal to
  18. Range is exist or not
  19. Filter is exist or not
  20. Find or create range
  21. Find or create filter
  22. Query build data source. To string
  23. Find group by field
  24. Find field

5 comments :

  1. you can do it much simplier with findOrCreateRange

    if(ExamResultCombo.valueStr() != '') //Check the combo box has value or not
    {
    SysQuery::findOrCreateRange( Hari_ExamResult_ds.query().dataSourceTable( tableNum( Hari_ExamResult )),
    fieldNum( Hari_ExamResult, ExamResult ) ).value( SysQuery::value( ExamResultCombo.valueStr() ) );
    }
    else
    {
    SysQuery::findOrCreateRange( Hari_ExamResult_ds.query().dataSourceTable( tableNum( Hari_ExamResult )),
    fieldNum( Hari_ExamResult, ExamResult ) ).value( SysQuery::valueUnlimited() );
    }

    do not forget to clear range when ExamResultCombo.valueStr() or your grid will be stay with records when you clear your combo!

    ReplyDelete
  2. how to use LIKE to filter data in show in grid? let say i have a CustInvoiceTrans table and i want to display the invoice number like "CN" only..please help me.. :(

    ReplyDelete
  3. this code is very useful for me in many cases
    this my hand piece of code
    Multiselectlookup filter
    http://pravin22ax.blogspot.in/2017/03/multiselect-lookup-filter.html

    ReplyDelete
  4. For this what is the execute query

    ReplyDelete