Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Advanced filter - does...
Finance | Project Operations, Human Resources, ...
Suggested answer

Advanced filter - does not occur in current month

(0) ShareShare
ReportReport
Posted on by Community member 3
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
  • Suggested answer
    ColbyGallagher Profile Picture
    ColbyGallagher 3,664 on at
    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 Calavon Profile Picture
    André Arnaud de Cal... 291,280 Super User 2024 Season 2 on at
    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 Profile Picture
    Community member 3 on at
    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 Profile Picture
    Kevin Xia Microsoft Employee on at
    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 Calavon Profile Picture
    André Arnaud de Cal... 291,280 Super User 2024 Season 2 on at
    Advanced filter - does not occur in current month
    Hi Kyle,
     
    You can use an advanced date filter here. Try e.g. (MonthRange(-999,-1)). 
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans