Tuesday, December 11, 2012 1:35 PM
Can anyone get me below query converted into QueryExpression or fetch XML?
select su.systemuserid, max(su.firstname), max(su.lastname), max(r.name) from FilteredSystemUserRoles ur join filteredsystemuser su on ur.systemuserid = su.systemuserid join filteredrole r on ur.roleid = r.roleid and r.name = 'Salesperson' where (select count(roleid) from FilteredSystemUserRoles ur1 where ur1.systemuserid=ur.systemuserid) = 1 group by su.SystemUserId having count(ur.roleid)=1Your help would be greatly appreciated.
(Fast Track Software Services)
Tuesday, December 11, 2012 3:37 PM
I think this would not be supported in FetchXML/QueryExpression - at least I'm not aware of how to implement filtering on the aggregates. One workaround might be to extend System User entity with an additional field where you would maintain the number of roles in which that user is involved (probably through a plugin). If you had such field, you would not need to involve aggregates.
Here is a link to a similar discussion:
And here is another link: http://social.microsoft.com/Forums/en/crmdevelopment/thread/2586e1a6-7e01-434e-bb61-9b3b21da748b
Alex Shlega, GotchaHunter.Net