Home > Dynamics Ax > Usage of (OR) condition in Ax Query’s of Ax Client and EP

Usage of (OR) condition in Ax Query’s of Ax Client and EP


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));

Party// Here the OR condition is implemented.  Smile 

    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");

 

Party// Here the OR condition is implemented.  Smile       

 

                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.

 

Advertisements
Categories: Dynamics Ax
  1. Carlos
    May 17, 2011 at 10:27 am

    Many thanks! it’s perfect for me.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: