Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics CRM forum
Suggested answer

Fetchxml to find out Contacts not associated to any Opportunity

Posted on by 28,958 Super User

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.

  • Suggested answer
    Bipin D365 Profile Picture
    Bipin D365 28,958 Super User on at
    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 Profile Picture
    Abdul Wahab 12,068 Super User on at
    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 Profile Picture
    Abdul Wahab 12,068 Super User on at
    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 Profile Picture
    Abdul Wahab 12,068 Super User on at
    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 Profile Picture
    Bipin D365 28,958 Super User on at
    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 Profile Picture
    Abdul Wahab 12,068 Super User on at
    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 Profile Picture
    Bipin D365 28,958 Super User on at
    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 Profile Picture
    Abdul Wahab 12,068 Super User on at
    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

    pastedimage1666956989904v1.pngpastedimage1666957024062v2.png

    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 Profile Picture
    Abdul Wahab 12,068 Super User on at
    RE: Fetchxml to find out Contacts not associated to any Opportunity

    Hi Bipin Kumar

    The easiest way to achieve the requirement is

    1. List  contacts (filter as much as you can here)
    2. Run the query. Do not forget to add top1pastedimage1666955455319v2.png
      1. When result = 0
        1. Run another query. Do not forget to add top1pastedimage1666955600173v3.png
          1. When result > 0
            1. 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/.../

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans
Liquid error: parsing "/blogs/post/?postid=%27nvOpzp;%20AND%201=1%20OR%20(%3C%27%22%3EiKO))," - Too many )'s.