locked
query expression to search by id of lookup field RRS feed

  • Question

  • How can |I construct a query in QueryExpression which will search by the id of a lookup field?

    I have a scenario in which I have an incident with a known ID. I have a custom entity that is linked to the incident. I want to fetch all the related custom entities;

    I have tried variations on 

                EntityReference incident = new EntityReference("incident", caseid);
    
                try
                {
                    utilities.log("List case histories");
    
                    QueryExpression CasesQuery = new QueryExpression
                    {
                        EntityName = "qubic_servicemodule_casehistory",
                        ColumnSet = new ColumnSet(true),
                        Criteria = new FilterExpression
                        {
                            Conditions = 
                            {
                                new ConditionExpression 
                                {
    
                                    AttributeName = "qubic_servicemodule_case",
                                    Operator = ConditionOperator.Equal,
                                    Values = { incident }
                                }
                            }
                        }
                    };

    and 

                try
                {
                    utilities.log("List case histories");
    
                    QueryExpression CasesQuery = new QueryExpression
                    {
                        EntityName = "qubic_servicemodule_casehistory",
                        ColumnSet = new ColumnSet(true),
                        Criteria = new FilterExpression
                        {
                            Conditions = 
                            {
                                new ConditionExpression 
                                {
                                    AttributeName = "qubic_servicemodule_caseid",
                                    Operator = ConditionOperator.Equal,
                                    Values = { caseid }
                                }
                            }
                        }
                    };

    but with no success. Fetching all the attributes for case histories shows me that I have an attribute "qubic_servicemodule_case", which in turn has an attribute which is the id for which I am searching. But how do I write a query to test that? qubic_servicemodule_case, qubic_servicemodule_case.Id and qubic_servicemodule_caseid all fail. Comparing to an entity itself fails so what should I be trying here?



    • Edited by J N Brand Thursday, June 26, 2014 11:24 AM
    Thursday, June 26, 2014 11:21 AM

All replies

  • If the lookup field inside the custom entity is qubic_servicemodule_case you need to use it as AttributeName.

    the problem is with Values, you are putting the entityrefrence, but you need to put caseid variable


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

    Thursday, June 26, 2014 11:27 AM
  • Not sure what you mean by that. I am using caseid in the Values field and "qubic_servicemodule_case" as the attribute nameas one of the variants I have tried and in that case I get " Object reference is not set to an instance of an object". If I instantiate an entity or entity reference I get some cryptic error about deserialization
    • Edited by J N Brand Thursday, June 26, 2014 11:38 AM
    Thursday, June 26, 2014 11:30 AM
  • In your first example (and before your edit also in the second one) you are using incident.

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

    Thursday, June 26, 2014 11:36 AM
  • In your first example (and before your edit also in the second one) you are using incident.

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

    The second example had a typo where I was cut an pasting (basically the real code has a number variant of that query all bar one of which was commented out)

    When I run the code with case id it tells me that I am not referencing an object if I use incident, either as an entity reference or entity, I get a deserialization error.

    This is the full code of the section that is giving me hassle at the moment ...

            public void ReadCaseHistoryList(Guid caseid)
            {
                //EntityReference incident = new EntityReference("incident", caseid);
                Entity incident = new Entity("incident") { Id = caseid };
                // Need to open a connection to CRM and create an Incoming Support Email Object
                try
                {
                    utilities.log("List case histories");
    
                    QueryExpression CasesQuery = new QueryExpression
                    {
                        EntityName = "qubic_servicemodule_casehistory",
                        ColumnSet = new ColumnSet(true),
                        Criteria = new FilterExpression
                        {
                            Conditions = 
                            {
                                new ConditionExpression 
                                {
                                    //AttributeName = "qubic_name",
                                    //Operator = ConditionOperator.NotEqual,
                                    //Values = { "" }
                                    AttributeName = "qubic_servicemodule_case",
                                    Operator = ConditionOperator.Equal,
                                    Values = { incident }
                                    //AttributeName = "qubic_servicemodule_case",
                                    //Operator = ConditionOperator.Equal,
                                    //Values = { caseid }
                                }
                            }
                        }
                    };
    
                    DataCollection<Entity> cases = service.RetrieveMultiple(
                        CasesQuery).Entities;
    

    • Edited by J N Brand Thursday, June 26, 2014 11:44 AM
    Thursday, June 26, 2014 11:41 AM
  • you are still using incident, try with this code:

    QueryExpression CasesQuery = new QueryExpression("qubic_servicemodule_casehistory");
    CasesQuery.ColumnSet = new ColumnSet(true);
    FilterExpression criteria = new FilterExpression();
    criteria.AddCondition(new ConditionExpression("qubic_servicemodule_case", ConditionOperator.Equal, caseid));
    CasesQuery.Criteria = criteria;


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

    Thursday, June 26, 2014 11:54 AM
  • That's the third example in the set I currently have in that code and it returns "object reference is not set to an instance of an object"
    Thursday, June 26, 2014 11:58 AM