Search This Blog

Wednesday, May 23, 2012

AX2012 Query Filter Usage


In this Example I am using QueryFilter(new class in AX2012 introduced), So I am using this class to filter the data accordingly which have the outerjoins and get the number of records in each table.

static void Krishh_QueryRangeFilter(Args _args)
{
    Query                   query;
    QueryBuildDataSource    datasource;
    QueryBuildRange         range;
    QueryFilter             filter;//new class in Ax2012
    QueryRun                queryRun;
     int                     counter = 0, totalCounter = 0;
 
    query = new Query();
    datasource = query.addDataSource(tableNum(ChangeOrderJournal));
    datasource = datasource.addDataSource(tableNum(ChangeOrderProduct));
    datasource.joinMode(JoinMode::OuterJoin);
    datasource.relations(true);
    datasource.addLink(fieldNum(ChangeOrderJournal, ChangeOrderNum),
            fieldNum(ChangeOrderProduct, ChangeOrderNum));

// initializes the filter for the specific field.
    filter = query.addQueryFilter(datasource, fieldStr(ChangeOrderProduct, PartNumber));
    filter.value("2-24-05902");

// this is the querybuildRange usage
 //range = datasource.addRange(fieldNum(ChangeOrderProduct ,   PartNumber));
 //range.value(SysQuery::value(' 2-24-05902 '));
 
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        totalCounter++;
        if (queryRun.changed(tableNum(ChangeOrderProduct)))
            counter++;
    }
    info(strFmt("ChangeOrderProduct Counter: %1", counter));
}

2 comments:

  1. Krishna, how do you add a twist to the query filter, by using a variable value from the hosting form rather than a constant (like 2-24-05902)?

    I need to end up showing, in the form's info part, a listing from a table that is not directly related to the form's data source, filtering the records based on SQL-like condition (t.f like '%/parm' or t.f like '%/parm/%'), where t.f is table, field on the info part and parm is a value from the hosting form.

    I tried using auto-link feature but it didn't work for me, first because the two tables are not easily related, and secondly AX doesn't seem to like the like (no pun intended) operations, especially compounded with *or*.

    Thank you very much for your time.

    Leo

    ReplyDelete
  2. hi Sir,

    I have a doubt Pls clarify me .
    am creating new form (Firm order). this form is duplicate of SalesOrder form.

    In Sale order form i have created a check box

    but firm order form is only of view type .the salesorder form record appear in firm order form.

    the record will appear in two condition.

    1)first - SalesOrder Reference number is empty at a time in salesLine i select a check box is checked .

    Now in firm order the records without reference number and checkbox that is selected those records will appear.

    .

    2)Second -- SalesTable form SO is created but Reference number is empty and salesLine item is not created .but this Record is should not appear in firm order form. But it appears for me . how to filter it .

    am using Query in executed query method query is below

    This Query write in SalesTableDataSource in firm order form

    ( queryBuildRange = this.query().dataSourceNo(1).addRange(fieldnum(salesTable,PurchOrderFormNum));

    queryBuildRange.value(sysQuery::valueEmptyString()); )

    This Query is written in SalesLineDataSource in firm order form

    queryBuildRange = this.query().dataSourceNo(1).addRange(fieldnum(SalesLine, check));

    queryBuildRange.value("Yes");



    This query is satisfy first condition.

    how to write second condition code

    ReplyDelete

Thanks for visiting my blog,
I will reply for your comment within 48 hours.

Thanks,
krishna.