{Dynamics CRM/ 365 + Views}–Create a view to show security roles assigned to user along with roles the user inherits from the team

The title might be confusing. So let’s straightway jump to the requirement here. Our client a role lookup on custom entity form and on click of the lookup, the user should be presented with a view with a view which shows the security role directly assigned to the user along with the security roles the user inherits from the team.

For e.g.

User A has security role – Role 1

User A is a member of the team which has security role – Role 2

So the view would show both the roles – Role 1 + Role 2

Very simple requirement right? However can you achieve this using OOB view? Well unfortunately the answer is BIG NO.

We went ahead with the design that when a user clicks the lookup we would call dynamically a fetchxml using the OOB custom view. Now the big task is creating the fetch xml of the custom view.

After much deliberation, I finally came up with the below Fetch XML which serves exactly the purpose. So let’s see our protagonist in the play.

 

var fetchString = string.Format(@"<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’>
                                            <entity name=’role’>
                                                <attribute name=’name’ />
                                                <attribute name=’businessunitid’ />
                                                <attribute name=’roleid’ />
                                                <order attribute=’name’ descending=’false’ />
                                                <link-entity name=’systemuserroles’ from=’roleid’ to=’roleid’ link-type=’outer’>
                                                    <link-entity name=’systemuser’ from=’systemuserid’ to=’systemuserid’ alias=’aj’ link-type=’outer’>
                                                        <attribute name=’systemuserid’ />
                                                    </link-entity>
                                                </link-entity>
                                                <link-entity name=’teamroles’ from=’roleid’ to=’roleid’ visible=’false’ intersect=’true’ link-type=’outer’>
                                                    <link-entity name=’teammembership’ from=’teamid’ to=’teamid’ visible=’false’ intersect=’true’ link-type=’outer’>
                                                        <link-entity name=’systemuser’ from=’systemuserid’ to=’systemuserid’ alias=’al’ link-type=’outer’ >
                                                            <attribute name=’systemuserid’ />
                                                        </link-entity>
                                                    </link-entity>
                                                </link-entity>
                                                    <filter type=’or’>
                       &#1
60;                                <condition entityname=’aj’ attribute=’systemuserid’ operator=’eq’ value='{0}’ />
                                                        <condition entityname=’al’ attribute=’systemuserid’ operator=’eq’ value='{0}’ />
                                                    </filter>
                                            </entity>
                                        </fetch>", userid);

I have highlighted the important part of the query. The first thing to notice here is the alias that I provided while fetching the roles from the systemuserroles table and the teamroles table.

The next part is using the alias and constructing the filter condition with the alias as the entity name. userid is the GUID of the user whose security roles needs to be determined.

 

Please note that this type of construct is actually possible from CRM 2013 and onwards. Although this works, unfortunately this construct is not supported through the UI.

Small trick but it can consume days if not know.

Hope this helps! Till you read one or more of my blog posts, happy CRM’ing