REST API with Pagination (MoreApp)

Hi all! This morning, I retrieved data with the REST API of MoreApp.com. The API is quite straightforward. I encountered some challenges that I'd like to share with you. If there’s anything incorrect in this description, please let me know so I can adjust it.

Preparation

I am using the TX REST connector version 7.1.0.0.
You will also need an API key, which must be created by the administrator in the app.
The data I need to retrieve is located at the base URL: https://api.moreapp.com/api/v1.0. The specific data I am fetching is "submissions," or filled-out surveys. The POST API used to fetch this data is structured as follows:
https://api.moreapp.com/api/v1.0/customers/{customerId}/forms/{formId}/submissions/filter/{page}

• Customer is fixed.
• FormId needs to be looked up.
• Page is required for pagination.

Authentication is achieved by including the X-Api-Key in the header.

Step 1 – Retrieve the correct FormIds

You can do this via Postman or TimeXtender. It is a simple GET API:
https://api.moreapp.com/api/v1.0/forms/customer/{customerId}/forms
This provides a list of available forms. You’ll need to note down the ID for the form you want to fetch "submissions" from, as it will be required in the POST API.

Step 2 – Fetch the submissions

Once you have the {customer} and {formId}, you can fetch the first page (page 0). The endpoint should be filled as follows:
/customers/{customerId}/forms/{formId}/submissions/filter/{page}

It is a POST API, and the API requires you to include a POST body. You can refer to the MoreApp documentation for this body. Set the HTTP method to POST. An example body:

{
  "query": [
    {
      "path": "info.date",
      "type": "date",
      "value": {
        "start": 1704067200000
      }
    }
  ]
}

Step 3 – Table Flattener

The data you retrieve will be a nested JSON file. You can flatten it using the Table Flattening feature. However, before doing so, I recommend enabling caching to a file instead of in-memory. This ensures that the output is written to a file when you click "Test Connection."

1. Click on Test Connection and copy a small bit of the output (Data_.raw) to the top section of the Table Flattener to get the structure.
2. Click on Get Schema.
3. For this form, I drag /TX_Autogenerated_Root/TX_Autogenerated_Element/elements/id to the ROOT section.
4. Then, drag the required columns into the columns section.

Finally:

• Click on Generate Output > Save and Close.
• You now have a flat table.
• I enabled the checkbox Only list flattened tables to avoid unnecessary data in my connector since I only need this table.

Step 4 – Pagination

By default, there are 50 results per page. The first page number is 0 (https://api.moreapp.com/api/v1.0/customers/{customerId}/forms/{formId}/submissions/filter/0), the second end with 1, and so on.

To enable pagination, I configured the following settings:

Parameter:

• Name: stopCondition

• Type: XPath
• Value: (/TX_Autogenerated_Root/TX_Autogenerated_Element/elements/id)[1]

This value depends on what you have defined as ROOT in the Table Flattener. It ensures the pagination loop stops when the value is empty.

Since I use Replace URL with {nextPage} as the Replaced URL, I first defined this value in the SQL expression section:

SELECT 'https://api.moreapp.com/api/v1.0/customers/{customer}/forms/{formId}/submissions/filter/' + CAST({TX_NextPage} as varchar(3)) as nextPage

This SQL builds the next page's URL as a string.

• I use the TX function {TX_NextPage}, which starts at 1 and increments by 1 with each iteration.
• Alternatively, {TX_CurrentPage} can be used; it behaves similarly but starts at 0.
  This command is executed on the SQL server.

When filling in the connection details of the SQL server, start the Connection String with server= followed by the server location.

Parameter Action:

• Choose Replace URL with the Replaced URL value {nextPage} fetched from the SQL request.

Additional Notes

The "submissions" POST API requires an extra header:

• "Content-Type": "application/json"

The GET API for Forms does not work if you include this header. To handle this, I used Override Header for my Forms endpoint, including only the API Key.

Once you’ve tested everything and confirmed it works, don’t forget to disable caching to a file.