Customer experience | Sales, Customer Insights,...

Fetchxml to find out Contacts not associated to any Opportunity

(0) Share

Report

Posted on by Bipin D365

28,981 Moderator

Hi All,

I need to construct fetchxml to find out the list of contacts which are not associated to any active opportunity and Last Updated Date field on opportunity is older than 1 year.

Contact can be associated to multiple opportunities.

Please help me construct the fetchxml which I can use in plugin to add contacts to marketing list.

I know, I can use fetchxml builder plugin in xrmtoolbox. I tried but couldn't able to design what I need so asking for help.

All responses (9)

Answers (0)

Suggested answer

Bipin D365 28,981 Moderator on at

Like (0)

Report

RE: Fetchxml to find out Contacts not associated to any Opportunity

Hi,

I am still thinking to go with sql instead of fetchxml.

Here's why-

Contact abc has 3 opportunity

Opportunity 123

Status open

Modified on 01/11/2022

Opportunity 456

Status open

Modified on 01/10/2022

Opportunity 678

Status closed

Modified on 01/10/2021

Contact xyz has 1 opportunity

Opportunity 001

Status open

Modified on 01/11/2021

Contact bob123 has 1 opportunity

Opportunity 001

Status closed

Modified on 01/11/2021

I should get contact xyz and bob123 in my result.

How to achieve this using fetchxml?
Suggested answer

Abdul Wahab 12,085 Moderator on at

Like (0)

Report

RE: Fetchxml to find out Contacts not associated to any Opportunity

Hi Bipin Kumar,

If I answer your question then please mark it as verified.

Let me know if I can provide you with more details.

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: www.linkedin.com/.../
Suggested answer

Abdul Wahab 12,085 Moderator on at

Like (0)

Report

RE: Fetchxml to find out Contacts not associated to any Opportunity

Hi Bipin Kumar,

If I answer your question then please mark it as verified.

Let me know if I can provide you with more details.

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: www.linkedin.com/.../
Suggested answer

Abdul Wahab 12,085 Moderator on at

Like (0)

Report

RE: Fetchxml to find out Contacts not associated to any Opportunity

Hi Bipin Kumar

My colleague, In the first post you are saying Opportunity is not equal to active... means not equal to open. Am I right? In your SQL query, I can see 'statecode' = 0. I am feeling a communication gap here. My query definitely gives an answer... Remove not equals to equal... You do not need OR... And is fine here. You can still compare results...

I can not see the logical difference in both queries.

If I answer your question then please mark it as verified.

Let me know if I can provide you with more details.

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: www.linkedin.com/.../
Suggested answer

Bipin D365 28,981 Moderator on at

Like (0)

Report

RE: Fetchxml to find out Contacts not associated to any Opportunity

Hi,

I don't think changing And to OR will work. I tried that and it is not giving me the correct results.

Do you think I should go for SQL query instead of fetchxml?

I am using on-premise version of CRM dynamics.

Select Distinct C.contactid from contact c inner join opportunity O on C.contactid=O.customerid where C. statecode=0 and O.statecode=0 And C.ownerid ='guid' and C.contactid NOT IN

(

Select C.contactid from contact c inner join opportunity O on C.contactid=O.customerid where C. statecode=0 and O.statecode=0 And C.ownerid ='guid' And DateDiff(days,O.modifiedon,GetDate())<=365

);

This query seems to be working but how do I convert this to fetchxml?

I tried sql4cds plugin in xrmtoolbox but no luck.
Suggested answer

Abdul Wahab 12,085 Moderator on at

Like (0)

Report

RE: Fetchxml to find out Contacts not associated to any Opportunity

Hi Bipin Kumar,

I am afraid about the understanding. Use OR instead of AND. It will resolve the problem

If I answer your question then please mark it as verified.

Let me know if I can provide you with more details.

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: www.linkedin.com/.../
Suggested answer

Bipin D365 28,981 Moderator on at

Like (0)

Report

RE: Fetchxml to find out Contacts not associated to any Opportunity

Hi Abdul,

Thanks for you reply.

Statecode ne 0 -- this will not work

Let's say I have contact ABC and it has below opportunity associated

Opportunity name - xyz

Statecode - Open

Last updated date - 1/2/2020

I don't think with your fetchxml I will have this contact in my result
Suggested answer

Abdul Wahab 12,085 Moderator on at

Like (0)

Report

RE: Fetchxml to find out Contacts not associated to any Opportunity

Hi Bipin Kumar,

One more quick approach comes to my mind. You can use the below-aggregated query

If I answer your question then please mark it as verified.

Let me know if I can provide you with more details.

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: www.linkedin.com/.../
Suggested answer

Abdul Wahab 12,085 Moderator on at

Like (0)

Report
RE: Fetchxml to find out Contacts not associated to any Opportunity

Hi Bipin Kumar

The easiest way to achieve the requirement is

List contacts (filter as much as you can here)

Run the query. Do not forget to add top1

When result = 0

Run another query. Do not forget to add top1

When result > 0

Here you can write your logic

If I answer your question then please mark it as verified.

Let me know if I can provide you with more details.

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: www.linkedin.com/.../