Advance QueryBuildRange in x++ :
Axapta query on multiple
tables
QueryRun selectReportQuery()
{
Query query = new Query();
QueryRun localQueryRun;
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
QueryBuildDataSource qbds3;
;
qbds1 = query.addDataSource(tableNum(salesTable));
qbds1.addRange(fieldNum(salesTable, affVendorAdjust)).value('1');
qbds2 = qbds1.addDataSource(tableNum(custInvoiceJour));
qbds2.fetchMode(JoinMode::InnerJoin);
qbds2.relations(false);
qbds2.addLink(fieldNum(salesTable, salesId),fieldNum(custInvoiceJour, salesId));
qbds2.addSortField(fieldNum(CustInvoiceJour, OrderAccount), SortOrder::Ascending);
qbds2.addSortField(fieldNum(CustInvoiceJour, InvoiceId), SortOrder::Ascending);
qbds2.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange(invBeginDate, invEndDate));
qbds3 = qbds1.addDataSource(tableNum(salesLine));
qbds3.relations(true);
qbds3.joinMode(JoinMode::InnerJoin);
qbds3.fetchMode(queryFetchMode::One2Many);
qbds3.addRange(fieldNum(salesLine, mbsLineVendor)).value(queryValue(vendAccount));
Join
Two Tables at Run Time
How
to join Two tables at RunTime, without using Datasource
static void QueryJoin2Tables(Args _agrs)
{
AxTestTable1 AxTestTable1; // Table 1
AxTestTable2 AxTestTable2; // another table which you want to join with table 1
Query q;
QueryBuildDataSource qbdAxTestTable1, qbdAxTestTable2; // objects for both tables
QueryBuildRange qbr;
QueryRun qr;
;
q = new Query(); //to make a new query
qbdAxTeatTable2 = q.addDataSource(tablenum(AxTestTable2)); // To Add table to query
qbdAxTesttable2.addRange(fieldnum(AxTestTable2,RollNo)).value("2");
qbdAxTeatTable1 = qbdAxTestTable2.adddataSource(tablenum(AxTestTable));
qbdAxTesttable1.addlink(fieldnum(AXTestTable2,RollNo),fieldnum(AxTestTable,Rollno));
qbdAxTestTable.joinMode(joinMode::InnerJoin);
qr = new QueryRun(q); // to fetch records from query
while(qr.next())
{
AxTestTable1 = qr.get(tablenum(AxTestTable));
info(AxtestTable.RollNo);
}
}
Join Two Tables at Run Time
Use query object to retrieve AOT query
Using Union Query :
static
void IgnTrasnferJournalSecurityUnionQuery(Args _args)
{
Query q;
QueryRun qr;
QueryBuildDataSource qbds;
InventJournalTrans InventJournalTransLoc;
;
{
Query q;
QueryRun qr;
QueryBuildDataSource qbds;
InventJournalTrans InventJournalTransLoc;
;
q =
new Query();
q.queryType(QueryType::Union);
q.queryType(QueryType::Union);
qbds
= q.addDataSource(tablenum(InventJournalTrans),
identifierstr(InventJournalTrans_1));
qbds.addRange(fieldnum(InventJournalTrans, JournalId)).value(queryValue(‘DTAC-00221’));
qbds.addRange(fieldnum(InventJournalTrans, JournalId)).value(queryValue(‘DTAC-00221’));
qbds
= qbds.addDataSource(tablenum(InventDim), identifierstr(InventDim_From));
qbds.joinMode(JoinMode::ExistsJoin);
qbds.joinMode(JoinMode::ExistsJoin);
qbds.fetchMode(QueryFetchMode::One2One);
qbds.relations(false);
qbds.addLink(fieldNum(InventJournalTrans,
InventDimId), fieldNum(InventDim, InventDimId),”InventJournalTrans_1″);
qbds.addRange(fieldnum(InventDim, InventSiteId )).value(queryValue(“A01″));
qbds.addRange(fieldnum(InventDim, InventSiteId )).value(queryValue(“A01″));
qbds
= q.addDataSource(tablenum(InventJournalTrans),
identifierstr(InventJournalTrans_2), UnionType::Union);
qbds.addRange(fieldnum(InventJournalTrans, JournalId)).value(queryValue(‘DTAC-00221’));
qbds.addRange(fieldnum(InventJournalTrans, JournalId)).value(queryValue(‘DTAC-00221’));
qbds
= qbds.addDataSource(tablenum(InventDim), identifierstr(InventDim_To));
qbds.joinMode(JoinMode::ExistsJoin);
qbds.joinMode(JoinMode::ExistsJoin);
qbds.fetchMode(QueryFetchMode::One2One);
qbds.relations(false);
qbds.addLink(fieldNum(InventJournalTrans,
ToInventDimId), fieldNum(InventDim, InventDimId),”InventJournalTrans_2”);
qbds.addRange(fieldnum(InventDim, InventSiteId )).value(queryValue(“A01”));
qr = new QueryRun(q);
qbds.addRange(fieldnum(InventDim, InventSiteId )).value(queryValue(“A01”));
qr = new QueryRun(q);
while (qr.next())
{
InventJournalTransLoc = qr.get(tablenum(InventJournalTrans));
info(strFmt(“%1”,InventJournalTransLoc.Qty));
}
}
{
InventJournalTransLoc = qr.get(tablenum(InventJournalTrans));
info(strFmt(“%1”,InventJournalTransLoc.Qty));
}
}
Using Expressions :
static
void IgnTrasnferJournalSecurityByExpression(Args _args)
{
Query q;
QueryRun qr;
QueryBuildDataSource qbds;
InventJournalTrans InventJournalTransLoc;
str qstr;
;
qstr = strFmt(‘(((%1.InventSiteId == “%2”) || (%3.InventSiteId == “%2”)) && (%4.JournalId == “%5”))’,
“InventDim_From”, queryValue(“A01”),
“InventDim_To”, “InventJournalTrans_1”,queryValue(“DTAC-00221″));
q = new Query();
{
Query q;
QueryRun qr;
QueryBuildDataSource qbds;
InventJournalTrans InventJournalTransLoc;
str qstr;
;
qstr = strFmt(‘(((%1.InventSiteId == “%2”) || (%3.InventSiteId == “%2”)) && (%4.JournalId == “%5”))’,
“InventDim_From”, queryValue(“A01”),
“InventDim_To”, “InventJournalTrans_1”,queryValue(“DTAC-00221″));
q = new Query();
qbds
= q.addDataSource(tablenum(InventJournalTrans),
identifierstr(InventJournalTrans_1));
qbds
= qbds.addDataSource(tablenum(InventDim), identifierstr(InventDim_From));
qbds.joinMode(JoinMode::ExistsJoin);
qbds.joinMode(JoinMode::ExistsJoin);
qbds.fetchMode(QueryFetchMode::One2One);
qbds.relations(false);
qbds.addLink(fieldNum(InventJournalTrans,
InventDimId), fieldNum(InventDim, InventDimId),”InventJournalTrans_1″);
qbds
= qbds.addDataSource(tablenum(InventDim), identifierstr(InventDim_To));
qbds.joinMode(JoinMode::ExistsJoin);
qbds.joinMode(JoinMode::ExistsJoin);
qbds.fetchMode(QueryFetchMode::One2One);
qbds.relations(false);
qbds.addLink(fieldNum(InventJournalTrans,
ToInventDimId), fieldNum(InventDim, InventDimId),”InventJournalTrans_1”);
qbds.addRange(fieldNum(InventJournalTrans,RecId)).value(qstr);
qr = new QueryRun(q);
qr = new QueryRun(q);
while (qr.next())
{
InventJournalTransLoc = qr.get(tablenum(InventJournalTrans));
info(strFmt(“%1”,InventJournalTransLoc.Qty));
}
}
{
InventJournalTransLoc = qr.get(tablenum(InventJournalTrans));
info(strFmt(“%1”,InventJournalTransLoc.Qty));
}
}
static void
ProjectContractInfoViewTest(Args _args)
{
ProjectContractInfoView
projectContractInfoView;
Query
query;
QueryRun qRun;
QueryBuildDataSource qBDSource;
QueryBuildRange
querybuildRange;
CustAccount
customerID;
int i = 0;
customerID =
"1010-1010123";
query = new
Query();
qBDSource =
query.addDataSource(tablenum(ProjectContractInfoView));
querybuildRange
= qBDSource.addRange(fieldnum(ProjectContractInfoView, CustAccount));
querybuildRange.value(customerID);
qRun = new
QueryRun(query);
while
(qRun.next())
{
projectContractInfoView
= qRun.get(tablenum(ProjectContractInfoView));
info("ProjId:
" + projectContractInfoView.ProjId + " Contract Id: " +
projectContractInfoView.ProjInvoiceProjId
+
" Contract Desc: " + projectContractInfoView.ProjContractDesc +
" Customer Name: " + projectContractInfoView.CustomerName);
i++;
}
info(int2str(i)
+ " Records found using range on CustAccount.");
}
Expressions in query ranges
One
of least understood but most powerful Axapta features is the so-called Expressions
in query ranges syntax. This is not the same as
simply using a QueryBuildRange object in a query and specifying a criteria for
a single field.
Contents
[hide]
·
2 Syntax
Introduction[edit]
This
is a method of specifying ranges on queries which allows you to perform complex
comparisons, and create complex join situations which would be impossible using
the standard syntax.
Syntax[edit]
To
use the special syntax, you should first add a range to your
QueryBuildDataSource object in the normal way. Note that for this special
syntax, it does not matter which field you use to add the range.
To
specify the range value itself, certain rules must be followed:
§ The
entire expression must be enclosed within single-quotes, not double-quotes
§ The
entire expression must be enclosed in parenthesis (brackets)
§ Each
sub-expression must be enclosed in its own set of parenthesis
§ For
fields in the current table, simply the field name can be used
§ For
fields in other tables, a prefix of the relevant datasource name must
be added. This is not always the same as the table name.
§ String
values should be surrounded by double-quotes, and wrapped in a call to
queryValue()
§ Enum
values should be specified by their integer value
§ Date
values should be formatted using Date2StrXpp()
§ Blank
string like ‘ ’ will not work as expected, use sysquery::valueEmptyString().
Examples[edit]
In
the example below, we construct a query and add a single datasource.
The
range is then added, using the DataAreaId field on each table. Any field can be
used, but using an unusual one such as DataAreaId helps remind a casual reader
of the code that it's not a normal range.
query = new
Query();
dsInventTable =
query.addDataSource(tableNum(InventTable));
// Add our range
queryBuildRange =
dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
Given
the above, the following are valid range specifications:
Simple criteria[edit]
Find
the record where ItemId is B-R14. Take note of the single quotes and
parenthesis surrounding the entire expression.
queryBuildRange.value(strFmt('(ItemId
== "%1")', queryValue("B-R14")));
Find
records where the ItemType is Service. Note the use of any2int().
queryBuildRange.value(strFmt('(ItemType
== %1)', any2int(ItemType::Service)));
Find
records where the ItemType is Service or the ItemId is B-R14.
Note the nesting of the parenthesis in this example.
queryBuildRange.value(strFmt('((ItemType
== %1) || (ItemId == "%2"))',
any2int(ItemType::Service),
queryValue("B-R14")));
Find
records where the modified date is after 1st January 2000. Note the use of
Date2StrXpp() to format the date correctly.
queryBuildRange.value(strFmt('(ModifiedDate
> %1)', Date2StrXpp(01\01\2000)));
Find
records where the Field is blank (null) or an empty string. For more see
Sys::Query Docs[1]
qbrStatement =
this.query().dataSourceName("BankAccountTrans2").addRange(fieldnum(BankAccountTrans,AccountStatement));
//qbrStatement.value("!?*");//this
is the old way that may not work in future versions of AX
qbrStatement.value(sysquery::valueEmptyString());//this
is the new way
Complex criteria with combined AND and OR clauses[edit]
Find
all records where the ItemType is Service, or both the ItemType is Item and the
ProjCategoryId is Spares. This is not possible to achieve using the standard
range syntax.
Note
also that in this example, we are using the fieldStr() method to specify our
actual field names and again, that we have nested our parenthesis for each
sub-expression.
queryBuildRange.value(strFmt('((%1
== %2) || ((%1 == %3) && (%4 == "%5")))',
fieldStr(InventTable, ItemType),
any2int(ItemType::Service),
any2int(ItemType::Item),
fieldStr(InventTable, ProjCategoryId),
queryValue("Spares")));
WHERE clauses referencing fields from multiple
tables[edit]
For
this example below, we construct a query consisting of two joined datasources
(using an Exists join). Note that we specify the datasource names when adding
the datasources to the query.
The
ranges are then added, using the DataAreaId field on each table as described in
the earlier example.
query = new
Query();
dsInventTable =
query.addDataSource(tableNum(InventTable), tableStr(InventTable));
dsInventItemBarCode
= dsInventTable.addDataSource(tableNum(InventItemBarCode),
tableStr(InventItemBarCode));
dsInventItemBarCode.relations(true);
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);
// Add our two
ranges
queryBuildRange1 =
dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 =
dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));
Find
all records where a bar code record exists for an item and was modified later
than the item was modified.
In
this example, we are using the range on the BarCode table. Therefore the
unqualified ModifiedDate reference will relate to
InventItemBarCode.ModifiedDate. The other field is a fully-qualified one, using
the DatasourceName.FieldName syntax.
queryBuildRange2.value(strFmt('(ModifiedDate
> InventTable.ModifiedDate)'));
Note
that if we had added our InventTable datasource using the following code
dsInventTable =
query.addDataSource(tableNum(InventTable), "InventTableCustomName");
// Note that we are manually specifying a different datasource name
then
the query range would need to appear as follows
queryBuildRange2.value(strFmt('(ModifiedDate
> InventTableCustomName.ModifiedDate)'));
Conditional joins[edit]
We
will modify our previous example slightly, to remove the automatic addition of
relations for the join.
query = new
Query();
dsInventTable =
query.addDataSource(tableNum(InventTable), "InventTable");
dsInventItemBarCode
= dsInventTable.addDataSource(tableNum(InventItemBarCode),
"InventItemBarCode");
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);
// Add our two
ranges
queryBuildRange1 =
dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 =
dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));
We
can now use the query expression to specify whatever we like as the join
criteria.
Find
all records where either the ItemType is Service, or the ItemType is Item and a
barcode exists. The join criteria is only applied in the second half of the
expression, so all Service items will appear irrespective of whether they have
a bar code. Priot to Ax 2012, this was not possible to achieve using the standard
query ranges. From that version onwards, however, the QueryFilter
class can be used to achieve the same result.
queryBuildRange2.value(strFmt('((%1.%2
== %3) || ((%1.%2 == %4) && (%1.%5 == %6)))',
query.dataSourceTable(tableNum(InventTable)).name(), //
InventTable %1
fieldStr(InventTable, ItemType), //
ItemType %2
any2int(ItemType::Service), // %3
any2int(ItemType::Item), // %4
fieldStr(InventTable, ItemId), //
ItemId %5
fieldStr(InventItemBarCode, ItemId)));
// %6
Using
the techniques above, it is possible to create queries with almost as much
flexibility as using SQL statements directly.
Filter on array fields[edit]
queryBuildRange.value(strFmt('((%1.%2
== "%4") || (%1.%3 == "%5"))',
queryBuildDataSource.name(),
fieldid2name(tablenum(<table>),
fieldid2ext(fieldnum(<table>, Dimension),
Dimensions::code2ArrayIdx(SysDimension::Center))),
fieldid2name(tablenum(<table>),
fieldid2ext(fieldnum(<table>, Dimension), Dimensions::code2ArrayIdx(SysDimension::Purpose))),
"some dim2 value",
"some dim3 value"));
Note:
you must always specify the datasource name if you use Query Expression syntax
to filter on array fields. See also Limitations section
at the bottom of the page.
Using wildcards and comma-separated range values[edit]
Again,
the previous example here was using standard syntax, not the special syntax
using expressions. It's not possible to modify the above examples to work with
wildcards.
The
above statement applies to AX versions < 5.0
AX
5.0 introduced solution to wildcards - while you still cannot directly use
wildcards in ranges, now it supports the 'LIKE' keyword.
(AccountNum LIKE
"*AA*" || Name LIKE "*AA*")
Limitations[edit]
The
use of the extended query syntax is not supported by the new having filtering
available in Ax 2012.
There
are two major limitations to the Query Expressions syntax. The first is the
loss of support for wildcards and comma-separated range values, and the second
is the inability to reference array fieldssuch as dimensions in
some older kernel versions.
Whilst
in standard queries you can specify "AA*" or "A,B,C" as
criteria, and they will be parsed by Axapta and sent through correctly to the
database, these will simply be passed directly through when
using the Query Expressions engine. As a result, they will not return the
expected results. On a related noted, the use of 'like' is not supported, so
there is no way to use wildcards in any form.
Query
Expressions syntax for array fields such as the Dimension field is known to be
suppported since the version 5.0.1500.2116 (RU4) for AX 2009. Previous kernel
versions are not tested to support Query Expressions syntax for array fields;
it is also known not to work at all in Axapta 3.
There
is a discussion regarding the use of array fields on the Discussion page for
this article. Please contribute to that discussion if
possible!
Axapta query on multiple
tables
QueryRun selectReportQuery()
{
Query query = new Query();
QueryRun localQueryRun;
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
QueryBuildDataSource qbds3;
;
qbds1 = query.addDataSource(tableNum(salesTable));
qbds1.addRange(fieldNum(salesTable, affVendorAdjust)).value('1');
qbds2 = qbds1.addDataSource(tableNum(custInvoiceJour));
qbds2.fetchMode(JoinMode::InnerJoin);
qbds2.relations(false);
qbds2.addLink(fieldNum(salesTable, salesId),fieldNum(custInvoiceJour, salesId));
qbds2.addSortField(fieldNum(CustInvoiceJour, OrderAccount), SortOrder::Ascending);
qbds2.addSortField(fieldNum(CustInvoiceJour, InvoiceId), SortOrder::Ascending);
qbds2.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange(invBeginDate, invEndDate));
qbds3 = qbds1.addDataSource(tableNum(salesLine));
qbds3.relations(true);
qbds3.joinMode(JoinMode::InnerJoin);
qbds3.fetchMode(queryFetchMode::One2Many);
qbds3.addRange(fieldNum(salesLine, mbsLineVendor)).value(queryValue(vendAccount));
static void CustTableSales1(Args
_args)
{
Query query;
QueryRun queryrun;
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
QueryBuildRange qbr1;
QueryBuildRange qbr2;
CustTable custTable;
;
query = new query();
qbds1 = query.addDataSource(tablenum(CustTable));
qbds1.addSortField(fieldnum(custTable,AccountNum),Sortorder::Descending);
qbr1 = qbds1.addRange(fieldnum(custTable,custGroup));
qbr1.value(queryvalue('10'));
qbr2 = qbds1.addRange(fieldnum(custTable,Blocked));
qbr2.value(queryvalue(CustVendorBlocked::No));
qbds2 = qbds1.addDataSource(tablenum(SalesTable));
qbds2.relations(false);
qbds2.joinMode(joinmode::ExistsJoin);
qbds2.addLink(fieldnum(CustTable,AccountNum),fieldnum(SalesTable,CustAccount));
queryrun = new queryrun(query);
while(queryrun.next())
{
custTable = queryrun.get(tablenum(custTable));
info(strfmt("%1 - %2",custtable.AccountNum,custTable.Name)); // to check your result
}
}
{
Query query;
QueryRun queryrun;
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
QueryBuildRange qbr1;
QueryBuildRange qbr2;
CustTable custTable;
;
query = new query();
qbds1 = query.addDataSource(tablenum(CustTable));
qbds1.addSortField(fieldnum(custTable,AccountNum),Sortorder::Descending);
qbr1 = qbds1.addRange(fieldnum(custTable,custGroup));
qbr1.value(queryvalue('10'));
qbr2 = qbds1.addRange(fieldnum(custTable,Blocked));
qbr2.value(queryvalue(CustVendorBlocked::No));
qbds2 = qbds1.addDataSource(tablenum(SalesTable));
qbds2.relations(false);
qbds2.joinMode(joinmode::ExistsJoin);
qbds2.addLink(fieldnum(CustTable,AccountNum),fieldnum(SalesTable,CustAccount));
queryrun = new queryrun(query);
while(queryrun.next())
{
custTable = queryrun.get(tablenum(custTable));
info(strfmt("%1 - %2",custtable.AccountNum,custTable.Name)); // to check your result
}
}
Join
Two Tables at Run Time
How
to join Two tables at RunTime, without using Datasource
static void QueryJoin2Tables(Args _agrs)
{
AxTestTable1 AxTestTable1; // Table 1
AxTestTable2 AxTestTable2; // another table which you want to join with table 1
Query q;
QueryBuildDataSource qbdAxTestTable1, qbdAxTestTable2; // objects for both tables
QueryBuildRange qbr;
QueryRun qr;
;
q = new Query(); //to make a new query
qbdAxTeatTable2 = q.addDataSource(tablenum(AxTestTable2)); // To Add table to query
qbdAxTesttable2.addRange(fieldnum(AxTestTable2,RollNo)).value("2");
qbdAxTeatTable1 = qbdAxTestTable2.adddataSource(tablenum(AxTestTable));
qbdAxTesttable1.addlink(fieldnum(AXTestTable2,RollNo),fieldnum(AxTestTable,Rollno));
qbdAxTestTable.joinMode(joinMode::InnerJoin);
qr = new QueryRun(q); // to fetch records from query
while(qr.next())
{
AxTestTable1 = qr.get(tablenum(AxTestTable));
info(AxtestTable.RollNo);
}
}
Join Two Tables at Run Time
static void theAxapta_JoinTables(Args _args)
{
Query query;
QueryBuildDataSource queryBuildDataSource1,
queryBuildDataSource2;
QueryBuildRange queryBuildRange;
QueryBuildLink queryBuildLink;
;
// Create a new query object
query = new Query();
// Add the first data source to the query
queryBuildDataSource1 = query.addDataSource(tablenum(CarTable));
// Add the range to this first data source
queryBuildRange = queryBuildDataSource1.addRange(fieldnum(CarTable, ModelYear));
// Add the second datasource to the first data source
queryBuildDataSource2 = queryBuildDataSource1.addDataSource(tablen
um(RentalTable));
// Add the link from the child data source to the
//parent data
source
queryBuildLink = queryBuildDataSource2.addLink(fieldnum(CarTable,
CarId),fieldnum(RentalTable, CarId));
}
Note:
This process (query through X++ code) is very similar to create a query directly through AOT node.
AOT -> Query -> Right Click -> New Query
I would suggest first create a query through query node, than go for this code.
{
Query query;
QueryBuildDataSource queryBuildDataSource1,
queryBuildDataSource2;
QueryBuildRange queryBuildRange;
QueryBuildLink queryBuildLink;
;
// Create a new query object
query = new Query();
// Add the first data source to the query
queryBuildDataSource1 = query.addDataSource(tablenum(CarTable));
// Add the range to this first data source
queryBuildRange = queryBuildDataSource1.addRange(fieldnum(CarTable, ModelYear));
// Add the second datasource to the first data source
queryBuildDataSource2 = queryBuildDataSource1.addDataSource(tablen
um(RentalTable));
// Add the link from the child data source to the
//parent data
source
queryBuildLink = queryBuildDataSource2.addLink(fieldnum(CarTable,
CarId),fieldnum(RentalTable, CarId));
}
Note:
This process (query through X++ code) is very similar to create a query directly through AOT node.
AOT -> Query -> Right Click -> New Query
I would suggest first create a query through query node, than go for this code.
Query query = new Query();
QueryBuildDataSource qbds,qbds1,qbds2;
qbds = query.addDataSource(tableNum(CustTable));
qbds1 = qbds.addDataSource(tableNum(CustTrans));
qbds2 = qbds1.addDataSource(tableNum(CustLedgerTransTypeMapping));
qbds1.addLink(fieldNum(CustTrans,AccountNum),fieldNum(CustTable,AccountNum));
qbds2.addLink(fieldNum(CustTrans,TransType),fieldNum(CustLedgerTransTypeMapping,CustSettleTransType));
info(query.toString());
------------------
static void Job20(Args _args)
{
Query q;
QueryRun qr;
QueryBuildDatasource qbds1, qbds2;
QueryBuildRange qbr;
LedgerTable _LedgerTable;
;
q = new Query();
qbds1 = q.addDataSource(tablenum(LedgerTable));
qbds2 = qbds1.addDataSource(tablenum(LedgerTrans));
qbds2.relations(TRUE); //this enforces a relationship between this datasource and its parent. Relationships defined in the Data Dictionary are used by default.
qbr = qbds1.addRange(fieldnum(LedgerTable,
AccountNum));
//qbr.value(SysQuery::value("10000")); //SysQuery object provides various static methods to assist in defining Query criteria. The SysQuery::value() method should always be used when defining a singular value for a range.
qr = new QueryRun(q);
while(qr.next())
{
//do something
info(_LedgerTable.AccountNum);
}
}
QueryBuildDataSource qbds,qbds1,qbds2;
qbds = query.addDataSource(tableNum(CustTable));
qbds1 = qbds.addDataSource(tableNum(CustTrans));
qbds2 = qbds1.addDataSource(tableNum(CustLedgerTransTypeMapping));
qbds1.addLink(fieldNum(CustTrans,AccountNum),fieldNum(CustTable,AccountNum));
qbds2.addLink(fieldNum(CustTrans,TransType),fieldNum(CustLedgerTransTypeMapping,CustSettleTransType));
info(query.toString());
------------------
static void Job20(Args _args)
{
Query q;
QueryRun qr;
QueryBuildDatasource qbds1, qbds2;
QueryBuildRange qbr;
LedgerTable _LedgerTable;
;
q = new Query();
qbds1 = q.addDataSource(tablenum(LedgerTable));
qbds2 = qbds1.addDataSource(tablenum(LedgerTrans));
qbds2.relations(TRUE); //this enforces a relationship between this datasource and its parent. Relationships defined in the Data Dictionary are used by default.
qbr = qbds1.addRange(fieldnum(LedgerTable,
AccountNum));
//qbr.value(SysQuery::value("10000")); //SysQuery object provides various static methods to assist in defining Query criteria. The SysQuery::value() method should always be used when defining a singular value for a range.
qr = new QueryRun(q);
while(qr.next())
{
//do something
info(_LedgerTable.AccountNum);
}
}
============================= for Fincancial Dimension
=====================
queryBuildRange.value(strFmt('((%1.%2 == "%4") || (%1.%3 ==
"%5"))',
queryBuildDataSource.name(),
fieldid2name(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension),
Dimensions::code2ArrayIdx(SysDimension::Center))),
fieldid2name(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension),
Dimensions::code2ArrayIdx(SysDimension::Purpose))),
"some dim2 value",
"some dim3 value"));
QueryRun
qr;
//1st
DataSource
QueryBuildDataSource
qbdTableSourcefromQuery; // get query from the AOT Query which used by System
in SysQuery()
QueryBuildDataSource qbdTableSourcefromCode; // newly added Query for Account Number Range
QueryBuildDataSource qbdTableSourcefromCode; // newly added Query for Account Number Range
qr =
new QueryRun(“your Query”);
qbdCustTableSourcefromQuery
= SysQuery::findOrCreateDataSource(qr.query(),
tableNum(TableName1));
SysQuery::findOrCreateRange(qbdCustTableSourcefromQuery,
fieldNum(TableName1, FieldName)).value(“QueryValue”);
//2nd
DataSource
qbdCustTableSourcefromCode
= qbdCustTableSourcefromQuery.addDataSource(tableNum(TableName1),
“TableNameNew”);
qbdCustTableSourcefromCode.relations(true);
qbdCustTableSourcefromCode.joinMode(JoinMode::ExistsJoin);
qbdCustTableSourcefromCode.addRange(fieldNum(TableName1, FieldName)).value(queryValue(“QueryValue”));
qbdCustTableSourcefromCode.relations(true);
qbdCustTableSourcefromCode.joinMode(JoinMode::ExistsJoin);
qbdCustTableSourcefromCode.addRange(fieldNum(TableName1, FieldName)).value(queryValue(“QueryValue”));
Create and add
datasource with range in X++
// Code using X++ to build the query
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds;
ProjTable ProjTable;
;
query = new Query();
// Add a datasource to the query
qbds = query.addDataSource(tableNum(ProjTable));
// Add a range to the newly added datasource.
qbds.addRange(fieldNum(ProjTable, ProjId)).value("00403_1036..00412_1036");
queryRun = new QueryRun(query);
while(queryRun.next())
{
projTable = queryRun.get(tableNum(ProjTable));
info(projTable.ProjId + ", " + ProjTable.Name);
}
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds;
ProjTable ProjTable;
;
query = new Query();
// Add a datasource to the query
qbds = query.addDataSource(tableNum(ProjTable));
// Add a range to the newly added datasource.
qbds.addRange(fieldNum(ProjTable, ProjId)).value("00403_1036..00412_1036");
queryRun = new QueryRun(query);
while(queryRun.next())
{
projTable = queryRun.get(tableNum(ProjTable));
info(projTable.ProjId + ", " + ProjTable.Name);
}
Use query object to retrieve AOT query
// Code using a query string
static void UseAOTQuery(Args _args)
{
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
ProjTable projTable;
query = new query(queryStr(ProjTable));
queryRun = new QueryRun(query);
while (queryRun.next())
{
projTable= queryRun.get(tableNum(ProjTable));
info (strFmt("%1 - %2", ProjTable.ProjId, ProjTable.Name));
}
}
static void UseAOTQuery(Args _args)
{
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
ProjTable projTable;
query = new query(queryStr(ProjTable));
queryRun = new QueryRun(query);
while (queryRun.next())
{
projTable= queryRun.get(tableNum(ProjTable));
info (strFmt("%1 - %2", ProjTable.ProjId, ProjTable.Name));
}
}
static void
CreateQuery6Job(Args _args)
{
TreeNode
treeNodeObj;
Query queryObj; // Extends TreeNode class.
QueryBuildDataSource qbds;
QueryBuildRange qbr;
QueryRun qr;
CustTable
xrecCustTable;
str queryName = "MyQuery1";
// Macro.
#AOT
// Delete the query from the AOT, if the query exists.
treeNodeObj = TreeNode::findNode(#QueriesPath);
treeNodeObj = treeNodeObj.AOTfindChild(queryName);
if (treeNodeObj) { treeNodeObj.AOTdelete(); }
// Add the query to the AOT.
treeNodeObj = TreeNode::findNode(#QueriesPath);
treeNodeObj.AOTadd(queryName);
queryObj
= treeNodeObj.AOTfindChild(queryName);
// Further define the query.
qbds = queryObj.addDataSource(tablenum(CustTable));
qbr = qbds.addRange(fieldnum(CustTable,
DlvMode));
qbr.value(">10");
// Compile the query.
queryObj.AOTcompile(1);
queryObj.AOTsave();
// Run the query.
qr = new QueryRun("CustTable");
while ( qr.next() )
{
xrecCustTable = qr.GetNo(1); // 1 means first data source.
Global::info(strFmt("%1 , %2",
xrecCustTable.AccountNum, xrecCustTable.DlvMode));
}
// Delete the query from the AOT.
treeNodeObj = TreeNode::findNode(#QueriesPath);
treeNodeObj = treeNodeObj.AOTfindChild(queryName);
treeNodeObj.AOTdelete();
}
static void _AOTquery(Args _args)
{
date dateFrom = 01\01\2012 ;
date dateTo = 31\01\2012 ;
CustInvoiceJour
custInvoiceJour;
Query query = new Query(queryStr
(CustInvoiceJour)); // Query name.
QueryRun qr;
QueryBuildRange qbr;
;
// Find the InvoiceDate range
on AOT Query.
qbr = query.dataSourceTable(
tablenum (CustInvoiceJour))
.findRange( fieldNum
(CustInvoiceJour, InvoiceDate));
// We can check if the query
range does exist, if not, we can create it.
if (!qbr)
{
qbr =
query.dataSourceTable( tableNum (CustInvoiceJour))
.addRange( fieldNum
(CustInvoiceJour, InvoiceDate));
}
// Assigning query range
value.
qbr.value(SysQuery::range(dateFrom, dateTo));
// We can also define an Order
By through code.
query.dataSourceTable(
tableNum (CustInvoiceJour))
.addOrderByField( fieldNum
(CustInvoiceJour, OrderAccount));
// Executing our query.
qr = new QueryRun(query);
// Looping through query
results.
while (qr.next())
{
// Assinging query results
to table buffer.
custInvoiceJour =
qr.getNo( 1 );
// Showing results.
info( strFmt ('%1 - %2 -
%3' , custInvoiceJour.InvoiceDate,
custInvoiceJour.SalesId,
custInvoiceJour.OrderAccount));
}
}
Facebook
2 comments:
Great post.
I every time emailed this blog post page to all my associates,
as if like to read it afterward my friends will too.
Post a Comment