locked
Problem with QueryExpression crm 2011 RRS feed

  • Question

  • Hi,

    I have this QueryExpression to get all records where custom attribute 'attr' is null:

                QueryExpression qry = new QueryExpression("opportunity");
                qry.ColumnSet = new ColumnSet("opportunityid");
                qry.Criteria = new FilterExpression();
                qry.Criteria.AddCondition("attr", ConditionOperator.Null);
    
    EntityCollection ents = service.RetrieveMultiple(qry);

    but unfortunately this query doesn't return any record. Why ? What's wrong ?

    attr is a DateTime field.


    • Edited by rogus1 Friday, January 10, 2014 10:13 AM
    Friday, January 10, 2014 10:08 AM

All replies

  • Hi,
    of course "attr" is not a valid custom attribute name, I guess your attribute real name is new_attr or something similar (I mean with the default or solution prefix)

    The code looks ok, did you try to do an advanced find inside CRM with the same criteria? It returns some values? If it returns please check the fetchxml generated or post here as well.


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

    Friday, January 10, 2014 10:17 AM
  • In the columnset you have mentioned only one column and that too opportunityid. Are you retrieving only Opportunityid?

    var processQuery = new QueryExpression
                    {
                        EntityName = "opportunity",
                        ColumnSet = new ColumnSet(columns),

                        Criteria =
                        {
                            FilterOperator = LogicalOperator.And,
                            Conditions =
                            {
                                new ConditionExpression
                                {
                                    AttributeName = "type",                   
                                    Operator = ConditionOperator.Equals,
                                    Values = {null}
                                }
                            }
                        }
                    };

    try this.

    Friday, January 10, 2014 10:19 AM
  • In the columnset you have mentioned only one column and that too opportunityid. Are you retrieving only Opportunityid?

    var processQuery = new QueryExpression
                    {
                        EntityName = "opportunity",
                        ColumnSet = new ColumnSet(columns),

                        Criteria =
                        {
                            FilterOperator = LogicalOperator.And,
                            Conditions =
                            {
                                new ConditionExpression
                                {
                                    AttributeName = "type",                   
                                    Operator = ConditionOperator.Null
                                }
                            }
                        }
                    };

    try this.

    Shantu, with little modification your code works fine.

    Guido, thanks for the hint with download fetchXML:) this works, too!

    Thanks guys!:)

    But why my code doesn't work ?

    I have one more question - how to add one more condition with OR FilterOperator ? (QueryExpression or FetchXML)

    Condition must be something like this = "my_attr" < DateTime.Now

    This is my fetch - 

                string fetchXMl = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                                      <entity name='opportunity'>
                                        <attribute name='name' />
                                        <attribute name='customerid' />
                                        <attribute name='estimatedvalue' />
                                        <attribute name='statuscode' />
                                        <attribute name='opportunityid' />
                                        <order attribute='name' descending='false' />
                                        <filter type='and'>
                                          <condition attribute='my_attr' operator='null' />
                                          <condition attribute='statuscode' operator='eq' value='1' />
                                          <condition attribute='statecode' operator='eq' value='0' />
                                        </filter>
                                      </entity>
                                    </fetch>";


    • Edited by rogus1 Friday, January 10, 2014 11:33 AM
    Friday, January 10, 2014 11:32 AM
  • Rogus,

    You can use QueryExpression, FilterExpression and COnditionExpression in conjunction.

    You can create three instances of conditionexpression(referring to your fetch).

    Instantiate Filter expression and add your conditions to it.

    Add the filter,columnset to your queryexpression as criteria and columnset respectively.

    Friday, January 10, 2014 11:41 AM
  • yes, I know, but how ?:)

    can you show me some sample code ?

    Friday, January 10, 2014 11:53 AM
  • Criteria = new FilterExpression
    {
     
    FilterOperator = LogicalOperator.Or,
     
    Filters =
     
    {
       
    new FilterExpression
       
    {
         
    FilterOperator = LogicalOperator.And,
         
    Conditions =
         
    {
           
    new ConditionExpression("field1", ConditionOperator.NotNull),
           
    new ConditionExpression("field2", ConditionOperator.NotNull)
         
    }
       
    },
       
    new FilterExpression
       
    {
         
    FilterOperator = LogicalOperator.And,
         
    Conditions =
         
    {
           
    new ConditionExpression("field3", ConditionOperator.NotNull),
           
    new ConditionExpression("field4", ConditionOperator.NotNull)
         
    }
       
    }
     
    }
    }
    or

    FilterExpression filter = new FilterExpression(LogicalOperator.Or);

    FilterExpression filter1 = new FilterExpression(LogicalOperator.And);
    filter1
    .Conditions.Add(new ConditionExpression("A_LogicalName", ConditionOperator.Equal, id1));
    filter1
    .Conditions.Add(new ConditionExpression("B_LogicalName", ConditionOperator.Equal, id2));

    FilterExpression filter2 = new FilterExpression(LogicalOperator.And);
    filter2
    .Conditions.Add(new ConditionExpression("B_LogicalName", ConditionOperator.Equal, id2));
    filter2
    .Conditions.Add(new ConditionExpression("C_LogicalName", ConditionOperator.Equal, id3));

    filter
    .AddFilter(filter1);
    filter
    .AddFilter(filter2);

    query
    .Criteria = filter;

    somthing like this.....

    Friday, January 10, 2014 12:21 PM