PowerPlatformConnectors: [BUG] Issue with setting up x-ms-pageable in Custom Connector

Type of Connector

Custom Connector

Name of Connector

Tribe CRM

Describe the bug

I’m building a Custom Connector on the Odata service of Tribe CRM. The default page size is 100 rows. This is also the maximum number of rows that can be retrieved in one call. For listing rows I want to use Pagination to retrieve more rows.

I’ve added the following to the OpenAPI definition to my list rows.

x-ms-pageable:
    nextLinkName: '@odata.nextLink'

By default the Tribe CRM API does not have a @odata.nextLink element. So with Custom Code I construct the correct URL and add the @odata.nextLink in the JSON response. This works OK.

I can now enable pagination in the action in Power Automate and set the threshold to like 5000. But the result is just 200 records, or better said the first two pages.

• First it calls the Custom Connector to retrieve the first page with 100 records. The Custom Code kicks in and adds the next link to the output.
• Then the second page is retrieved immediately from the URL provided by the next Link. It does not call my custom connector or my custom code.

How do I know? I ended up with a Unauthorized error when pagination was enabled, it retrieves OK when a single page is retrieved. When I manually put the correct authorization in the query string when constructing the next link the error went away. So no Authorization headers were added by the Custom Connector. This second page also did not have the nextLink added to the output. Also, I added additional Custom Code for debugging, to make sure that my Custom Code is indeed not called.

Is this a security bug?

No, this is not a security bug

What is the severity of this bug?

Severity 1 - Connector is broken or there’s a very serious issue

To Reproduce

• Create a Custom Connector based on the Tribe CRM Odata api
• Enable paging using x-ms-pageable
• Use Custom Code to add an @odata.nextLink to the output of the List Operation
• Use the connectors in Power Automate and enable paging

Expected behavior

When pagination is enabled, retrieving each page should be handled by the Custom Connector. This includes any authentication, policies or Custom Code that needs to run in order to retrieve the next page correctly.

Environment summary

I have a dev environment in Power Platform where I develop my Custom Connector. I moved the connector to a Sandbox environment. Both environments have the same result.

Additional context

What have I tried and/or found?

• I’ve looked at the x-ms-pageable docs in Autorest This mentioned that you can also supply ‘operationName’. But trying to do this I get a validation error in de Custom Connector editor.

  Specified swagger has the following errors: 'Definition is not valid.  
  Error: 'Critical : paths/~1odata~1{entityName}/get/x-ms-pageable/operationName : 
  The property 'operationName' is not allowed and must be removed.​
  

• I’ve looked at the underlying JSON Schema of the Custom connector here on GitHub. There it document x-ms-pageable as well (and indeed does not include ‘operationName’). It mentions the following as description for the nextlink url:

  The name of the property at the root of the response that has the next link url.
  Usage: Be sure to use the policy template (id: 'updatenextlink') to enable the correct routing of this link.
  

  I’m unable to find any reference or documentation to policy template ‘updatenextlink’.

• I’ve tried adding a ‘<operationName>Next’ operation to my OpenAPI definition, as is indicated being the default operationName on x-ms-pageable. But you cannot have multiple operations on the same path with the same HTTP Verb. So I’m not sure how I would add this to my OpenAPI definition.

  The combination of path and method must be unique to each operation. 
  There are 2 operations sharing this path and method including: ListRecords, ListRecordsNext
  

Who can help me get the paging working for my Custom Connector? What am I doing wrong? It needs to Custom Code in order to add the @odata.nextLink in de response. So it needs to call my Custom Connector to do so.

Swagger definition:

swagger: '2.0'
info:
  title: Tribe CRM
  description: Connector for Tribe CRM
  version: '1.0'
host: api.tribecrm.nl
basePath: /v1/
schemes:
  - https
consumes: []
produces: []
paths:
  /odata/{entityName}:
    get:
      responses:
        '200':
          description: List of Rows Changed
          schema:
            type: object
            properties:
              '@odata.nextLink':
                type: string
                title: ''
                x-ms-visibility: internal
              value:
                type: array
                items:
                  type: object
      summary: List rows
      description: >-
        This action allows you to list the rows in a Tribe CRM table that match
        the selected options.
      operationId: ListRecords
      x-ms-visibility: important
      parameters:
        - name: entityName
          in: path
          required: true
          type: string
          description: Table name
          x-ms-summary: Table name
          x-ms-visibility: important
        - name: $select
          in: query
          required: false
          type: string
          x-ms-summary: Select columns
          description: >-
            Enter a comma-separated list of column unique names to limit which
            columns are listed
          x-ms-visibility: important
        - name: $filter
          in: query
          required: false
          type: string
          x-ms-summary: Filter rows
          description: >-
            Enter an OData style filter expression to limit which rows are
            listed
          x-ms-visibility: important
        - name: $expand
          in: query
          required: false
          type: string
          x-ms-summary: Expand Query
          description: Enter an Odata style expand query to list related rows
          x-ms-visibility: important
        - name: $orderby
          in: query
          required: false
          type: string
          x-ms-summary: Sort By
          description: Columns to sort by in OData orderBy style (excluding lookups)
          x-ms-visibility: advanced
        - name: $top
          in: query
          required: false
          type: number
          x-ms-summary: Row count
          description: Enter the number of rows to be listed (default = 100, max = 100)
          x-ms-visibility: advanced
        - name: $skip
          in: query
          required: false
          type: number
          x-ms-summary: Skip
          description: >-
            Enter the number of rows to be skipped. For pagination use in
            combination with Sort By.
          x-ms-visibility: advanced
definitions: {}
parameters: {}
responses: {}
securityDefinitions:
  oauth2-auth:
    type: oauth2
    flow: accessCode
    authorizationUrl: https://auth.tribecrm.nl/oauth2/auth
    tokenUrl: https://auth.tribecrm.nl/oauth2/token
    scopes:
      read write offline: read write offline
security:
  - oauth2-auth:
      - read write offline
tags: []

Custom Code:

#nullable enable
public partial class Script : ScriptBase
{
	public override async Task<HttpResponseMessage> ExecuteAsync()
	{
		// Fix doing requests from the Custom Connector test pane
		var authHeader = Context.Request.Headers.Authorization;
		Context.Request.Headers.Clear();
		Context.Request.Headers.Authorization = authHeader;

		try
		{
			switch (this.Context.OperationId)
			{
				case "ListRecords":
					var listPaginationProcessor = new ListPaginationProcessor(Context);
					return await listPaginationProcessor.Process(CancellationToken).ConfigureAwait(false);

				default:
					return await Context.SendAsync(Context.Request, CancellationToken).ConfigureAwait(false);
			}
		}
		catch (Exception ex)
		{
			Context.Logger.Log(LogLevel.Critical, ex, "Error while processing Operation ID '{operationId}'", Context.OperationId);
			var response = new HttpResponseMessage(HttpStatusCode.InternalServerError);
			response.Content = new StringContent(ex.ToString());
			return response;
		}
	}

	private class ListPaginationProcessor
	{
		private IScriptContext _context;

		public ListPaginationProcessor(IScriptContext context)
		{
			_context = context;
		}

		public async Task<HttpResponseMessage> Process(CancellationToken cancellationToken)
		{
			// decode so we can fix + to %20
			UriBuilder builder = new UriBuilder(_context.Request.RequestUri!);
			builder.Query = WebUtility.UrlDecode(builder.Query);
			_context.Request.RequestUri = builder.Uri;

			// do the actual request to Tribe CRM with the updated URL
			HttpResponseMessage response = await _context.SendAsync(_context.Request, cancellationToken).ConfigureAwait(false);

			if (!response.IsSuccessStatusCode)
				return response;

			// Build the nextLink URI
			var queryString = HttpUtility.ParseQueryString(builder.Query);

			if (queryString["$orderby"] == null)
				return response;

			JObject content = JObject.Parse(await response.Content.ReadAsStringAsync());
			JArray? rows = content["value"] as JArray;

			if (!(queryString["$top"] is string topString && topString is not null && int.TryParse(topString, out int top)))
				top = rows?.Count ?? 100;

			if (!(queryString["$skip"] is string skipString && skipString is not null && int.TryParse(skipString, out int skip)))
				skip = 0;

			skip += top;

			queryString["$top"] = top.ToString();
			queryString["$skip"] = skip.ToString();
			//queryString["access_token"] = _context.Request.Headers.Authorization.Parameter;

			// Recreate the query string, then decode again because of the + to %20 conversion
			builder.Query = HttpUtility.UrlDecode(queryString.ToString());

			// add the next link only if the current page is a complete page
			if (rows?.Count == top)
			{
				content.AddFirst(new JProperty("@odata.nextLink", builder.ToString()));
				response.Content = CreateJsonContent(content.ToString());
			}

			return response;
		}
	}
}