HOW TO: Filter display fields on a form on Dynamics AX 2012

Today we are going to show you how to filter a display field on a form. As a recomendation this should be used as a last resort just when you can’t have a physical field on the form.

First of all we will overwrite the context method on the form control that has the display method set, and we are going to paste the following code:

 X++ |  copy code |? 
01
02
public void context()
03
{
04
    int             selectedMenu;
05
    formrun         fr;
06
    Args            ag;
07
    Name            strtext;
08
    querybuilddataSource qb1,qb2;
09
    queryrun    qr;
10
    query       q;
11
    InventDim   inventDimAux;
12
    InventItemPurchSetup    inventItemPurchSetup;
13
    ItemId                  itemId;
14
    InventItemInventSetup   inventItemInventSetup;
15
    InventTransferTable inventTransferTable;
16
    Common          common=element.args().record();
17
    PopupMenu menu = new PopupMenu(element.hWnd());
18
    Query                   query = new Query();
19
    QueryBuildDataSource    qbds;
20
    QueryBuildDataSource    qbdsJoin;
21
    QueryBuildDataSource    qbdsJoinAux;
22
    int a = menu.insertItem('Filtrar por Campo');
23
    int b; //= menu.insertItem('Filter By Selection');
24
    int c = menu.insertItem('Remover Filtros');
25
    ;
26
 
27
    selectedMenu = menu.draw();
28
    switch (selectedMenu)
29
    {
30
    case -1: //Filter by field
31
            break;
32
    case a: // run the dialog for the filter and build the query with the new datasouces
33
            ag = new args('SysformSearch');
34
            fr = new formrun(ag);
35
            fr.run();
36
            fr.wait();
37
//Reading User entered value for filter process
38
            strtext = fr.design().controlName('FindEdit').valueStr();
39
            if(strtext)
40
            {
41
                if(common.TableId == tableNum(inventTransferTable))
42
                {
43
                    inventTransferTable    = common;
44
                }
45
//Creating a query for filter
46
                q   = EcoResProduct_ds.query();
47
                qb1 = q.dataSourceTable(tablenum(EcoResProduct));
48
                qb1 = qb1.addDataSource(TableNum(InventItemPurchSetup));
49
                qb1.addLink(FieldNum(EcoResProduct,DisplayProductNumber),FieldNum(InventItemPurchSetup,ItemId));
50
                qb1.addRange(FieldNum(InventItemPurchSetup,InventDimId)).value("!CMH-000001");
51
                qbdsJoin= qb1.addDataSource( tableNum(inventItemInventSetup));
52
                qbdsJoin.relations( false);
53
                qbdsJoin.fields().dynamic(NoYes::Yes);
54
                qbdsJoin.addLink( fieldNum(InventItemPurchSetup, InventDimId), fieldNum(inventItemInventSetup, InventDimId));
55
                qbdsJoin.addLink( fieldNum(InventItemPurchSetup, itemId), fieldNum(inventItemInventSetup, ItemId));
56
                qbdsJoin.joinMode(JoinMode::InnerJoin);
57
                qbdsJoinAux = qbdsJoin.addDataSource(tableNum(InventDim));
58
                qbdsJoinAux.addLink( fieldNum(inventItemInventSetup, InventDimIdDefault), fieldNum(InventDim, InventDimId));
59
                qbdsJoinAux.addRange( fieldNum(InventDim, InventLocationId)).value(strtext);
60
 
61
                qbdsJoinAux.joinMode(JoinMode::InnerJoin);
62
 
63
 
64
                EcoResProduct_ds.query(Q);
65
                EcoResProduct_ds.executeQuery();
66
            }
67
            break;
68
 
69
    /*case b:   // Filter By Selection
70
            q   = smmBusRelTable_ds.query();
71
            qb1 = q.dataSourceTable(tablenum(smmBusRelTable));
72
            qb1 = qb1.addDataSource(TableNum(CustTable));
73
            qb1.addLink(FieldNum(smmBusRelTable,CustAccount),FieldNum(CustTable,AccountNum));
74
            qb1.addRange(FieldNum(CustTable,Name)).value(disCustName.valueStr());
75
            smmBusRelTable_ds.query(Q);
76
            smmBusRelTable_ds.executeQuery();*/
77
            //  break;
78
    case c :   // Remove Filter
79
            q   = new Query();
80
            qb1 = q.dataSourceTable(tablenum(EcoResProduct));
81
            qb1.clearLinks();
82
            qb1.clearRanges();
83
            EcoResProduct_ds.query(Q);
84
            EcoResProduct_ds.removeFilter();
85
            break;
86
 
87
    Default:
88
            break;
89
    }
90
 
91
}
92

Now you can see that each section has a comment explaining what does the code, for example we have the “case a” section, here we are going to show a dialog that will allow us to enter the value to filter, this will be set on the strText variable that will be used as a range for the query. For the range to be efective we need to add and edit the values of the datasources of the query. In this case we need the default location for an item so we need to add the InventItemPurchSetup table to the datasources and link it to the EcoResProduct query that we already have on our form.

As I said this can be used but just as a last resort on your forms since it has it’s disadvantages:

– To remove the filter you will have to click on the remove filter Option by right clicking on the display field, you will have to write the code for this to happen.
– The query will be permanently modified so you will always have to clear the query if you want to perform any other operation on the form.

So taking this into account we think this is a pretty interesting thing to do, we hope you like it and see you next time.

  • Itsuki Minami

    by LP