Tuesday, September 9, 2008

Dynamics Ax - Use the Arithmetic Functions with query classes

Dynamics Ax - Using the Arithmetic functions with the query classes. Smile

Query query;
QueryBuildDataSource queryBuildDataSource;
query = new Query();
queryBuildDataSource = query.addDataSource(TableNum(AssetTrans));
queryBuildDataSource.orderMode(OrderMode::GROUPBY);
queryBuildDataSource.addSelectionField(FieldNum(AssetTrans,amountMST), SelectionField::SUM);
queryBuildDataSource.addSelectionField(FieldNum(AssetTrans,revaluationAmount), SelectionField::SUM);
queryBuildDataSource.addSortField(FieldNum(AssetTrans, transType));
queryBuildDataSource.addRange(FieldNum(AssetTrans,assetId)).value(rangeAssetId);
queryBuildDataSource.addRange(FieldNum(AssetTrans,bookId)).value(rangeBookId);
queryBuildDataSource.addRange(FieldNum(AssetTrans, transDate)).value(queryRange(dateFrom, dateTo));
return query;

Result of the above Query: Hot SELECT SUM(AmountMST), SUM(RevaluationAmount) FROM AssetTrans GROUP BY AssetTrans.TransType ASC WHERE ((AssetId = FA-000001)) AND ((BookId = COMPUTERS)) AND ((TransDate<=12/31/2153))

Another simple query using the query classes.Tongue out

Query query = new Query();
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;

queryBuildDataSource = query.addDataSource(tablenum(PurchParmTable));
queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, Ordering));
querybuildrange.value(queryvalue(documentstatus::PackingSlip));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, PurchId));
querybuildrange.value(queryvalue(purchparmtable.PurchId));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, ParmJobStatus));
querybuildrange.value(queryvalue('Executed'));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, Invoiced));
querybuildrange.value(queryvalue('No'));

this.query(query);

These are the simple examples using the query classes. Eye-rolling

No comments: