FetchXML works fine in Organization.svc but doesn't work in WebAPI

(0) Share

Report

Posted on by Community Member

Hello,

I seem to have some fetchxml that is working inside of the Organizations.svc (SOAP?) using XrmToolBox. I take this same fetchXML and i try to apply it to the Webapi using the /api/data/v8.1/$batch... Doesn't seem to work at all.

Is there a reason why fetchxml doesn't work consistently?

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

ashlega 34,477 on at

Like (0)

Report

Hi,

what is the url you are using to run fetchxml? Should something like this:

https://ORG_URL/api/data/v8.1/contacts?fetchXml=<fetch mapping='logical' output-format='xml-platform' version='1.0' distinct='false' > <entity name='contact' > <attribute name='fullname'/> </entity></fetch>

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
function BuildBatchBody(getString) { var body = '--batch_contactfetch\n'; body += 'Cache-Control: no-cache, no-store, must-revalidate\n'; body += 'Pragma: no-cache\n'; body += 'Expires: 0\n'; body += 'Content-Type: application/http\n'; body += 'Content-Transfer-Encoding: binary\n'; body += 'Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"\n'; body += '\n'; body += getString; body += 'Content-Type: application/json\n'; body += 'OData-Version: 4.0\n'; body += 'OData-MaxVersion: 4.0\n'; body += 'Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"\n'; body += 'Accept: Application/json; odata=verbose\n'; body += '\n'; body += '--batch_contactfetch--'; return body; } serverURL = parent.Xrm.Page.context.getClientUrl(); var body = BuildBatchBody('GET ' + serverURL + '/api/data/v8.0/new_xxx?fetchXml=' + encodeURI(targetXmlEmployee) + ' HTTP/1.1\n'); $.ajax({ type: "POST", url: serverURL + "/api/data/v8.1/$batch", headers: { 'Cache-Control': 'no-cache, no-store, must-revalidate', 'Pragma': 'no-cache', 'Expires': '0', 'OData-Version': '4.0', 'Accept': 'Application/json; odata=verbose', 'Prefer': 'odata.include-annotations="OData.Community.Display.V1.FormattedValue"' }, contentType: 'multipart/mixed;boundary=batch_contactfetch', data: body, }).then(function (responseBig) { debugger; var response = JSON.parse(responseBig.substring(responseBig.indexOf('{'), responseBig.lastIndexOf('}') + 1)); return response.value; });

Seems to work great when I don't include the <order attribute> inside of the fetchXML.

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report

Hi ,Seems somethings is wrong in your fetchxml, it's should work with order attribute.

Please refer below article.May be something is missing in your fetch xml. Hope this helps.

www.hbs.net/.../using-fetchxml-in-web-api-queries-via-javascript-f

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Nothing is wrong in my fetchXML. Seems there is a bug to do with serialization when including the order attribute tag.

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report

that might be the reason , do you received any error for this?

Was this reply helpful? Yes No

Community Member on at

Like (0)

Report

<fetch>
    <entity name="new_paramedic" >
        <attribute name="new_lastname" />
        <attribute name="new_firstname" />
        <attribute name="new_lastscheduleddate" />
        <order attribute="new_lastscheduleddate" descending="false" />
        <link-entity name="phonecall" from="new_regardingparamedic" to="new_paramedicid" link-type="outer" alias="pc" >
            <attribute name="new_position" />
            <attribute name="new_eventdate" />
            <attribute name="new_response" />
            <attribute name="createdon" />
            <filter type="and" >
                <filter type="or" >
                    <filter type="and" >
                        <condition attribute="new_eventdate" operator="on" value="2017-07-17T00:00:00-07:00" />
                        <condition attribute="new_shiftenddatetime" operator="on-or-after" value="2017-07-17T19:15:01-07:00" />
                    </filter>
                    <filter type="and" >
                        <condition attribute="new_eventdate" operator="on" value="2017-07-18T00:00:00-07:00" />
                        <filter type="or" >
                            <condition attribute="new_shiftstartdatetime" operator="between" >
                                <value>
                                    2017-07-17T19:15:01-07:00
                                </value>
                                <value>
                                    2017-07-18T18:14:59-07:00
                                </value>
                            </condition>
                            <condition attribute="new_shiftenddatetime" operator="between" >
                                <value>
                                    2017-07-17T19:15:01-07:00
                                </value>
                                <value>
                                    2017-07-18T18:14:59-07:00
                                </value>
                            </condition>
                        </filter>
                    </filter>
                    <filter type="and" >
                        <condition attribute="new_eventdate" operator="on" value="2017-07-19T00:00:00-07:00" />
                        <condition attribute="new_shiftstartdatetime" operator="on-or-before" value="2017-07-18T18:14:59-07:00" />
                    </filter>
                </filter>
                <filter type="or" >
                    <condition attribute="new_response" operator="eq" value="100000000" />
                    <condition attribute="new_response" operator="eq" value="100000001" />
                    <filter type="and" >
                        <condition attribute="new_response" operator="eq" value="100000005" />
                        <condition attribute="new_regardingappointment" operator="eq" value="{178CDB32-6C87-E711-8121-0050562F10B0}" />
                    </filter>
                    <filter type="and" >
                        <condition attribute="new_response" operator="eq" value="100000008" />
                        <condition attribute="new_regardingappointment" operator="eq" value="{178CDB32-6C87-E711-8121-0050562F10B0}" />
                    </filter>
                </filter>
            </filter>
        </link-entity>
        <link-entity name="so_availability" from="so_regardingemployee" to="new_paramedicid" link-type="outer" >
            <attribute name="so_fromdate" />
            <attribute name="so_todate" />
            <attribute name="so_regardingemployee" />
            <filter type="and" >
                <condition attribute="statecode" operator="eq" value="0" />
                <condition attribute="so_fromdate" operator="le" value="7/18/2017" />
                <condition attribute="so_todate" operator="ge" value="7/18/2017" />
            </filter>
        </link-entity>
        <link-entity name="new_holiday" from="new_regarding" to="new_paramedicid" link-type="outer" >
            <attribute name="new_fromdate" />
            <attribute name="new_todate" />
            <attribute name="new_regarding" />
            <filter type="and" >
                <condition attribute="statecode" operator="eq" value="0" />
                <condition attribute="new_fromdate" operator="le" value="7/18/2017" />
                <condition attribute="new_todate" operator="ge" value="7/18/2017" />
            </filter>
        </link-entity>
        <filter type="and" >
            <condition attribute="statecode" operator="eq" value="0" />
            <condition entityname="so_availability" attribute="so_availabilityid" operator="null" />
            <condition entityname="new_holiday" attribute="new_holidayid" operator="null" />
            <condition attribute="new_post" operator="eq" value="100000001" />
            <filter type="or" >
                <condition entityname="phonecall" attribute="activityid" operator="null" />
                <filter type="and" >
                    <condition entityname="phonecall" attribute="new_response" operator="ne" value="100000000" />
                    <condition entityname="phonecall" attribute="new_response" operator="ne" value="100000001" />
                </filter>
            </filter>
            <filter type="and" >
                <condition attribute="statecode" operator="eq" value="0" />
                <condition attribute="new_platoon" operator="ne" value="C" />
                <condition attribute="new_platoon" operator="ne" value="2" />
                <condition attribute="new_platoon" operator="ne" value="A" />
                <condition attribute="new_platoon" operator="ne" value="5" />
                <condition attribute="new_platoon" operator="ne" value="B" />
                <condition attribute="new_platoon" operator="ne" value="1" />
                <filter type="or" >
                    <condition attribute="new_platoon" operator="ne" value="E" />
                    <filter type="and" >
                        <condition attribute="new_platoon" operator="eq" value="E" />
                        <filter type="or" >
                            <condition attribute="new_workmondayto" operator="null" />
                            <condition attribute="new_workmondayto" operator="le" value="100001915" />
                        </filter>
                        <filter type="or" >
                            <condition attribute="new_worktuesdayfrom" operator="null" />
                            <condition attribute="new_worktuesdayfrom" operator="ge" value="100001814" />
                        </filter>
                        <filter type="or" >
                            <condition attribute="new_worktuesdayto" operator="null" />
                            <condition attribute="new_worktuesdayto" operator="le" value="100001915" />
                        </filter>
                        <filter type="or" >
                            <condition attribute="new_workwednesdayfrom" operator="null" />
                            <condition attribute="new_workwednesdayfrom" operator="ge" value="100001814" />
                        </filter>
                    </filter>
                </filter>
                <condition attribute="new_pcp" operator="eq" value="true" />
            </filter>
        </filter>
    </entity>
</fetch>

-^ This works over FetchXML tester.

It wont work over the WebAPI (resource not found error). If I take out the order attribute:

        <order attribute="new_lastscheduleddate" descending="false" />

It works fine.

Was this reply helpful? Yes No

Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report
Seems , the order is not supporting with date format you are using , so could you please try with another attributes.

<order attribute="new_lastname" descending="true" />

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

That did work, but it should work with the <order attribute="new_lastscheduleddate" descending="false" />

I've created new_lastscheduleddate as a:

Data Type: Date And Time

Field Type: Simple

Behaviour: User Local

Format: Date Only

IME Mode: auto

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I noticed if I add in another field that is just a plain text field. It causes the fetchxml query to fail as well. But testing it on the FetchXML tool... works fine.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

What was going on..

<entity name="new_paramedic" >

<attribute name="new_lastname" />

<attribute name="new_firstname" />

<attribute name="new_lastscheduleddate" />

<order attribute="new_lastscheduleddate" descending="false" />

You don't need the <attribute name="new_lastscheduleddate" />

if you have the order attribute (it includes the order attribute in the return).

Was this reply helpful? Yes No

Community site session details

FetchXML works fine in Organization.svc but doesn't work in WebAPI

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

FetchXML works fine in Organization.svc but doesn't work in WebAPI

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates