Web API with FetchXML for dropdown listDropdown on custom page

[a365]

Hi all,

I am currently facing an issue while working on a project that involves fetching data from a Web API. Specifically, I am encountering an unexpected error when attempting to include a FetchXML statement in my Web API call.

Here is a brief description of the problem:

• When I make a Web API call using jQuery to fetch data without a FetchXML statement, everything works as expected. Data is retrieved successfully, and there are no issues.
• However, when I include a FetchXML statement in the Web API call to specify more advanced querying requirements, I receive the following error message:

{
    "error": {
        "code": "9004010A",
        "message": "An unexpected error occurred while processing the request"
    }
}

I am using the following code to get my results with fetchxml, currently simplified my fetchXML statement to get it to run, but it will be more complex. What is possibly wrong? If I write a standard OData query, all works fine, I need to have a fetchxml statement as part of my code.

(function (webapi, $) {
    function safeAjax(ajaxOptions) {
      var deferredAjax = $.Deferred();

      shell.getTokenDeferred().done(function (token) {
        // add headers for ajax
        if (!ajaxOptions.headers) {
          $.extend(ajaxOptions, {
            headers: {
              "Accept": "application/json",
              "OData-MaxVersion": "4.0",
              "OData-Version": "4.0",
              "__RequestVerificationToken": token
            }
          });
        } else {
          ajaxOptions.headers["Accept"] = "application/json";
          ajaxOptions.headers["OData-MaxVersion"] = "4.0";
          ajaxOptions.headers["OData-Version"] = "4.0";
          ajaxOptions.headers["__RequestVerificationToken"] = token;
        }
        $.ajax(ajaxOptions)
          .done(function (data, textStatus, jqXHR) {
            validateLoginSession(data, textStatus, jqXHR, deferredAjax.resolve);
          })
          .fail(deferredAjax.reject); //ajax
      }).fail(function () {
        deferredAjax.rejectWith(this, arguments); // on token failure, pass the token ajax and args
      });

      return deferredAjax.promise();
    }

    webapi.safeAjax = safeAjax;
  })(window.webapi = window.webapi || {}, jQuery);

  $(document).ready(function () {
    // Define the URL of your Web API endpoint
    var apiUrl = '/_api/a365_shippingcarriers?$fetchxml=';

    var fetchXmlQuery = `
    <fetch version="1.0" output-format="xml-platform" mapping="logical">
      <entity name="a365_shippingcarrier">
        <attribute name="a365_shippingcarrierid" />
        <attribute name="a365_shippingcarriername" />
      </entity>
    </fetch>`;

    var encodedFetchXmlQuery = encodeURIComponent(fetchXmlQuery);

    // Define additional query parameters, including $fetchXml
    var queryParameters = {
      $select: 'a365_shippingcarriername,a365_shippingcarrierid',
      $top: 10,
      $fetchXml: encodedFetchXmlQuery
    };

    // Make an AJAX request to fetch data from the Web API using the safeAjax function
    webapi.safeAjax({
      url: apiUrl,
      method: 'GET',
      dataType: 'json',
      data: queryParameters,
      success: function (data) {
        console.log('Data from the Web API:', data);

        // Assuming the Web API returns an array of objects with 'id' and 'name' properties
        var dropdown = $('#shippinCarrierList');

        // Loop through the data and add options to the dropdown list
        $.each(data.value, function (index, item) {
          dropdown.append($('<option>', {
            value: item.a365_shippingcarrierid,
            text: item.a365_shippingcarriername
          }));
        });
      },
      error: function (error) {
        console.error('Error fetching data from the Web API:', error);
      }
    });
  });

[Fubar]

Not sure if FetchXML is supported by the Portals Web API or not, but the alternative would be to do it old style.  This will return a JSON object with your fetchxml results (use the Portal / Power Pages Management App)

• Create a Web Template that contains your fetchxml in Liquid and outputs the results as JSON (and receive any request variables you need).  Set the mime type on the Web Template
• Create a Page Template for the the Web Template, turn off the headers and footers, 
• Create a Web Page that uses the new Page template
• On your other page, make a standard Ajax call to the new Web Page

You will find various posts on how to do this the following is one: https://www.inogic.com/blog/2020/11/use-of-json-type-web-templates-in-powerapps-portals/

and another one https://colinvermander.wordpress.com/2017/04/17/dynamics-365-portals-use-liquid-to-return-json-or-xm...

[a365]

I know of using FetchXML with liquid tags, but since it is server based and I need to update my data based on another control it would not work on the page I am on. But making an ajax call on the other webpage, in theory, it can work like I would like it to.

I will try it and let you know.

Thank you.

[nhayduk · Accepted solution]

To use FetchXML via the Power Pages WebAPI, use the fetchXml parameter (not $fetchXml). Also, I seem to get errors if I try to include $select or $top as part of my queries. So select the columns you need as part of your FetchXML statement, and use count in your FetchXML statement if you want to limit the results.

As an example:

url: "/_api/contacts?fetchXml=" + encodeURI('<fetch count="2"><entity name="contact"><attribute name="firstname" /><attribute name="lastname" /><filter><condition attribute="lastname" operator="ne" value="Hayduk" /></filter></entity></fetch>'),

Nick

[a365]

Thank you, that did it! 🙂