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
Hi Maniraj,
Thank you for your solution. This is working for me.
Hi Varun,
Please change isdocument value as 1
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>";
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
***************************************************
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.
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.
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!
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)
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.
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,113 Super User 2024 Season 2
Martin Dráb 229,918 Most Valuable Professional
nmaenpaa 101,156