Dynamics AX Query Range

(0) Share

Report

Posted on by Saurabh_star

Hi,

Dynamics AX 2009, I want to add a range to a date field (Field name - EndDate) in Query such that it fetches only those records where that 'EndDate' falls in the current month. I don't think I can do that in the AOT object as such, but should be able to code it in the init() method.

The function MthOfYr(systemdateget()) actually gets me close to what I want. I get the numeric value of the month, but still not clear on how do I actually put it in setting the range. Can someone plz help?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Oleg Abapolov 39 on at

Like (0)

Report
Copy link

Link copied!

I believe this can help

queryRange(dateStartMth(currentDate), dateEndMth(currentDate));

Was this reply helpful? Yes No
Saurabh_star on at

Like (0)

Report
Copy link

Link copied!

I was actually expecting a logic such as MthOfYr(endDate) == MthOfYr(systemdateget())

but If thts difficult, the one you mentioned is a fair option as well. Can that be put in the properties window of the range or have to write it in method?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,040 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

In code, you could use dateStartMth() and dateEndMth() together with SysQuery::queryRange().

But there is an easier way that can be use directly in AOT: monthRange(). Please note that functions from SysQueryRangeUtil class must be enclosed in brackets when used in query ranges - e.g. (monthRange()).

Was this reply helpful? Yes No
Suggested answer

Dick Wenning 8,705 Moderator on at

Like (0)

Report
Copy link

Link copied!

look at class SysQueryRangeUtil

public static str monthRange(int relativeMonthsFrom = 0, int relativeMonthsTo = 0)

{

utcdatetime currentDateTime;

utcdatetime monthStartDate;

int dayValue;

currentDateTime = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(), DateTimeUtil::getUserPreferredTimeZone());

dayValue = DateTimeUtil::day(currentDateTime) - 1;

monthStartDate = DateTimeUtil::addDays(currentDateTime, -dayValue);

return SysQueryRangeUtil::dateRange(DateTimeUtil::date(DateTimeUtil::addMonths(monthStartDate,relativeMonthsFrom)),

DateTimeUtil::date(DateTimeUtil::addDays(DateTimeUtil::addMonths(monthStartDate,relativeMonthsTo +1), -1)));

}

in your aot Range (monthRange(-1,0))

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report
Copy link

Link copied!

Hi Saurabh,

It can't be put in properties window

above solution suggested by Oleg, can be used using Query object in init() method

I think solution provided by Oleg is the best solution you have for your requirement

Please verify and let us know if you have more details..

Was this reply helpful? Yes No
Saurabh_star on at

Like (0)

Report
Copy link

Link copied!

Thanks Oleg,

it worked absolutely fine. It was short and simple.. :)

Was this reply helpful? Yes No
Saurabh_star on at

Like (0)

Report
Copy link

Link copied!

Yes Nitesh....you are right. It worked well.

Thanks Dick and Martin as well for your comments.

Was this reply helpful? Yes No
Oleg Abapolov 39 on at

Like (0)

Report
Copy link

Link copied!

The solution suggested by Martin Dráb and Dick Wenning works perfectly:

Was this reply helpful? Yes No