Advanced Find Query

(0) Share

Report

Posted on by Gowtham89

Dear Team,

I am after a help, it might sound bit strange but I am not able to figure it out. I am looking to perform Advanced Find search for Accounts that does not have any Open opportunities.

Even though it sounds simple, I am not getting correct results when I try to search the accounts via Advanced Find in CRM. Because each account has multiple opportunities (some of them are Won, and some are in Open status) so I am getting the results even though there are open opportunities for the account. How can I search for an account that does not have any Open opportunity.

Thanks

Gowtham

All responses (10)

Answers (1)

Gowtham89 on at

Like (1)

Report

RE: Advanced Find Query

Hi Joergen,

Fantastic, yes this will help satisfying my requirement and I can build Power Automate flow based on the conditions and create a new Lead or Opportunity accordingly.

Thanks again for your help!!

Thanks,

Gowtham
Verified answer

Joergen 455 on at

Like (1)

Report
RE: Advanced Find Query

Hi Gowtham,

I cannot provide you an fetchxml only solution as I am afraid this will not be possible because you cannot start with an OR condition and inside an OR condition you cannot use related tables.

But what will work is the following:

Create a new Rollup Field type Whole number on the account table

and add the following (I will name my rollup field oo):

Rollup field name: oo

Source: Account

Related Entity: Opportunities (Account)

Filters: If Status equals Open

Aggregation: Count of Opportunity

The rollup field can be recalculated manually for a test, or it will be recalculated every 12 hours automatically (If this is not enough, you can always use a plugin to recalculate)

Then create your view:

Add row with OO (Rollup field) equals 0

Initially after creating the rollup field you will still see all accounts, as the rollup field has not been recalculated or automatically calculated.

Once you clicked on the small calculator on the account form next to the rollup field , you can select recalculate and the value will show the number of open opportunities.
On the other hand it does not matter, if there is any opportunity at all related to this account, or if there are several lost or won opportunities.

Hope this helps to solve your topic.

If this is the answer you are looking for, please verify.

Best Joergen
Gowtham89 on at

Like (0)

Report

RE: Advanced Find Query

Hi Necdet,

Thanks for your response.

Yes I am using fetchxml to retrieve records in Power Automate but the fetchxml doesn't seem to support left outer join which will help in my scenario.

The suggested response will not work in my case. If I select Status equals 'Won' or 'Lost' that will display the accounts (Clients) records that has Won or Lost Leads, but eventhough there are open leads attached to it.

Its because the account has multiple leads (one of them is 'Won' and one of them is in 'Open' status)

My requirement is different, I have list of accounts and each account has many leads and opportunities mapped to it.

I am trying to retrieve the account that does have any open leads or open opportunities. So the above suggested case will not work in my scenario.

Is there any other suggestion please?

Thanks

Gowtham
Gowtham89 on at

Like (0)

Report

RE: Advanced Find Query

Hi Bipin,

Thanks for your response. I have gone through this article and it does not help in my scenario.

My requirement is bit different to the one highlighted in this article, I have list of accounts and each account has many leads and opportunities mapped to it.

I am trying to retrieve the account that does have any open leads or open opportunities. So the above suggested case will not work in my scenario.

Is there any other suggestion please?

Thanks

Gowtham
Gowtham89 on at

Like (0)

Report

RE: Advanced Find Query

Hi Yoshika,

Thanks for your response.

The suggested response will work if the account has no opportunities at all. My requirement is different, I have list of accounts and each account has many leads and opportunities mapped to it.

I am trying to retrieve the account that does have any open leads or open opportunities. So the above suggested case will not work in my scenario.

Is there any other suggestion please?

Thanks

Gowtham
Suggested answer

Yoshika Suzuki on at

Like (0)

Report

RE: Advanced Find Query

When I set Opportunity (Account) to "Does Not Contain Data", I was able to extract accounts that do not have Opportunity.
Suggested answer

necsa 3,455 on at

Like (0)

Report

RE: Advanced Find Query

Hi,

Did you tray to find out with " Status equal Won and Lost" condition. What is the result from this condition?

If you use Power Automate why don't use fetchXML on the Client records?
Suggested answer

Bipin D365 28,977 Moderator on at

Like (0)

Report

RE: Advanced Find Query

Hello,

Please look at below article which might help

www.c5insight.com/.../dynamics-365-view-accounts-with-no-activities-for-30-days.aspx

You will need to download fetchxml builder plugin in xrmtoolbox

Please mark my answer verified if this is helpful!

Regards,

Bipin Kumar

Follow my Blog: xrmdynamicscrm.wordpress.com/
Gowtham89 on at

Like (0)

Report

RE: Advanced Find Query

Hi Necdet,

Thanks for the response. Yes accounts is renamed as 'Client'.

Yeah it's easier if I find Client with Open opportunities but there is no way I can find the client with no open opportunities.

I am trying to run a Power Automate flow based on this condition that if the client does not have any open Opportunity then create one. But due to this limitation I am struggling to do so.

Thanks

Gowtham
necsa 3,455 on at

Like (0)

Report

RE: Advanced Find Query

Hi,

I try it and get only all open opportunities. Are the Clients a custom entity or renamed account?

Please refresh your knowledge about limitations of Advanced find take as a reference following link:

http://leontribe.blogspot.com/2013/04/three-limitations-of-using-advanced-find.html