6. juni 2008 22:08
Our client is leaning heavily on sharing accounts and they would like for these shared accounts to show up along side the owned accounts in the 'My Active Accounts" view.
Here is a sample SQL query that returns the data we need in the view (just going against the base MSCRM tables in this example):
select* from accountbase
whereowninguser = [current user id]
(select objectid from principalobjectaccess where principalid = [current user id])
Thus far I have come up against what appears to be limitations of FetchXml? No subqueries or 'OR' statement for multiple link entities?
Here is some FetchXml that gets all of the shared accounts successfully:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<condition attribute="statecode" operator="eq" value="0" />
<link-entity name="principalobjectaccess" from="objectid" to="accountid" link-type="inner">
<condition attribute="accessrightsmask" operator="gt" value="1"/><!-- Greater than 'Read' rights-->
<condition attribute="principalid" operator="eq-userid"/>
Does anyone know of a way to get both shared and owned entites in the same view? We are willing to entertain any reasonable level of customization (including unsupported) to make this happen.
7. juni 2008 06:06Redaktør
As you can get what you want with FetchXml, and views are based on FetchXml, I'd start by trying to modify the FetchXml directly. You can do this is one of two ways:
Use the CRM web services to retrieve and update the savedquery entity that is the My Active Accounts view
(Unsupported) Directly update the FetchXml column in the savedquerybase table
I'd recommend creating a new public account vew first, and testing it out on that, rather than risk breaking the built in view
9. juni 2008 19:03
Hi David - thanks for the response.
Regarding your comment '...you can get what you want with FetchXml...' -- this is really the crux of the biscuit -- I cant see how to accomplish this using FetchXml.
I have created a new view for dev and I am able to modify/update the FetchXml, but have so far I have'nt been able to come up with a query that provides the following:
select all accounts where
the current user owns the account
- OR -
the account has been shared with current user
FetchXml appears to treat the OR clause as an AND clause?
Can you (or anyone) provide an example of FetchXml (or an alternative approach) that can accomplish this?
11. juni 2008 17:14Redaktør
My apologies, I didn't read your first post in sufficient detail to realise that the fetch query didn't cover all you wanted. I can't think of a way to get one FetchXml query to do what you want.
Alternatively, I can think of possible avenues to investigate:
Create a many-many relationship between the account and systemuser entity. Use plugins on the Assign, GrantAccess and RevokeAccess messages to maintain data that reflects who owns or is shared a given account. Build your advanced find off this relationship
Write a plugin on the Execute message (which is actually the Fetch method). If the fetch is for the My Active Accounts view, run an additional fetch to get the shared accounts and merge these results with the original results in the OutputParameters property bag
Write a Reporting Services report to get the data you want, and display this in CRM
11. juni 2008 17:25
Hi David - thanks for following up.
The suggestions you made look viable - particulalry #2. I'll give it a go and see what I come up with!
13. september 2008 11:51Hi David,
This is a nice post, I think you can help me in proper way to find out solution of my task. I need to find out email address of the shared member i.e. suppose i have an opporunity and when i shared this opportunity to three users say A,B and C, once this opportuntiy is shared using GrandAccess message i need to find out Email address of A,B and C. As per your above description i guess you have proper knowledge on this. If you have any sample code please forward it.