performance related

http://benchmarktoolkit.codeplex.com/

SysGlobalObjectCache
Data Management Framework
Dynamics Management View
xClassTrace
runClassMethodIL
sysglobalconfiguration table

Common table

Recid,CreateDate ..etc are inherited form Common table and Recid is not only use for index,RECID is also unique ID

Therefore we can use sth like this to addRange:

qB1.addRange(fieldname2id(tablename2id('smmActivities'),fieldstr(Common,RecId))).value(queryValue(actNum));

good example of adding relationship to the query

between 2 table:
static void Job6(Args _args)
{
DictRelation dr1,dr2;
Query q;
QueryBuildDataSource qB1,qB2;

q = new Query();
qB1 = q.addDataSource(tablename2id('smmActivities'));

dr1 = new DictRelation(tablename2id('smmActivities'));
if(dr1.loadTableRelation(tablename2id('smmBusRelTable')))
{
qB2 = qB1.addDataSource(tablename2id('smmBusRelTable'));
qB2.addRelation(dr1);
}

info(qB2.toString());

}

between 3 table:
static void Job6(Args _args)
{
DictRelation dr, dr1;
Query q;
QueryBuildDataSource qB1,qB2,qB3;

dr = new DictRelation(tablename2id('smmActivities'));
dr.loadTableRelation(tablenum(smmActivityParentLinkTable));

dr1 = new DictRelation(tablenum(smmActivityParentLinkTable) );
dr1.loadTableRelation(tablename2id('smmBusRelTable'));

q = new Query();
qB1 = q.addDataSource(tablename2id('smmActivities'));
qB1.addRange(fieldname2id(tablename2id('smmActivities'),fieldstr(Common,RecId))).value(queryValue(docuRef.RefRecId));

qB2 = qB1.addDataSource(tablenum(smmActivityParentLinkTable));
qB2.addRelation(dr);

qB3 = qB2.addDataSource(tablename2id('smmBusRelTable'));
qB3.addRelation(dr1);


info(qB1.toString());

}

price2Amount

pricedisc 裡面有一個 static 的 function price2Amount:
用來計算所有 AX 裡面的 price 基本上邏輯如下

price2Amount(PriceCur price,
PriceUnit priceUnit,
DiscAmount discAmount,
Qty qtyNow,
Qty qty,
MarkupAmount markup,
DiscPct discPercent,
CurrencyCode currencyCode,
Amount amount,
boolean forceAmountCalculation = false)


returnAmount = (qtyNow * ( (price - discAmount) / priceUnit) + (qtyNow >= 0 ? markup : -markup) ) * (100 - discPercent) / 100;

(price - discAmount) 計算減掉 discount 後的值 => / priceUnit => 除以單位數得到單價 =>(qtyNow >= 0 ? markup : -markup) 現在 Deliver Now 是多少 大於零 + markup 小於零 -markup =>得到真正的總單家 qtyNow * ( => 乘上 Deliver Now 的數量得到總價 => * (100 - discPercent) / 100 => 算出打折以後的值

基本的 template

Static void QueryTest(Args _args)
{
Query query;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
QueryRun queryRun;
InventTable inventTable;

;
Query = new Query();
Qbds = query.addDataSource(tablenum(InventTable),’InventTable’);

//add range at here
Qbr = qbds.addRange(fieldnum(InventTable,ItemId));
Qbr.value(‘I-999,I-888,I-777’);

queryRun = new QueryRun(query);
while(queryRun.next())
{
inventTable = queryRun.get(tablenum(InventTable));
print inventTable.recId;
}

Pause;
}

QueryRange 有趣的 cases

當寫 custom Query Range 的時候, 可以使用任何一個 Table 的 Field 來取得 querybuildrange, 然後 querybuildrange 的 value 可以跟用來取得 querybuildrange 的 Field 豪不相關, 你可以使用通用的 field 來去得, 譬如說 RecId 或是 DataAreaId. 下面的例子使用, RecId 取得 querybuildrange 然後用 query 的 field 卻是 AccountNum 以及 Name

qbr = qbd.addRange(FieldNum(CustTable, RecId));
qbr.value(strFmt('((%1 == "4000") || (%2 == "Bob"))',
fieldStr(CustTable, AccountNum),
fieldStr(CustTable, Name)));

==================================================================

QueryBuild Range 裡面使用 OR 的例子:

例子1: 使用 ,

Query q;
QueryBuildDataSource qbd;
QueryBuildRange qbr;
q = new Query();
qbd = q.addDataSource(TableNum(CustTable));
qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
qbr.value('100, 200');

結果:
SELECT * FROM CustTable WHERE ((((AccountNum == "100") || (AccountNum == "200"))))

例子2:

qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));
qbr.value(strFmt('((%1.%2 == "1000") || (%1.%3 == "ABC"))',
qbd.name(),
fieldStr(CustTable, AccountNum),
fieldStr(CustTable, Name)));

結果:
SELECT * FROM CustTable WHERE ((((CustTable_1.AccountNum == "1000") || (CustTable_1.Name == "ABC"))))

==================================================================

基本型:
Qbr = qbds.addRange(fieldnum(InventTable,ItemId));
Qbr.value(‘I-999’);

Qbr = qbds.addRange(fieldnum(InventTable,RecId));
Qbr.value(5646876135);

可用queryvalue把 value 先包起來讓 AX 處理譬如說 NULL 或是 datetime 等特別型態以求安全
qbr.value(queryvalue(‘I-999’)

==================================================================
Query Expression:

Qbr = qbds.addRange(fieldnum(InventTable,ItemId));
Qbr.value(strfmt(‘(%1 == “%2”)’,fieldstr(InventTable,ItemId),queryvalue(‘I-999’));

strfmt 注意:
a) value should be inside ''
b) every expression should have ( )
‘((%1 == %2) || (%1 == %3))’
c) %n expression order is expressed by this format
d)if it is a string, it should use ""
Strfmt(‘(%1 == “%2”)’,fieldstr(InventTable,ItemId),queryvalue(‘I-999’));

==================================================================


SOURCE #init
#public void init()
#{
# QueryBuildDataSource queryBuildDataSource;
# QueryBuildRange rangeHours;
#
# super();
#
# queryBuildDataSource = this.query().dataSourceName(tablestr(TSTimesheetLineWeek) );
# rangeHours = queryBuildDataSource.addRange(fieldid2ext(fieldnum(TSTimesheetLineWeek, Hours),1));
# rangeHours.value(strFmt('((%1.%2) != %9) || ((%1.%3) != %9) || ((%1.%4) != %9) || ((%1.%5) != %9) || ((%1.%6) != %9) || ((%1.%7) != %9) || ((%1.%8) != %9)',
# queryBuildDataSource.name(),
# fieldid2name(tablenum(TSTimesheetLineWeek),fieldid2ext( fieldnum(TSTimesheetLineWeek, Hours), 1 )),
# fieldid2name(tablenum(TSTimesheetLineWeek),fieldid2ext( fieldnum(TSTimesheetLineWeek, Hours), 2 )),
# fieldid2name(tablenum(TSTimesheetLineWeek),fieldid2ext( fieldnum(TSTimesheetLineWeek, Hours), 3 )),
# fieldid2name(tablenum(TSTimesheetLineWeek),fieldid2ext( fieldnum(TSTimesheetLineWeek, Hours), 4 )),
# fieldid2name(tablenum(TSTimesheetLineWeek),fieldid2ext( fieldnum(TSTimesheetLineWeek, Hours), 5 )),
# fieldid2name(tablenum(TSTimesheetLineWeek),fieldid2ext( fieldnum(TSTimesheetLineWeek, Hours), 6 )),
# fieldid2name(tablenum(TSTimesheetLineWeek),fieldid2ext( fieldnum(TSTimesheetLineWeek, Hours), 7 )),
# 0
# ));
#}
ENDSOURCE

QueryBuildRange.Value wildcard example

How to use "Like" operator in the QueryBuildRange.Value
To make something as the "LIKE" operator in a query, you should just assign a value to the queryRange including a wildcard.

The query framework will then change the statement into a LIKE statement. If the value does not contain a wildchard the query framework will change the statement to ==.

Example:


static void QueryBuildRange_Like(Args _args){ Query query = new Query(); QueryRun queryRun; QueryBuildDataSource queryDataSource; QueryBuildRange queryRange; CustTable custTable; ;
queryDataSource = query.addDataSource(tablenum(CustTable));
queryRange = queryDataSource.addRange(fieldnum(CustTable, Name)); queryRange.value('The*'); queryRun = new QueryRun(query); while (queryRun.next()) { custTable = queryRun.get(tablenum(CustTable)); info (custTable.Name); }}


This posting is provided "AS IS" with no warranties, and confers no rights.