locked
CRM 2013: Retrieve Multiple Business Closure Dates RRS feed

  • Question

  • Hello,

    I'm trying to write an oData query to retrieve Business Closure Dates.

    For example this is our Business Closures data, and we want to retrieve 11/27/14 and 11/28/2014.

    Equivalent to SQL query would be this, so I think the entity should be 'CalendarRule'. The field that I want it 'StartTime'.

    Select starttime from calendarrule where calendarid in
    (select calendarid from calendar where name='Business Closure Calendar')

    I try to construct my oData query using oData tool here from Codeplex, it's working great for other entity like Account, etc.

    But for some reason it's not working well for CalendarRule. This is the query that I got but it's giving me HTTP 404 Not Found when I open it.

    https://crmtest.crm.com/crmorg/xrmservices/2011/OrganizationData.svc/CalendarRuleSet?$select=StartTime

    Any suggestion on how to fix this? Appreciate your help.

    -tri

    Tuesday, November 25, 2014 2:12 AM

All replies

  • I suspect not, at least not via OData - the beauty of OData/REST is that you can check queries in IE via typing directly in the address bar:

    http://<server>:<port>/TT/XrmServices/2011/OrganizationData.svc/CalendarSet?$filter=Name eq 'Business Closure Calendar'

    and when you do so with the above there is no mention of the rules.  Calendar Rules are somewhat 'special' - they are stored in a separate entity, but are available as an attribute (collection) when querying via QueryExpression.

    You will be able to access them via a JScript QueryExpression RetrieveMultiple request - more work but sometimes it is the only way.

    Refer this link.


    Regards, Saad

    Tuesday, November 25, 2014 5:39 AM
  • HTTP 404 Not Found is resource error not sql query error, can you post your detailed error here ?
    Tuesday, November 25, 2014 7:41 AM
  • Thanks for all your reply. Sorry just replying now as we are just done with holiday.

    Saad I agree with you it looks like we cannot use oData but doesn't seem to work with Calendar Rules.

    I'm trying to understand the works with JScript QueryExpression RetrieveMultiple from Jamie Miley's blog but it cant seem to wrap my head around this. Would you please give me a very simple example how to retrieve the field 'starttime' from Calendar Rules using JScript Query Expression?

    So in SQL language it would be something like this:

    Select starttime from calendarrule where calendarid in
    (select calendarid from calendar where name='Business Closure Calendar')

    Thanks much.

    -tri

    Monday, December 1, 2014 5:21 AM
  • Hi,

    Please use the below to get Business Closure days start time:

    function guid1(){
    
    var xml = "<?xml version='1.0' encoding='utf-8'?>" + 
        "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" + 
        GenerateAuthenticationHeader() +
        "<soap:Body>" + 
        "<RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" + 
        "<query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" + 
        "<q1:EntityName>calendar</q1:EntityName>" + 
        "<q1:ColumnSet xsi:type='q1:ColumnSet'>" + 
        "<q1:Attributes>" + 
        "</q1:Attributes>" + 
        "</q1:ColumnSet>" + 
        "<q1:Distinct>false</q1:Distinct>" + 
    	
    	"<q1:Criteria>" +
        "<q1:FilterOperator>And</q1:FilterOperator>" +
        "<q1:Conditions>" +
        "<q1:Condition>" +
        "<q1:AttributeName>name</q1:AttributeName>" +
        "<q1:Operator>Equal</q1:Operator>" +
        "<q1:Values>" +
        "<q1:Value xsi:type=\"xsd:string\">" + "Business Closure Calendar" + "</q1:Value>" +
        "</q1:Values>" +
        "</q1:Condition>" +
        "</q1:Conditions>" +
        "</q1:Criteria>" +
    
        "</query></RetrieveMultiple>" +
        "</soap:Body></soap:Envelope>";
    
    
        var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    
        xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
        xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
        xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
        xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
        xmlHttpRequest.send(xml);
    
        var result = xmlHttpRequest.responseXML.xml;
        var doc = new ActiveXObject("MSXML2.DOMDocument");
        doc.async = false;
        doc.loadXML(result);
    	
    var arrayAnswers = [];
    var arr = doc.selectNodes("//q1:starttime");
        for(var i=0, len = arr.length; i < len; i++)
        {
    		
           arrayAnswers[i] = arr.nextNode.text;
    
        }
    	
    	
    	alert(arrayAnswers);
    	alert(arrayAnswers.length);
    
    }

    This is the result fetchxml for all attributes of calendar rule table.

    "
    <?xml version=\"1.0\"?>\r\n
    <soap:Envelope
        xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\"
        xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"
        xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">
        <soap:Body>
            <RetrieveMultipleResponse
                xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">
                <RetrieveMultipleResult EntityName=\"calendar\" MoreRecords=\"0\" PagingCookie=\"&lt;cookie page=&quot;1&quot;&gt;&lt;calendarid last=&quot;{42C71022-B284-E311-BB31-000C29E9425D}&quot; first=&quot;{42C71022-B284-E311-BB31-000C29E9425D}&quot; /&gt;&lt;/cookie&gt;\" TotalRecordCount=\"-1\" TotalRecordCountLimitExceeded=\"0\">
                    <BusinessEntities
                        xmlns=\"http://schemas.microsoft.com/crm/2006/WebServices\">
                        <BusinessEntity
                            xmlns:q1=\"http://schemas.microsoft.com/crm/2007/WebServices\" xsi:type=\"q1:calendar\">
                            <q1:calendarid>{42C71022-B284-E311-BB31-000C29E9425D}</q1:calendarid>
                            <q1:calendarrules>
                                <q1:calendarrule
                                    xmlns:q1=\"http://schemas.microsoft.com/crm/2007/WebServices\" xsi:type=\"q1:calendarrule\">
                                    <q1:calendarruleid>{373A8C13-2479-E411-ABE7-000C29E9425D}</q1:calendarruleid>
                                    <q1:organizationid>{648C02F1-B184-E311-BB31-000C29E9425D}</q1:organizationid>
                                    <q1:starttime date=\"12/16/2014\" time=\"5:30 AM\">2014-12-16T05:30:00+05:30</q1:starttime>
                                    <q1:issimple name=\"No\">0</q1:issimple>
                                    <q1:extentcode formattedvalue=\"2\">2</q1:extentcode>
                                    <q1:duration formattedvalue=\"1,440\">1440</q1:duration>
                                    <q1:calendarid>{42C71022-B284-E311-BB31-000C29E9425D}</q1:calendarid>
                                    <q1:businessunitid>{FC7BA412-B284-E311-BB31-000C29E9425D}</q1:businessunitid>
                                    <q1:createdby name=\"crm2011 Last name\" type=\"systemuser\">{3CC71022-B284-E311-BB31-000C29E9425D}</q1:createdby>
                                    <q1:effectiveintervalend date=\"12/17/2014\" time=\"5:30 AM\">2014-12-17T05:30:00+05:30</q1:effectiveintervalend>
                                    <q1:rank formattedvalue=\"0\">0</q1:rank>
                                    <q1:effectiveintervalstart date=\"12/16/2014\" time=\"5:30 AM\">2014-12-16T05:30:00+05:30</q1:effectiveintervalstart>
                                    <q1:createdon date=\"12/1/2014\" time=\"12:04 PM\">2014-12-01T12:04:19+05:30</q1:createdon>
                                    <q1:description>Holiday Rule</q1:description>
                                    <q1:timezonecode formattedvalue=\"-1\">-1</q1:timezonecode>
                                    <q1:modifiedby name=\"crm2011 Last name\" type=\"systemuser\">{3CC71022-B284-E311-BB31-000C29E9425D}</q1:modifiedby>
                                    <q1:timecode formattedvalue=\"2\">2</q1:timecode>
                                    <q1:pattern>FREQ=DAILY;INTERVAL=1;COUNT=1</q1:pattern>
                                    <q1:modifiedon date=\"12/1/2014\" time=\"12:05 PM\">2014-12-01T12:05:05+05:30</q1:modifiedon>
                                    <q1:name>test</q1:name>
                                    <q1:subcode formattedvalue=\"5\">5</q1:subcode>
                                </q1:calendarrule>
                                <q1:calendarrule
                                    xmlns:q1=\"http://schemas.microsoft.com/crm/2007/WebServices\" xsi:type=\"q1:calendarrule\">
                                    <q1:calendarruleid>{F3C01E2F-2479-E411-ABE7-000C29E9425D}</q1:calendarruleid>
                                    <q1:organizationid>{648C02F1-B184-E311-BB31-000C29E9425D}</q1:organizationid>
                                    <q1:starttime date=\"12/25/2014\" time=\"5:30 AM\">2014-12-25T05:30:00+05:30</q1:starttime>
                                    <q1:issimple name=\"No\">0</q1:issimple>
                                    <q1:extentcode formattedvalue=\"2\">2</q1:extentcode>
                                    <q1:duration formattedvalue=\"1,440\">1440</q1:duration>
                                    <q1:calendarid>{42C71022-B284-E311-BB31-000C29E9425D}</q1:calendarid>
                                    <q1:businessunitid>{FC7BA412-B284-E311-BB31-000C29E9425D}</q1:businessunitid>
                                    <q1:createdby name=\"crm2011 Last name\" type=\"systemuser\">{3CC71022-B284-E311-BB31-000C29E9425D}</q1:createdby>
                                    <q1:effectiveintervalend date=\"12/26/2014\" time=\"5:30 AM\">2014-12-26T05:30:00+05:30</q1:effectiveintervalend>
                                    <q1:rank formattedvalue=\"0\">0</q1:rank>
                                    <q1:effectiveintervalstart date=\"12/25/2014\" time=\"5:30 AM\">2014-12-25T05:30:00+05:30</q1:effectiveintervalstart>
                                    <q1:createdon date=\"12/1/2014\" time=\"12:05 PM\">2014-12-01T12:05:05+05:30</q1:createdon>
                                    <q1:description>Holiday Rule</q1:description>
                                    <q1:timezonecode formattedvalue=\"-1\">-1</q1:timezonecode>
                                    <q1:modifiedby name=\"crm2011 Last name\" type=\"systemuser\">{3CC71022-B284-E311-BB31-000C29E9425D}</q1:modifiedby>
                                    <q1:timecode formattedvalue=\"2\">2</q1:timecode>
                                    <q1:pattern>FREQ=DAILY;INTERVAL=1;COUNT=1</q1:pattern>
                                    <q1:modifiedon date=\"12/1/2014\" time=\"12:05 PM\">2014-12-01T12:05:05+05:30</q1:modifiedon>
                                    <q1:name>Christmas</q1:name>
                                    <q1:subcode formattedvalue=\"5\">5</q1:subcode>
                                </q1:calendarrule>
                            </q1:calendarrules>
                        </BusinessEntity>
                    </BusinessEntities>
                </RetrieveMultipleResult>
            </RetrieveMultipleResponse>
        </soap:Body>
    </soap:Envelope>\r\n"


    Regards, Saad

    Monday, December 1, 2014 7:47 AM
  • Thanks much Saad, greatly appreciate it.

    I'm still trying this but keep getting the error message "'GenerateAuthenticationHeader' is undefined". Any idea?

    Edit: Just found that 'GenerateAuthenticationHeader' is deprecated in CRM 2013, the version that we are using now.

    Is there an alternative way to this?

    I remove 'GenerateAuthenticationHeader' from my code an as a result it's not outputting anything, probably due to missing authentication.

    I also tried to replace 2007 service endpoint with getServerUrl() but still not outputting anything. Below is the xmlHttpRequest that I changed:

    var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    
            xmlHttpRequest.Open("POST", Xrm.Page.context.getServerUrl(), false);
            xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
            xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
            xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
            xmlHttpRequest.send(xml);

    Thanks much.

    -tri



    • Edited by triangular Monday, December 1, 2014 9:21 PM
    Monday, December 1, 2014 7:58 PM