Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
> 
Query expression for below Query.

Frage Query expression for below Query.

  • Tuesday, December 11, 2012 1:35 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    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)=1
    Your help would be greatly appreciated.

    Regards,
    Dhaval Panchal
    Software Engineer
    (Fast Track Software Services)

     

All Replies

  • Tuesday, December 11, 2012 3:37 PM
     
     
    Sign In to Vote
    0

    Hi Dhaval,

      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:

      http://go4answers.webhost4life.com/Example/aggregate-inside-condition-fetchxml-27462.aspx

      And here is another link: http://social.microsoft.com/Forums/en/crmdevelopment/thread/2586e1a6-7e01-434e-bb61-9b3b21da748b

    Alex Shlega, GotchaHunter.Net