locked
crm 4 fetch condition date field in the given month RRS feed

  • Question

  • Hi this might be easy question but was tricky for me. what is the best way to fetch records from crm 4 entity base on date field?

    I need to select all records from my custom entity where expiry date in the month after three months from the current month.

    if I run fetch today I need to list all records expiry date set  in december 2011  i have tried

     fetch1 += "<condition attribute='new_expirydate' operator='onorbefore' value='" + lastdate + "' /><condition attribute='new_expirydate' operator='onorafter' value='" + startdate + "' /><condition attribute='statecode' operator='eq' value='0'/>";
    
                  
    
    

     

    but this gives me error System.Web.Services.Protocols.SoapException: Server was unable to process request. at , i am passing date in MM/dd/yyy format

     

    • Edited by pathiya007 Tuesday, September 27, 2011 12:15 PM
    Tuesday, September 27, 2011 12:11 PM

Answers

  • yes but I dont need all close in next three months,  what I wanted to do is, list all due close on the third month from this month,

    so i f I run the fetch on any day of september I need all close due on December first to 31st

    this is what i did

    DateTime nextreneealmonth = DateTime.Now.AddMonths(3);
                     int noofdays = DateTime.DaysInMonth (nextreneealmonth .Year, nextreneealmonth .Month );    
                
                     string monthval = nextreneealmonth.Month.ToString ();
                     if (monthval.Length == 1)
                     {
                         monthval = "0" + monthval;
                     }
                     string startdate =  nextreneealmonth.Year + "-" + monthval +  "-01"  ;
                     string lastdate = nextreneealmonth.Year + "-" +  monthval + "-" + noofdays  ;
    
                     string fetch1 = "<fetch  version='1.0' mapping='logical' output-format='xml-platform'  >";
                     fetch1 += "<entity name='new_customerservice'><attribute name ='new_name'/><attribute name ='new_expirydate'/><attribute name ='new_customerid'/><attribute name ='new_contactpersonid'/>";
                     fetch1 += "<link-entity name='account' from='accountid' to='new_customerid'>";
                     fetch1 += "<attribute name='name' /></link-entity>";
                     fetch1 += "<link-entity name='contact' from='contactid' to='new_contactpersonid'>";
                     fetch1 += "<attribute name='emailaddress1' /><attribute name='fullname' /></link-entity>";
                     fetch1 += "<link-entity name='product' from='productid' to='new_productid'>";
                     fetch1 += "<attribute name='name'/><attribute name='defaultuomid'/><attribute name='productnumber'/></link-entity>";
                     fetch1 += "<filter type='and'>";
                     fetch1 += "<condition attribute='new_expirydate' operator='on-or-before' value='" + lastdate + "' /><condition attribute='new_expirydate' operator='on-or-after' value='" + startdate + "' /><condition attribute='statecode' operator='eq' value='0'/>";
                     fetch1 += "</filter>";
                     fetch1 += "</entity></fetch>";
    

     

    • Marked as answer by pathiya007 Thursday, January 19, 2012 4:21 PM
    Thursday, September 29, 2011 11:35 AM

All replies

  • I have change code to on-or-before and on-or-after and date format  to yyyy-MM-dd then it gave me result.

    when I use operator ='on' I can use date format MM/dd/yyyy

    i don't no theory behind this. anyway this works now.

    Tuesday, September 27, 2011 12:46 PM
  • A tip buddy, If you want fetch xml. Create a advance find in crm and "Download fetch Xml" button in the ribbon you will get the perfect fetch xml

    Instead of messing with the date use this query, it will work all the time

    The query is to get all the opportunities with estimated close date in next 3 months

    <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="estimatedclosedate" operator="next-x-months" value="3" />

    </filter>

    </entity>

    </fetch>

    Change it according to your needs.

    I hope this helps.


    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    • Proposed as answer by Amreek Singh Tuesday, September 27, 2011 1:47 PM
    Tuesday, September 27, 2011 1:47 PM
  • When you get the error "Server was unable to process request", you have to put a try catch block around your code to intercept a SoapException error.

    Then to display the real error message: use error.Detail.OuterXml


    My blog : http://mscrmtools.blogspot.com

    All my tools for Dynamics CRM 4.0 on my dedicated site: MSCRMTools Repository

    Tuesday, September 27, 2011 2:00 PM
    Moderator
  • yes but I dont need all close in next three months,  what I wanted to do is, list all due close on the third month from this month,

    so i f I run the fetch on any day of september I need all close due on December first to 31st

    this is what i did

    DateTime nextreneealmonth = DateTime.Now.AddMonths(3);
                     int noofdays = DateTime.DaysInMonth (nextreneealmonth .Year, nextreneealmonth .Month );    
                
                     string monthval = nextreneealmonth.Month.ToString ();
                     if (monthval.Length == 1)
                     {
                         monthval = "0" + monthval;
                     }
                     string startdate =  nextreneealmonth.Year + "-" + monthval +  "-01"  ;
                     string lastdate = nextreneealmonth.Year + "-" +  monthval + "-" + noofdays  ;
    
                     string fetch1 = "<fetch  version='1.0' mapping='logical' output-format='xml-platform'  >";
                     fetch1 += "<entity name='new_customerservice'><attribute name ='new_name'/><attribute name ='new_expirydate'/><attribute name ='new_customerid'/><attribute name ='new_contactpersonid'/>";
                     fetch1 += "<link-entity name='account' from='accountid' to='new_customerid'>";
                     fetch1 += "<attribute name='name' /></link-entity>";
                     fetch1 += "<link-entity name='contact' from='contactid' to='new_contactpersonid'>";
                     fetch1 += "<attribute name='emailaddress1' /><attribute name='fullname' /></link-entity>";
                     fetch1 += "<link-entity name='product' from='productid' to='new_productid'>";
                     fetch1 += "<attribute name='name'/><attribute name='defaultuomid'/><attribute name='productnumber'/></link-entity>";
                     fetch1 += "<filter type='and'>";
                     fetch1 += "<condition attribute='new_expirydate' operator='on-or-before' value='" + lastdate + "' /><condition attribute='new_expirydate' operator='on-or-after' value='" + startdate + "' /><condition attribute='statecode' operator='eq' value='0'/>";
                     fetch1 += "</filter>";
                     fetch1 += "</entity></fetch>";
    

     

    • Marked as answer by pathiya007 Thursday, January 19, 2012 4:21 PM
    Thursday, September 29, 2011 11:35 AM