Skip to main content
Microsoft Dynamics AX forum
Suggested answer

Advanced filter - does not occur in current month

editSubscribe (0) ShareShare
ReportReport
Posted on by 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
Categories:
Attachments
  • 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
    Andre Arnaud de Calavon Profile Picture
    Andre Arnaud de Cal... 283,166 Super User 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.
  • 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.comThe 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
    Andre Arnaud de Calavon Profile Picture
    Andre Arnaud de Cal... 283,166 Super User 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)). 
     

Helpful resources

Quick Links

New Blog Features Released!

Check out the new community blog features for viewers and authors…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,166 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,703 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Featured topics

Product updates

Dynamics 365 release plans