Finance | Project Operations, Human Resources, ...

Query range in execute query D365 F&O

(1) Share

Report

Posted on by CU21052024-0

265

Hi All,

I am getting "invalid range" error when clicking the button. Below is the relevant code, I want to filter the form based on a toggle control.
If the toggle is checked, the filter condition should be:
InvoiceDate <= Today() - TotalPeriod
However, TotalPeriod represents a number of months (e.g., 2 or 3). the intended logic should be
InvoiceDate <= AddMonths(Today(), -TotalPeriod). Any suggestions/help would be greatly appreciated.

if (Cntrl. checked())

{

qbds.addRange(fieldnum(CustInvoiceTable, TotalPeriod));

QueryBuildRange criteriaCreditl = qbds.addRange(fieldnum(CustInvoiceTable, InvoiceDate)):

criteriaCreditl.value(strFmt('(%1 < =(date2StrXpp(DateTimeUtil::date(DateTimeUtil::addmonths(today(), -%2))))', fieldnum(CustInvoiceTable, InvoiceDate), fieldnum(CustInvoiceTable, TotalPeriod)));
}
next executeQuery();

Thanks!

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

André Arnaud de Cal... 301,293 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!
Hi,

In case you need a range on the invoice date before your calculated date, your range value has a wrong syntax.

The result should be something like: '.. 23/05/2025'

Your current range value has a complete wrong syntax where the outcome should be something like this: '(32 < =(date2StrXpp(DateTimeUtil::date(DateTimeUtil::addmonths(today(), -47))))'. You can check this by using the debugger.

Change your coding to:

criteriaCreditl.value(strFmt('.. %1', date2StrXpp(DateTimeUtil::date(DateTimeUtil::addmonths(today(), CustInvoiceTable.TotalPeriod)))));

Was this reply helpful? Yes No
Martin Dráb 238,024 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
You can also use queryRange() to avoid the format string completely. In my example, I've fixed one more issues (that today() shouldn't be used). I'm assuming that criteriaCreditl refers to a date field; it it was an utcDateTime field, you would need to use UTC time instead.

utcDateTime today = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()); utcDateTime adjustedDate = DateTimeUtil::date(DateTimeUtil::addMonths(today, custInvoiceTable.TotalPeriod)); criteriaCreditl.value(queryRange(dateNull(), adjustedDate));

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 238,024 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
It seems that your intention is your intention is to calculate an adjusted date based on two fields of the same record and then use it in a comparison with a different field. You have two options:

Either you do it in database, but then you can't use X++ code such as DateTimeUtil::addmonths().

Or you'll first fetch the record to F&O, calculate the value by X++ and use the result in the filter.

Your current attempt to create a SQL condition containing X++ functions can't work.

Consider creating a view with a computed column that will calculate the date based on InvoiceDate and TotalPeriod. Instead of X++ function DateTimeUtil::addMonths(), you'll need T-SQL function DATEADD(). You can then simply compare the other field with the computed column.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Query range in execute query D365 F&O

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Query range in execute query D365 F&O

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates