In this example, I want to display the records, from Project Table for the fields ProjId and ProjName have similar values like as show in figure.

 

Here I am filtering the records which have, ProjId and Project Name is 305492-00*

 

In simple I want to achieve this

 

Selec  * from ProjTable where ProjTable.ProjId == “305492-001” ||

                        ProjTable.Name == “305492-001”;

 

 

Simple select query in Ax Client:

static void projTableJob(Args _args)

{

    ProjTable   projTable;

    ;

 

    while Select projTable where projTable.ProjId Like "305492-00*" ||

                                 projTable.Name   Like "305492-00*"

 

    {

        print projTable.Name;

    }

 

    pause;

}

 

 

Using Query Classes in Ax Client: The same query is implemented by using Query classes in Ax client

 

static void projTableJobUsingQuery(Args _args)

{

    Query                   query;

    QueryBuildDataSource    qb, qb1;

    QueryBuildRange         qbr, qbr1;

    QueryRun                qr;

    ProjTable               projTable;

    str                     myvalue = '305492-00*';

    ;

 

    query = new Query();

    qb = query.addDataSource(tablenum(ProjTable), "ProjTable");

 

    qbr = qb.addRange(fieldnum(ProjTable, ProjId));

// Here the OR condition is implemented.   

    qbr.value(strfmt('(%1.%2 Like "%3") || (%1.%4 Like "%5")',

                qb.name(),

                fieldstr(ProjTable, ProjId), any2str(myvalue),

                fieldstr(ProjTable, Name),  any2str(myvalue)

                ));

 

 

    qr = new QueryRun(query);

 

    while (qr.next())

    {

        projTable = qr.get(tablenum(ProjTable));

        print projtable.Name;

    }

 

    pause; 

 

}

 

The above query is implemented in Ax-2009 EP:

 

protected void Project_LookUp(object sender, AxLookupEventArgs e)

    {

 

        AxLookup lookup = e.LookupControl;

        int projTableId = TableMetadata.TableNum(this.AxSession, "ProjTable");

 

               

        //Create the lookup dataset - we will do a lookup in the Project table

        using (Proxy.SysDataSetBuilder sysDataSetBuilder = Proxy.SysDataSetBuilder.constructLookupDataSet(this.AxSession.AxaptaAdapter, TableMetadata.TableNum(this.AxSession, "ProjTable")))

        {

 

            // Set the run time generated data set as the lookup data set

            lookup.LookupDataSet = new DataSet(this.AxSession, sysDataSetBuilder.toDataSet());

        }

        lookup.LookupDataSet.Init();

 

        using (Proxy.Query query = lookup.LookupDataSet.DataSetViews[0].MasterDataSource.query())

        {

            using (Proxy.QueryBuildDataSource dataSource = query.dataSourceName("ProjTable"))

            {

               

                TableMetadata projTableMetadata = MetadataCache.GetTableMetadata(this.AxSession, projTableId);

 

                TableDataFieldMetadata custAccountField =

                    (TableDataFieldMetadata)projTableMetadata.Fields.GetByName("ProjId");

 

                TableDataFieldMetadata nameField =

                    (TableDataFieldMetadata)projTableMetadata.Fields.GetByName("Name");

 

// Here the OR condition is implemented.         

 

                String myValue = String.Format("((({0}.{1}) Like \"{3}\")) || (({0}.{2}) Like \"{3}\")))",

                                                projTableMetadata.ToString(),

                                                custAccountField.Name,

                                                nameField.Name,

                                                (string)SearchClient.Text.ToString());

 

                using (Proxy.QueryBuildRange range = dataSource.addRange(custAccountField.FieldId))

                {

                    range.status = (int)Proxy.RangeStatus.Open;

                    range.value = myValue;                  

                }

            }

        }

 

        // Specify the lookup fields used

 

        lookup.Fields.Add(AxBoundFieldFactory.Create(this.AxSession, lookup.LookupDataSetViewMetadata.ViewFields["ProjId"]));

        lookup.Fields.Add(AxBoundFieldFactory.Create(this.AxSession, lookup.LookupDataSetViewMetadata.ViewFields["Name"]));

 

        // Specify the select field

        lookup.SelectField = "ProjId";

 

        lookup.DefaultLookupGrid.ShowFilter = false;

        

       

    }

 

 

Note: Here I am using Like Operator instead of ==.

If we want to search the records by using wild characters (*,?), it’s suggested to use Like operator instead of ==, while working with Query classes.

 

In this example, Customer Lookup is customized in EP using Query classes, as similar to Ax Client.

This lookup is customized without using any Dataset.

 lookup is based on the Customer account number. 

To get a quick idea about this functionality, see the following figure.

For code and design the application, find the document at the following location

http://cid-f2ec589e221a4db0.skydrive.live.com/self.aspx/.Public/Dynamics%20Ax2009%20EP/Usage%20of%20Query%20Objects%20in%20EP.docx

We can filter the data, simply using grid filters, but this article is mainly to focus on the customization of standard lookup’s according to certain condition.

From this article, we can get an idea about how we can filter the standard lookups in EP

...