locked
Filter criteria for query expression using C# in MS Dynamics CRM 2011 RRS feed

  • Question

  • Hi..

    Good day. 

    I would like to know how will i add a filter criteria on the query expression i have created using c#. Below is my code with comment.

                        QueryExpression query = new QueryExpression("nexus_budgetnonbudgetedexpenses");
                        query.ColumnSet = new ColumnSet(new string[] { "nexus_paymentstatuscheckbox", "nexus_tpcamountallocation", "nexus_transportationamountallocation", "nexus_representationamountallocation", "nexus_adminexpenseamountallocation", "nexus_communicationamountallocation", "nexus_technicalsupportamountallocation", "nexus_trainingamountallocation", "nexus_freebiesamountallocation", "nexus_perdiemamountallocation", "nexus_deliveryamountallocation", "nexus_refundableamountallocation", "nexus_othersamountallocation" });

                        queryid.Criteria.AddCondition("nexus_paymentstatuscheckbox", ConditionOperator.Equal, false); //FIRST CONDITION
                        query.Criteria.AddCondition("nexus_accountbudgetexpenses", ConditionOperator.Equal, uRelatedAccountId); //SECOND CONDITION

                        query.AddOrder("nexus_tpcamountallocation", OrderType.Descending);
                        query.PageInfo.ReturnTotalRecordCount = true;

                        EntityCollection result = service.RetrieveMultiple(query);

    Now i want the conditions to be filtered with AND operator. Would like to know what code should i add.

    Thank you for any help on this.

    Regards,

    Honey

    Wednesday, November 6, 2013 3:25 AM

Answers

  • Hello,

    Try like as below :

    QueryExpression _Query = new QueryExpression
                    {
                        EntityName = "nexus_budgetnonbudgetedexpenses",
                        ColumnSet = new ColumnSet(true),
                        Criteria =
                        {
                            FilterOperator = LogicalOperator.And,
                            Conditions =
                            {
                                new ConditionExpression
                                {
                                    AttributeName="nexus_paymentstatuscheckbox",
                                    Operator=ConditionOperator.Equal,
                                    Values={false}
    
                                },
    
                                new ConditionExpression
                                {
                                    AttributeName="nexus_accountbudgetexpenses",
                                    Operator=ConditionOperator.Equal,
                                    Values={uRelatedAccountId}
    
                                }
                            }
                        }
    
                    };
    
    EntityCollection _Entities = service.RetrieveMultiple(_Query);
    
    if (_Entities.Entities.Count > 0)
    {
    }


    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !


    • Edited by _Vikram Wednesday, November 6, 2013 5:24 AM
    • Marked as answer by Honey Dulatre Wednesday, November 6, 2013 7:35 AM
    Wednesday, November 6, 2013 5:14 AM
  • Write as like below :

     ColumnSet = new ColumnSet("nexus_paymentstatuscheckbox", "nexus_tpcamountallocation", "nexus_transportationamountallocation", "nexus_representationamountallocation", "nexus_adminexpenseamountallocation", "nexus_communicationamountallocation", "nexus_technicalsupportamountallocation", "nexus_trainingamountallocation", "nexus_freebiesamountallocation", "nexus_perdiemamountallocation", "nexus_deliveryamountallocation", "nexus_refundableamountallocation", "nexus_othersamountallocation"),


    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !

    • Marked as answer by Honey Dulatre Wednesday, November 6, 2013 7:58 AM
    Wednesday, November 6, 2013 6:02 AM
  • Everything is correct, just remove some lines :

    QueryExpression _Query = new QueryExpression
                     {
                         EntityName = "nexus_budgetnonbudgetedexpenses",
                         ColumnSet = new ColumnSet("nexus_paymentstatuscheckbox", "nexus_tpcamountallocation", "nexus_transportationamountallocation", "nexus_representationamountallocation", "nexus_adminexpenseamountallocation", "nexus_communicationamountallocation", "nexus_technicalsupportamountallocation", "nexus_trainingamountallocation", "nexus_freebiesamountallocation", "nexus_perdiemamountallocation", "nexus_deliveryamountallocation", "nexus_refundableamountallocation", "nexus_othersamountallocation"),
                         Criteria =
                         {
                             FilterOperator = LogicalOperator.And,
                             Conditions =
                                {
                                    new ConditionExpression
                                    {
                                        AttributeName="nexus_paymentstatuscheckbox",
                                        Operator=ConditionOperator.Equal,
                                        Values={false}
                                    },
    
                                    new ConditionExpression
                                    {
                                        AttributeName="nexus_accountbudgetexpenses",
                                        Operator=ConditionOperator.Equal,
                                        Values={uRelatedAccountId}
                                    }
                                }
                         }
    
                     };
                      
                            EntityCollection result = service.RetrieveMultiple(_Query);
    
                            foreach (Entity EntityResult in result.Entities)
                            {
                       
                                tpc = tpc + EntityResult.GetAttributeValue<double>("nexus_tpcamountallocation");
                                transportation = transportation + EntityResult.GetAttributeValue<double>("nexus_transportationamountallocation");
                                adminexpense = adminexpense + EntityResult.GetAttributeValue<double>("nexus_adminexpenseamountallocation");
                                representation = representation + EntityResult.GetAttributeValue<double>("nexus_representationamountallocation");
                                communication = communication + EntityResult.GetAttributeValue<double>("nexus_communicationamountallocation");
                                technicalsupport = technicalsupport + EntityResult.GetAttributeValue<double>("nexus_technicalsupportamountallocation");
    
                                training = training + EntityResult.GetAttributeValue<double>("nexus_trainingamountallocation");
                                freebies = freebies + EntityResult.GetAttributeValue<double>("nexus_freebiesamountallocation");
                                perdiem = perdiem + EntityResult.GetAttributeValue<double>("nexus_perdiemamountallocation");
                                delivery = delivery + EntityResult.GetAttributeValue<double>("nexus_deliveryamountallocation");
                                refundable = refundable + EntityResult.GetAttributeValue<double>("nexus_refundableamountallocation");
                                others = others + EntityResult.GetAttributeValue<double>("nexus_othersamountallocation");
    
                            }



    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !

    • Marked as answer by Honey Dulatre Wednesday, November 6, 2013 7:58 AM
    Wednesday, November 6, 2013 6:45 AM

All replies

  • Hi,

    By default conditions operator works with AND operator, if I am not wrong.

    Have you tried to execute above code ?


    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !

    Wednesday, November 6, 2013 3:33 AM
  • Hello,

    Yes i have tried to execute the code above but I'm getting a total record count of 1 which is supposed to be 0. Do you know where did i get wrong from the code?

    Thank you for the support.

    Regards,

    Honey

    Wednesday, November 6, 2013 3:49 AM
  • are you checking count like as below ? following count property will return number of records fetched.

    result.Entities.Count 



    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !

    Wednesday, November 6, 2013 4:00 AM
  • hi,

    i have tried this code instead result.TotalRecordCount . It returns 1 total record, which is suppose to be 0. i'm not sure if the query works correctly.

    Thank you.

    Honey


    Wednesday, November 6, 2013 5:01 AM
  • Hi,

    I think the code is not working correctly, is there any alternative code for this?

    Thank you for the support.

    Regards,

    Honey

    Wednesday, November 6, 2013 5:10 AM
  • Hello,

    Try like as below :

    QueryExpression _Query = new QueryExpression
                    {
                        EntityName = "nexus_budgetnonbudgetedexpenses",
                        ColumnSet = new ColumnSet(true),
                        Criteria =
                        {
                            FilterOperator = LogicalOperator.And,
                            Conditions =
                            {
                                new ConditionExpression
                                {
                                    AttributeName="nexus_paymentstatuscheckbox",
                                    Operator=ConditionOperator.Equal,
                                    Values={false}
    
                                },
    
                                new ConditionExpression
                                {
                                    AttributeName="nexus_accountbudgetexpenses",
                                    Operator=ConditionOperator.Equal,
                                    Values={uRelatedAccountId}
    
                                }
                            }
                        }
    
                    };
    
    EntityCollection _Entities = service.RetrieveMultiple(_Query);
    
    if (_Entities.Entities.Count > 0)
    {
    }


    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !


    • Edited by _Vikram Wednesday, November 6, 2013 5:24 AM
    • Marked as answer by Honey Dulatre Wednesday, November 6, 2013 7:35 AM
    Wednesday, November 6, 2013 5:14 AM
  • Hi,

    Your code works, it's now counting correct number of record. Now, how can i add the column names in the query? This is how i add column or the fields to be retrieved: query.ColumnSet = new ColumnSet(new string[] { "nexus_paymentstatuscheckbox", "nexus_tpcamountallocation", "nexus_transportationamountallocation", "nexus_representationamountallocation", "nexus_adminexpenseamountallocation", "nexus_communicationamountallocation", "nexus_technicalsupportamountallocation", "nexus_trainingamountallocation", "nexus_freebiesamountallocation", "nexus_perdiemamountallocation", "nexus_deliveryamountallocation", "nexus_refundableamountallocation", "nexus_othersamountallocation" });

    I want to know how do i do it using your code. Thank you so much for the support.

    Regards,

    Honey

    Wednesday, November 6, 2013 5:52 AM
  • Write as like below :

     ColumnSet = new ColumnSet("nexus_paymentstatuscheckbox", "nexus_tpcamountallocation", "nexus_transportationamountallocation", "nexus_representationamountallocation", "nexus_adminexpenseamountallocation", "nexus_communicationamountallocation", "nexus_technicalsupportamountallocation", "nexus_trainingamountallocation", "nexus_freebiesamountallocation", "nexus_perdiemamountallocation", "nexus_deliveryamountallocation", "nexus_refundableamountallocation", "nexus_othersamountallocation"),


    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !

    • Marked as answer by Honey Dulatre Wednesday, November 6, 2013 7:58 AM
    Wednesday, November 6, 2013 6:02 AM
  • Hi,

    Does the code should be like this?

                            

                        QueryExpression _Query = new QueryExpression
                        {
                            EntityName = "nexus_budgetnonbudgetedexpenses",
                            ColumnSet = new ColumnSet("nexus_paymentstatuscheckbox", "nexus_tpcamountallocation", "nexus_transportationamountallocation", "nexus_representationamountallocation", "nexus_adminexpenseamountallocation", "nexus_communicationamountallocation", "nexus_technicalsupportamountallocation", "nexus_trainingamountallocation", "nexus_freebiesamountallocation", "nexus_perdiemamountallocation", "nexus_deliveryamountallocation", "nexus_refundableamountallocation", "nexus_othersamountallocation"),
                            Criteria =
                            {
                                FilterOperator = LogicalOperator.And,
                                Conditions =
                                {
                                    new ConditionExpression
                                    {
                                        AttributeName="nexus_paymentstatuscheckbox",
                                        Operator=ConditionOperator.Equal,
                                        Values={false}
                                    },

                                    new ConditionExpression
                                    {
                                        AttributeName="nexus_accountbudgetexpenses",
                                        Operator=ConditionOperator.Equal,
                                        Values={uRelatedAccountId}
                                    }
                                }
                            }

                        };
                        //FROM SUPPORT

                        EntityCollection result = service.RetrieveMultiple(_Query);

                        //

                        if (result.Entities.Count > 0)
                        {

                        }

    Lastly, how do i retrieve the values from the column set? i used to use for each with this code before:

                                

                            foreach (Entity EntityResult in result.Entities)
                            {
                       
                                tpc = tpc + EntityResult.GetAttributeValue<double>("nexus_tpcamountallocation");
                                transportation = transportation + EntityResult.GetAttributeValue<double>("nexus_transportationamountallocation");
                                adminexpense = adminexpense + EntityResult.GetAttributeValue<double>("nexus_adminexpenseamountallocation");
                                representation = representation + EntityResult.GetAttributeValue<double>("nexus_representationamountallocation");
                                communication = communication + EntityResult.GetAttributeValue<double>("nexus_communicationamountallocation");
                                technicalsupport = technicalsupport + EntityResult.GetAttributeValue<double>("nexus_technicalsupportamountallocation");

                                training = training + EntityResult.GetAttributeValue<double>("nexus_trainingamountallocation");
                                freebies = freebies + EntityResult.GetAttributeValue<double>("nexus_freebiesamountallocation");
                                perdiem = perdiem + EntityResult.GetAttributeValue<double>("nexus_perdiemamountallocation");
                                delivery = delivery + EntityResult.GetAttributeValue<double>("nexus_deliveryamountallocation");
                                refundable = refundable + EntityResult.GetAttributeValue<double>("nexus_refundableamountallocation");
                                others = others + EntityResult.GetAttributeValue<double>("nexus_othersamountallocation");

                            }

    How do i do that using your code?

    Thank you.

    Honey

    Wednesday, November 6, 2013 6:29 AM
  • Everything is correct, just remove some lines :

    QueryExpression _Query = new QueryExpression
                     {
                         EntityName = "nexus_budgetnonbudgetedexpenses",
                         ColumnSet = new ColumnSet("nexus_paymentstatuscheckbox", "nexus_tpcamountallocation", "nexus_transportationamountallocation", "nexus_representationamountallocation", "nexus_adminexpenseamountallocation", "nexus_communicationamountallocation", "nexus_technicalsupportamountallocation", "nexus_trainingamountallocation", "nexus_freebiesamountallocation", "nexus_perdiemamountallocation", "nexus_deliveryamountallocation", "nexus_refundableamountallocation", "nexus_othersamountallocation"),
                         Criteria =
                         {
                             FilterOperator = LogicalOperator.And,
                             Conditions =
                                {
                                    new ConditionExpression
                                    {
                                        AttributeName="nexus_paymentstatuscheckbox",
                                        Operator=ConditionOperator.Equal,
                                        Values={false}
                                    },
    
                                    new ConditionExpression
                                    {
                                        AttributeName="nexus_accountbudgetexpenses",
                                        Operator=ConditionOperator.Equal,
                                        Values={uRelatedAccountId}
                                    }
                                }
                         }
    
                     };
                      
                            EntityCollection result = service.RetrieveMultiple(_Query);
    
                            foreach (Entity EntityResult in result.Entities)
                            {
                       
                                tpc = tpc + EntityResult.GetAttributeValue<double>("nexus_tpcamountallocation");
                                transportation = transportation + EntityResult.GetAttributeValue<double>("nexus_transportationamountallocation");
                                adminexpense = adminexpense + EntityResult.GetAttributeValue<double>("nexus_adminexpenseamountallocation");
                                representation = representation + EntityResult.GetAttributeValue<double>("nexus_representationamountallocation");
                                communication = communication + EntityResult.GetAttributeValue<double>("nexus_communicationamountallocation");
                                technicalsupport = technicalsupport + EntityResult.GetAttributeValue<double>("nexus_technicalsupportamountallocation");
    
                                training = training + EntityResult.GetAttributeValue<double>("nexus_trainingamountallocation");
                                freebies = freebies + EntityResult.GetAttributeValue<double>("nexus_freebiesamountallocation");
                                perdiem = perdiem + EntityResult.GetAttributeValue<double>("nexus_perdiemamountallocation");
                                delivery = delivery + EntityResult.GetAttributeValue<double>("nexus_deliveryamountallocation");
                                refundable = refundable + EntityResult.GetAttributeValue<double>("nexus_refundableamountallocation");
                                others = others + EntityResult.GetAttributeValue<double>("nexus_othersamountallocation");
    
                            }



    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !

    • Marked as answer by Honey Dulatre Wednesday, November 6, 2013 7:58 AM
    Wednesday, November 6, 2013 6:45 AM
  • hi,

    i can get the correct total record count, but when i retrieve the column values, no data has been displayed. Is there any missing code ?

    Thanks and regards,

    Honey

    Wednesday, November 6, 2013 7:08 AM
  • Hi Vikram,

    I was now able to get the right values.

    Thank you so much for your HELP. :)

    regards,

    Honey

    Wednesday, November 6, 2013 7:35 AM