locked
How Retrive Recrods by fetch xml RRS feed

  • Question

  • I want to retrive leads records using fetch xml based on Lead Attribute "scheduledDatetime"

    but in Advance find query there is  only option for Date for that attribute.how can i retrieve lead record by selected time also.....


    Muhammad Sohail

    Monday, July 8, 2013 6:36 AM

All replies

  • All DateTime fields comprise a Date and a Time component, even if they're set to display 'Date Only'.

    However, any conditions in FetchXml work across the Date and the Time components of the field; there are no functions for manipulating the Time component only. So, it is possible to find all records where scheduledDateTime < '1 jan 2013 10:00' for example, but it is not possible to query for all leads where scheduledDateTime <'10:00' on any date.

    Can you give more details on the query you want


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Monday, July 8, 2013 7:42 AM
    Moderator
  • you cant do this by advanced find use report instead  of it.

    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Monday, July 8, 2013 7:50 AM
  • ok I donot Want to Retrieve leads  by Only Time

    I know that I should Retrive record on Date.

    for Example Want to see all records that have Time 10:00 on today date....

    and want to time as Paramertic in fetchxml So Retrived Records on different time but same Date.

    i am using fetch xml but it give me error...

    var datetime= new Date();
    var fetchxml="<fetch mapping='logical'>"+
      "<entity name='lead'>"+
        "<attribute name='fullname' />"+
        "<attribute name='companyname' />"+
        "<attribute name='telephone1' />"+
        "<attribute name='leadid' />"+
        "<attribute name='new_scheduledtime' />"+
        "<order attribute='fullname' descending='false' />"+
        "<filter type='and'>"+
          "<condition attribute='statecode' operator='eq' value='0' />"+
          "<condition attribute='new_scheduledtime' operator='on' value='"+datetime+"' />"+
        "</filter>"+
      "</entity>"+
    "</fetch>";
    var fetchedLeads = CrmServiceToolkit.Fetch(fetchxml);
           //alert(fetchedLeads.length);
        if(fetchedLeads.length>0)
        {
          var leadId=fetchedLeads[0].getValue('leadid');
          Xrm.Utility.openEntityForm("lead",leadId);
        }


    Muhammad Sohail



    • Edited by sohail450 Monday, July 8, 2013 8:41 AM cahnging
    Monday, July 8, 2013 8:35 AM
  • 'time as Paramertic in fetchxml So Retrived Records on different time but same Date' - this is not possible in FetchXml.

    Your only option will be to return all records, then iterate through the Entities and use the .Net DateTime methods to determine the time component (e.g. 10:00)


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Monday, July 8, 2013 10:29 AM
    Moderator
  • Here seems error is due to format mismatch. Try to debug your code in IE.then get exact error .

    You can try to hard code value to test your query at runtime.


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"


    Monday, July 8, 2013 10:43 AM
  • Note :I am using this code in javascript that is called from ribbon button.

    so how use .netDatTime?


    Muhammad Sohail

    Monday, July 8, 2013 10:47 AM
  • you can also debug javascript that is called from ribbon button.

    or you can use your code to run on load of form.

    then you will be specific for your error.


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Monday, July 8, 2013 11:02 AM
  • The error is more than likely going to be a result of your date/time format in the fetch statement.  By default JavaScript will serialize your date/time object using the long date/time format for the system.  This typically will look something like this, "Mon Jul 8 07:41:12 EDT 2013".   You need to format the date/time using an ISO format like yyyy-mm-ddThh:mm:ssZ.  The easiest way to do this is with the toISOString method of the date object.  You should change your datetime reference when building your fetch to datetime.toISOString().  If you are using IE8 or earlier this function will not work and you will need to manually build the string by extracting each date component yourself.  The toISOString function will also use the UTC time, as denoted by the trailing Z in the serialized string which makes things easier to deal with across time zones.


    Nick

    Monday, July 8, 2013 11:48 AM
  • hi Nick  i am using below code  as you say for the fetch format.

    var datetime=new Date();

    var time=datetime.toISOString();

    alert(time);

    this is not work but i am using another code and getting this formate as shown in fig is it ok? can this type of formate in fetch xml or different?.

    var datetime=new Date();

    var time=datetime.format("yyyy-MM-dd'T'HH:MM:ss'Z'");

    aler(time);


    Muhammad Sohail




    • Edited by sohail450 Monday, July 8, 2013 2:46 PM chaning
    Monday, July 8, 2013 2:44 PM
  • Yes, that format should work.  Can you put a debugger statement in your code and/or capture the response from the service call using Fiddler.  There may be something else going on that is not related to the format of the date/time.  In my experience debugging ribbon code by setting breakpoints can be cumbersome and it generally easier if you put a debugger statement in the code as the breakpoints are often not hit with IE.

    Nick

    Monday, July 8, 2013 2:53 PM