locked
query not returning any business entity collection. RRS feed

  • Question

  • Hi Guys,

    I am having issue with query by expression.

    I am trying to get all the projects which has  active status and 'project end date' has passed today i.e (project end date < today);

    As CRM stores date in UTC I am convertin system datetime into UTC to compare/check.

    Custom Entity Name = new_Projects

    Attribute = new_Projenddate.

    try
    
    
     {
    
     ColumnSet cols = new ColumnSet();
     cols.Attributes.AddRange(new string[] { "new_projenddate", "statecode" });
    
    ConditionExpression con1 = new ConditionExpression();
     con1.AttributeName = "statecode";
     con1.Operator = ConditionOperator.Equal;
    
    
     con1.Values = new string[] { "Active" };
     ConditionExpression con2 = new ConditionExpression();
    con2.AttributeName = "new_projenddate";
     con2.Operator = ConditionOperator.LessThan;
     con2.Values = new string[] { DateTime.Today.Date.ToUniversalTime().ToString() }; // is this correct ???
    
     FilterExpression filter = new FilterExpression();
    
    filter.Conditions.Add(con1);
     filter.Conditions.Add(con2);
     filter.FilterOperator = LogicalOperator.And;
     
    QueryExpression query = new QueryExpression();
     query.ColumnSet = cols;
     query.EntityName = "new_projects".ToString(); 
    
     query.Criteria = filter;
    
     RetrieveMultipleRequest req = new RetrieveMultipleRequest();
     req.Query = query;
     req.ReturnDynamicEntities = true;
    
     RetrieveMultipleResponse res = (RetrieveMultipleResponse)service.Execute(req);
     if (res.BusinessEntityCollection != null && res.BusinessEntityCollection.BusinessEntities.Count > 0)
     {
    
    
    for (int i = 0; i < res.BusinessEntityCollection.BusinessEntities.Count; i++){
    
    
     DynamicEntity deproj = (DynamicEntity)res.BusinessEntityCollection.BusinessEntities[i];
    
     DeactivateProjects(deproj["new_projectsid"].ToString()); //calling function and pasing id of the project entity
    
    
    
     }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
     }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
     }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
     catch (System.Web.Services.Protocols.SoapException ex)
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
     {
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
     //excetion here
    
    
    
     }
    
    
    
    // function defination
    
    
    
    public void DeactivateCallCycles(string projID){
    
    
    SetStateDynamicEntityRequest deactivateRequest = new SetStateDynamicEntityRequest();
    deactivateRequest.Entity = new Moniker("new_projects", new Guid(projID));
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    deactivateRequest.State = "inactive";
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    deactivateRequest.Status = -1;
    
    
    
    service.Execute(deactivateRequest);
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

    Thank you all for your help - CRM Thirsty
    Thursday, October 7, 2010 6:43 AM

Answers

  • You can use DateTime.UtcNow.ToString("yyyy-MM-dd") to get Universal date.

    you can use ConditionOperator.LessThan in your ConditionExpression

    con1.Values = new string[] { "Active" };
    ConditionExpression con2 = new ConditionExpression();
    con2.AttributeName = "new_projenddate";
    con2.Operator = ConditionOperator.LessThan;
    con2.Values = new string[] { DateTime.UtcNow.ToString("yyyy-MM-dd") };

    • Marked as answer by CRM Thirsty Thursday, October 7, 2010 10:37 AM
    Thursday, October 7, 2010 10:03 AM

All replies

  • Need to format it as DateTime.Now.ToString("yyyy-MM-dd")

    You can use DateTime.Now.ToString("yyyy-MM-dd") along with ConditionOperator.LessEqual or ConditionOperator.OnOrBefore

    Thursday, October 7, 2010 7:34 AM
  • Hi Vinoth,

    Many thanks for this reply.

    Just one question..will this give me Universal date? also, can i use ConditionOperator.Lessthan rahter than LessEqual as my businesslogic

    requires to exclude 'new_projenddate' date,meaning all records less than todays date. (new_projenddate < today.universaldate)

    Thanks.


    Thank you all for your help - CRM Thirsty
    Thursday, October 7, 2010 9:26 AM
  • You can use DateTime.UtcNow.ToString("yyyy-MM-dd") to get Universal date.

    you can use ConditionOperator.LessThan in your ConditionExpression

    con1.Values = new string[] { "Active" };
    ConditionExpression con2 = new ConditionExpression();
    con2.AttributeName = "new_projenddate";
    con2.Operator = ConditionOperator.LessThan;
    con2.Values = new string[] { DateTime.UtcNow.ToString("yyyy-MM-dd") };

    • Marked as answer by CRM Thirsty Thursday, October 7, 2010 10:37 AM
    Thursday, October 7, 2010 10:03 AM
  • Thanks Vinoth for this prompt help....

    Much appreciated.

     

    Cheers.


    Thank you all for your help - CRM Thirsty
    Thursday, October 7, 2010 10:41 AM