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));
}
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)?
ReplyDeleteI 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
hi Sir,
ReplyDeleteI 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