Finance | Project Operations, Human Resources, ...

Advanced filter - does not occur in current month

(0) Share

Report

Posted on by Community member

Hello, I am looking for some help on an advanced filter. I want to be able to filter on items that do not occur in the current month.

So if I have a list of 10 accounts, 8 of which have a value in the current month and 2 that do not, I want to locate the 2 that do not. I can't seem to find a filter that will allow me to find the two.

I want to apply this as part of a single batch job that I will run on, for example, the 3rd of the Month. So whatever I apply, I want to run each month and provide me with only the accounts that do not have a date value populated within the current month for this data item.

Is this possible?

Thanks, Kyle

All responses (5)

Answers (0)

Suggested answer

ColbyGallagher 3,664 on at

Like (0)

Report

Advanced filter - does not occur in current month

You could do this as two queries like Andre suggested, but just adding it as two ranges on the same field will get you the result you are looking for, as criteria on the same field, but on multiple rows, are OR'ed together.

So this:

Is the same as saying "I want all ship dates from 999 months ago to 1 months ago OR 1 months from now to 999 months from now"
Suggested answer

André Arnaud de Cal... 290,900 Super User 2024 Season 2 on at

Like (0)

Report

Advanced filter - does not occur in current month

Hi Kyle,

You can use the MonthRange function also with positive values. You will then need to query twice. One for all dates before and one for the dates after the current month.

It is possible to extend the class SysQueryRangeUtil (development effort) and create your own NotCurrentMonth method that can be used in case you need to have the result using one inquiry.
Community member 3 on at

Like (0)

Report

Advanced filter - does not occur in current month

Hi Andre,

Thank you for the response, this works but only provides me with accounts that have this date field populated with a date before the current month, and does not give me accounts that have this field populated with a date after the current month.

Is there any way to achieve this? I want accounts with dates populated before and after the current month.

Thanks, Kyle
Kevin Xia Microsoft Employee on at

Like (0)

Report

Advanced filter - does not occur in current month

Hi,

You can refer to this blog: dynamics-tips.com. The advanced filter functionality allows users to join multiple related tables together that are not natively accessible from the standard form. The user can also use advanced syntax and combine different criteria across multiple fields. In this article you will learn how to use the advanced filter functionality in F&O and some tips and tricks to help you avoid some headaches.

Best regards,

Kevin
Suggested answer

André Arnaud de Cal... 290,900 Super User 2024 Season 2 on at

Like (1)

Report

Advanced filter - does not occur in current month

Hi Kyle,

You can use an advanced date filter here. Try e.g. (MonthRange(-999,-1)).

See also the documentation: Advanced filtering and query syntax - Finance & Operations | Dynamics 365 | Microsoft Learn

Advanced filter - does not occur in current month

Helpful resources

News and Announcements

Quick Links

November Spotlight Star - Khushbu Rajvi

Forum Structure Changes Complete!

Dynamics 365 Community Platform update – Oct 28

Leaderboard

Featured topics

Product updates

Advanced filter - does not occur in current month

Helpful resources

News and Announcements

Quick Links

November Spotlight Star - Khushbu Rajvi

Forum Structure Changes Complete!

Dynamics 365 Community Platform update – Oct 28

Subscribe to

Leaderboard

Featured topics

Product updates