Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / CRM view of Opportunit...
Microsoft Dynamics CRM (Archived)

CRM view of Opportunity records with Notes attachment

(0) ShareShare
ReportReport
Posted on by Varun Singh 941

Hi All,

I need to create a system view in CRM 2013 for Opportunity records, however the condition for the view is that it should only contain those Opportunity records which do not have Notes attachment. 

Earlier I was thinking it's very easy to create this view but till now I am not able to create right filter. Please help me to create this view. 

*This post is locked for comments

  • Varun Singh Profile Picture
    Varun Singh 941 on at
    RE: CRM view of Opportunity records with Notes attachment

    Hi Maniraj,

    Thank you for your solution. This is working for me.

  • Suggested answer
    ManirajKV Profile Picture
    ManirajKV 1,477 on at
    RE: CRM view of Opportunity records with Notes attachment

    Hi Varun,

    Please change isdocument value as 1

  • Varun Singh Profile Picture
    Varun Singh 941 on at
    RE: CRM view of Opportunity records with Notes attachment

    Hi Maniraj,

    Once again thank you. I am able to create View from console application. However the result is same. It's giving Opportunity records which have attachment also. I need only those Opportunity records which do not have any attachment at all.

    Below is the fetchXML variable which I am using.


    var fetchXML = "<fetch distinct=\"true\" mapping=\"logical\" output-format=\"xml-platform\" version=\"1.0\" >";
    fetchXML += "<entity name=\"opportunity\">";
    fetchXML += " <attribute name=\"name\" />";
    fetchXML += " <filter type=\"and\" >";
    fetchXML += " <filter type=\"or\" >";
    fetchXML += " <condition attribute=\"new_state\" operator=\"eq\" value=\"100000006\" /> ";
    fetchXML += " <condition attribute=\"new_state\" operator=\"eq\" value=\"100000003\" /> ";
    fetchXML += " </filter>";
    fetchXML += " <condition attribute=\"new_closeddate\" operator=\"this-year\" />";
    fetchXML += " </filter>";
    fetchXML += " <link-entity name=\"annotation\" from=\"objectid\" to=\"opportunityid\" alias=\"an\" link-type=\"outer\" >";
    fetchXML += " <attribute name=\"objectid\" />";
    fetchXML += " <filter type=\"and\" >";
    fetchXML += " <condition attribute=\"isdocument\" value=\"0\" operator=\"eq\" />";
    fetchXML += " </filter>";
    fetchXML += " </link-entity>";
    fetchXML += " <filter type=\"and\" >";
    fetchXML += " <condition entityname=\"an\" attribute=\"objectid\" operator=\"null\" />";
    fetchXML += " </filter>";
    fetchXML += " </entity>";
    fetchXML += "</fetch>";

  • Verified answer
    ManirajKV Profile Picture
    ManirajKV 1,477 on at
    RE: CRM view of Opportunity records with Notes attachment

    Hi, 
    1. Create one console application
    2. replace the below code in Program.cs file
    3. Add reference of Microsoft.Xrm.Sdk.dll, Microsoft.Xrm.Client.dll

    ***************************************************
    using Microsoft.Xrm.Client;
    using Microsoft.Xrm.Client.Services;
    using Microsoft.Xrm.Sdk;
    using System;
    namespace CRMCustomDemo
    {
    class Program
    {
    static void Main(string[] args)
    {
    try
    {
    // for crm online instance
    CrmConnection connection = CrmConnection.Parse("URL=https://****.crm5.dynamics.com/; username=***@***.com; password=****");
    OrganizationService _service = new OrganizationService(connection);
    var fetchXML = "<fetch distinct=\"true\" mapping=\"logical\" output-format=\"xml-platform\" version=\"1.0\" >";
    fetchXML += "<entity name=\"opportunity\">";
    fetchXML += "        <attribute name=\"name\" />";
    fetchXML += "        <link-entity name=\"annotation\" from=\"objectid\" to=\"opportunityid\" alias=\"an\" link-type=\"outer\" >";
    fetchXML += "            <attribute name=\"objectid\" />";
    fetchXML += "            <filter type=\"and\" >";
    fetchXML += "                <condition attribute=\"isdocument\" value=\"1\" operator=\"eq\" />";
    fetchXML += "            </filter>";
    fetchXML += "        </link-entity>";
    fetchXML += "        <filter type=\"and\" >";
    fetchXML += "            <condition entityname=\"an\" attribute=\"objectid\" operator=\"null\" />";
    fetchXML += "        </filter>";
    fetchXML += "    </entity>";
    fetchXML += "</fetch>";
    Entity entity = new Entity("savedquery");
    entity["name"] = "NoAttachmentsView";
    entity["fetchxml"] = fetchXML;
    entity["returnedtypecode"] = "opportunity";
    entity["querytype"] = 0;
    _service.Create(entity);
    }
    catch(Exception ex)
    {
    Console.WriteLine(ex.Message);
    }
    }
    }
    }
    If you couldn't get it done, please drop a mail to me @ manirajcareer@live.com will share you the solution file.
  • Varun Singh Profile Picture
    Varun Singh 941 on at
    RE: CRM view of Opportunity records with Notes attachment

    Hi Maniraj,

    Thanks for your help. Would you please help, how to make this view (For example from Plugin or somewhere else. Please little bit elaborate.

  • Suggested answer
    ManirajKV Profile Picture
    ManirajKV 1,477 on at
    RE: CRM view of Opportunity records with Notes attachment

    Yes, It will not work for one scenario. (Its not a bug in CRM).

    Scenario: "Opportunity X" record has more than one notes (one note with attachment and one note without attachment)

    Notes entity will associate two records with "Opportunity X". So only above fetch is not working on this scenario.

    You can achieve your requirement using below fetch:

    <fetch distinct="true" mapping="logical" output-format="xml-platform" version="1.0" >
    <entity name="opportunity">
        <attribute name="name" />
        <link-entity name="annotation" from="objectid" to="opportunityid" alias="an" link-type="outer" >
            <attribute name="objectid" />
            <filter type="and" >
                <condition attribute="isdocument" value="1" operator="eq" />
            </filter>
        </link-entity>
        <filter type="and" >
            <condition entityname="an" attribute="objectid" operator="null" />
        </filter>
    </entity>
</fetch>

    Through savequeryentity, you can create view in CRM. Its a one time activity.
    Please follow the below to do the same.

    8664.Untitled.png

  • Suggested answer
    Dhaval mistry Profile Picture
    Dhaval mistry 810 on at
    RE: CRM view of Opportunity records with Notes attachment

    Hi

    What I understand your question is: you need to list out oppotunity which doesn't have Note.

    using advance find of CRM Out of box, you can't implement left join on fatch XML

    But you can do it using fatchXML using C# code (custom web resource) or SSRS report.

    Left outer join 

    https://msdn.microsoft.com/en-us/library/dn531006.aspx?f=255&MSPPError=-2147217396

    Wish it helps!

  • Varun Singh Profile Picture
    Varun Singh 941 on at
    RE: CRM view of Opportunity records with Notes attachment

    Hi Aileen,

    I have tried this earlier many times but when I am putting isdocument equal to NO, the result will contain attached document records also. I think it's bug in CRM (We have migrated our CRM from 2011 to CRM 2015)

  • Varun Singh Profile Picture
    Varun Singh 941 on at
    RE: CRM view of Opportunity records with Notes attachment

    Hi Maniraj,

    I have tried this condition earlier also. There is some bug in CRM. If I put above filter on Notes related entity then the result of view is fetching those results which have attachment also.

  • Aileen Gusni Profile Picture
    Aileen Gusni 44,522 on at
    RE: CRM view of Opportunity records with Notes attachment

    Hi Varun

    If your query purpose is to get the list of opportunities with Notes but the note is not containing attachment or document then you can use the adv find by using Opportunity then go to the related entity find Notes and use the isdocument attribute to No

    But if your purpose to query Opportunity without any notes attached then is not possible using crm out of the box query to do query with "not in " clause.

    Hope this helps

    Thanks

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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,113 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,918 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans