Microsoft Dynamics AX (Archived)

Range Expression with date

(0) Share

Report

Posted on by OrangeRocks

700

Dear community,

I am using an expression in query range and my date range seem not to work:

qbr.value( strfmt( '  ((( %1.%2 == "%3" ) || (%1.%2 == "%4"))) &&   (( %1.%5 == "%6" )) ',        

queryLedgerTrans.dataSourceTable( TableNum( LedgerTrans )).name(), 
fieldStr( LedgerTrans, AccountNum ),                                
queryValue(ProjParameters::find().customParameter ),                                                                  queryValue(ProjParameters::find().customParameter2  ),

fieldStr( LedgerTrans, TransDate ),                              
SysQuery::range( fromDate, toDate ))    ));

The first expression is successfully included, but the date range not. Is it crrect written? Or it is not possible to consider date ranges and I have to break down the range to TransDate>= fromDate && TransDate <= toDate ?

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

Xiaoyun Fu 25 on at

Like (0)

Report

You could try using a separate the range for the TransDate condition instead of lump all the conditions in the expression:

qbr = SysQuery::findOrCreateRange(qbdsLedgerTrans,fieldnum(LedgerTrans,TransDate));

qbr.Value(SysQuery::range(fromDate, toDate));

Let me know if this works!

Was this reply helpful? Yes No
Verified answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Hi,

As suggested by Xiaoyun Fu, you need to define two ranges one on field AccountNum and another on field TransDate of table LedgerTrans. Your code typically looks like below

QueryBuildRange qbr, qbr1; QueryBuildDataSource qbds; Query queryLedgerTrans; TransDate fromDate, toDate; queryLedgerTrans = new Query(); qbds = queryLedgerTrans.addDataSource(tableNum(LedgerTrans)); qbr = qbds.addRange(fieldnum(LedgerTrans, AccountNum)); qbr.value( strfmt('((( %1.%2 == "%3" ) || (%1.%2 == "%4")))', queryLedgerTrans.dataSourceTable(TableNum(LedgerTrans)).name(), fieldStr(LedgerTrans, AccountNum), queryValue(ProjParameters::find().custParameter), queryValue(ProjParameters::find().custParameter2))); qbr1 = qbds.addRange(fieldnum(LedgerJournalTrans, TransDate)); qbr1.value(queryRange( fromDate, toDate));

Was this reply helpful? Yes No
OrangeRocks 700 on at

Like (0)

Report

Thank you guys, it worked!

Was this reply helpful? Yes No
Menna Allah Ahmed 163 on at

Like (0)

Report

How to make date not between this two dates ?

I want to make trans date not in query range

Was this reply helpful? Yes No
Suggested answer

GirishS 27,827 Moderator on at

Like (0)

Report
You can try like below and see whether it works.

qbr.value( strfmt('((( %1.%2 != "%3" ) || (%1.%2 != "%4")))', queryLedgerTrans.dataSourceTable(TableNum(TableName)).name(), fieldStr(LedgerTrans, Date), queryValue(dateValue1), queryValue(dateValue2)));

Thanks,

Girish S.

Was this reply helpful? Yes No

Community site session details

Range Expression with date

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Range Expression with date

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates