web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Accounts with no invoi...
Microsoft Dynamics CRM (Archived)

Accounts with no invoices last x months (Advanced Find)

(0) ShareShare
ReportReport
Posted on by KING901 100

Hello all,

 I have been using the forums for answers for a while however this is one that has stumped me and I don't seem to be able to find an answer.

I would like a list of Accounts which have NOT had an invoice in the last 3 months. I don't seem to be able to work out the criteria required. I can get Accounts where they have invoices over 3 months old, but then I want to exclude any of those who have invoiced in the last 3 months. This seems impossible. I would really like this a an advanced find option as it will be really useful for our sales people. I can easily do it in SQL however it would be far better as an advanced find.

 Any ideas would be greatly appreciated. Did I mention that my boss wants the data today!! Ahhh..

 Thanks in advance

 Dave.

*This post is locked for comments

I have the same question (0)
  • Yaniv Arditi Profile Picture
    Yaniv Arditi 3,990 on at

    Hi,

    Using the Advanced find can take you half the way to the solution:

    1. Create a query to return all Accounts which have related Invoices with creation on the last 3 months. Export the result to Excel as List1.
    2. Create a query to return all Accounts. Export the result to Excel as List2.
    3. Compare the two lists using Excel tools. The list of Accounts that appear in List2 but not in List1 are the Accounts with no Invoice in the last 3 months.

    I am quite sure the SQL query will take you less time...

    Yaniv Arditi

  • Neeraj.Sharma Profile Picture
    Neeraj.Sharma 1,587 on at

    have u tried invoice createdon attribute in advance find

  • KING901 Profile Picture
    KING901 100 on at

     

    This allows me to return a list of account who have invoices older than 3 months, but I then want to limit that result set by accounts with no invoices in the last 3 months.

    I am trying to get Accounts which have stopped using our services in the last 3 months, but had used recently.

  • paulmcquillancrm Profile Picture
    paulmcquillancrm 40 on at

    Hi there,

    Microsoft CRM's advanced find does have a problem in that it cannot search for a negative - so whilst the tool can be used to undertake complex queries to say find Accounts in London that have been involved with a Campaign Activity in the last 6 months, it cannot do the reverse to find Accounts which have not been involved with a Campaign Activity in the last 6 months - or other similar 'presence of a negative' NOT style queries.

    However there is a work-around to this using the Marketing List functionality.

    1. Add all of the Accounts you wish to examine to a Marketing List - in this example, say all the Accounts who have had an Invoice in the last 12 months.

    2. Open this Marketing List and browse to the 'Marketing List Members' area.  Here there is a 'Manage Members' button which opens a sub-window offering 4 different options for adding or removing records from the List.

    3. One of these four options is 'Use Advanced Find to Remove Members' - you can then use this option to build an Advanced Find query which removes all the Accounts who have an Invoice in the last 3 months.

    4. This should leave you with the Marketing List now containing all the Accounts who have not been sent an Invoice in the last 3 months - as we started with a list of all the Accounts and then removed anyone who did not fit our criteria.  Effectively building a 'NOT' clause query by the back door as it were.

    5. We can now report on the Marketing List through either Excel or inside MSCRM in the Advanced Find to market to the recently lapsed accounts.

    Hope that helps.

    Kind Regards, Paul.

     

  • BarryCosme Profile Picture
    BarryCosme on at

    While this is an old post, it still expresses an existing problem with CRM 2011 to which I found a workaround.  To accomplish the equivalent to a "not-in" advanced find, I suggest the following.

    1. This technique uses the MS Dynamics CRM 2011 - Bulk Workflow Execution Tool that can be obtained at the following link http://crm.fullscope.com/ms-dynamics-crm-2011-bulk-workflow-execution-tool/ 

    2. Define the group in which you are interested as narrowly as possible to reduce processing time.  In my case it was a certain type of active accounts where the territory was not assigned to a particular team.  

    3. Set up a special Yes/No field in the accounts entity, in my case I called it TestPositive.

    4. Build and save an advanced find to return the group previously defined where the Yes/No field is not already set to N (meaning it is currently set to to Yes or has no data).

    5. Build and activate a workflow to set the Yes/No field to No.

    6. Run this in the Bulk Workflow Tool for the previously saved advanced.  After this is run the Yes/No field will be set to No for the entire group.

    7. Build and save a second advanced find to return the reverse of the "not-in" search you would really like to perform.  In my case I wanted accounts, in the previously defined group, that had not ordered in the last six months so this advanced find was for accounts in this group that DID have an order in the last six months.

    8. Build and activate a workflow to set the Yes/No field to Yes.

    9. Run the Yes workflow in the Bulk Workflow Tool using the second advanced find.  This sets the field to Yes for the members of the group that did have orders.

    10. This leaves the remainder of the defined group with a value of No.  Build and save an advanced find for the defined group where the value of the Yes/No filed is No.  This is the not-in group.  In my case it was the accounts in the defined group that did NOT have an order in the last six months.

    11. Using this final advanced find, report on or process the not-in group to suit your needs.

  • Suggested answer
    Neil Parkhurst Profile Picture
    Neil Parkhurst 10,727 User Group Leader on at

    Out of the box Dynamics 365 does not include a negative query option in advanced find. So anyone who has no invoices will not show in your query.

    You could like looking at the not-in capability introduced by this solution.

    www.cobalt.net/building-not-in-queries-using-dynamics-crm-advanced-find

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans