locked
Query Expression not returing value from Systemuser table RRS feed

  • Question

  • HI All,

    I tried to fetch user from Systemuserbase using QueryExpression using below Query but its not Retriving values

    QueryExpression query = new QueryExpression("systemuser");
    query.ColumnSet = new ColumnSet(true);
    query.Criteria.AddCondition(new ConditionExpression("domainname", ConditionOperator.Equal, proposal.ModifiedBy));
    var coltest = orgService.RetrieveMultiple(query);
     for (int i = 0; i < coltest.Entities.Count; i++)
    {
     systemuserid = new Guid(coltest[i].Attributes["systemuserid"].ToString());
    }

    proposal.ModifiedBy may change based on user the values will be ASKA\ADMIN and ASKA\USER (the user will be passed from share point)


    • Edited by JERRY_TP Wednesday, April 29, 2015 5:19 AM
    Wednesday, April 29, 2015 5:18 AM

All replies

  • if the query doesn't return valuesmeans that there are no records where "domainname" equals the value of proposal.ModifiedBy value.

    Because domainname is a string field, proposal.ModifiedBy is a string as well or it contains the GUID of the user?


    My blog: www.crmanswers.net - Rockstar 365 Profile

    Wednesday, April 29, 2015 6:37 AM
  • Hi,

    As Guido says, if you get no answers there are no entries where the data match. What happens if you run the same query using Advanced Find from the GUI, and what sort of data do you enter with the proposal.ModifiedBy?

    cheers!


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, April 29, 2015 7:06 AM
  • Hi,

    am Passing the Admin user for the CRM system but that value also not retiving but if i pass value like ASKA\\ADMIN i can get the values. please guide me how to chagne ASKA\ADMIN to ASKA\\ADMIN

    Wednesday, April 29, 2015 7:44 AM
  • Hi,

    What happens if you put the proposal.ModifiedBy into a string of its own before using it in the fetch?

    I.e.

    string userName = proposal.ModifiedBy;QueryExpression query = new QueryExpression("systemuser");
    query.ColumnSet = new ColumnSet(true);
    query.Criteria.AddCondition(new ConditionExpression("domainname", ConditionOperator.Equal, userName));
    var coltest = orgService.RetrieveMultiple(query);
     for (int i = 0; i < coltest.Entities.Count; i++)
    {
     systemuserid = new Guid(coltest[i].Attributes["systemuserid"].ToString());
    }
    

    There might be som issues with how the string is formatted from wherever you're getting it. If that doesn't work you can do a string.replace and add an extra backslash if needed.

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, April 29, 2015 7:59 AM
  • Hi Rickard,

    Its not working :( have cross checked it

    Wednesday, April 29, 2015 10:39 AM
  • What are you getting from the proposal.ModifiedBy? That is the issue if you can manually input a username and it works but when you are using the variable it's not returning anything.

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, April 29, 2015 10:45 AM
  • The ModifiedBy attribute in Crm is a guid, not a string value, so won't match on the domainname. The easiest fix is to put the condition on the systemuserid column, not domainname:

    query.Criteria.AddCondition(new ConditionExpression("systemuserid", ConditionOperator.Equal, proposal.ModifiedBy));

    Or, given that the systemuserid is unique, you could use Retrieve, rather than RetrieveMultiple


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Wednesday, April 29, 2015 11:25 AM
    Moderator
  • Is the guid for modifiedby also true for Sharepoint David?

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, April 29, 2015 11:27 AM
  • Another option would be to change your query to use FetchXML.  I have a post that describes how to use FetchXML to build a QueryExpression.  Two nice things about using FetchXML: 1. Advanced Find will build your query for you and 2. you can use the XRM Toolbox to test your FetchXML.
    Wednesday, April 29, 2015 1:04 PM