CRM view of Opportunity records with Notes attachment

(0) Share

Report

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

All responses (12)

Answers (1)

Varun Singh 941 on at

Like (1)

Report

RE: CRM view of Opportunity records with Notes attachment

Hi Maniraj,

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

ManirajKV 1,477 on at

Like (0)

Report

RE: CRM view of Opportunity records with Notes attachment

Hi Varun,

Please change isdocument value as 1
Varun Singh 941 on at

Like (0)

Report

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 1,477 on at

Like (0)

Report
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 941 on at

Like (0)

Report

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 1,477 on at

Like (0)

Report
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.
Suggested answer

Dhaval mistry 810 on at

Like (1)

Report

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 941 on at

Like (0)

Report

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 941 on at

Like (0)

Report

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 44,522 on at

Like (0)

Report

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