Loading All Data from a Custom Table

When to use this

Scrolling is built for moving and processing large amounts of data, not for showing it on screen. Use it when you need to:

Extract data from tables to VXL Live, for example to deliver a full table into Excel with a workflow.
Integrate with an external system, where a workflow or an external system needs to read every record in a table.

It is not recommended for App Builder or any other user interface. See When you need the data in the UI for what to do instead.

The problem

A search against a custom table returns at most 10,000 records, however you page through the results. If your table holds more than that, a normal search never reaches the records beyond the limit.

The same limit applies in the Vince Platform user interface: the table data viewer cannot show more than 10,000 records.

The solution

Use the scroll feature of the Custom Tables API. Instead of asking for a page by position, you start a scroll and receive a scrollId. You then pass that scrollId in each following request, and every request returns the next page, until all the data has been fetched.

Prerequisites

A custom table with data in it. See Tables.
Access to the Custom Tables API.

Before you start — get the total count

To find out whether you need to scroll at all, and how many pages to expect, get the number of records first. Call the aggregated endpoint without any query parameters:

GET /v1/custom-tables/aggregated/:tableName

The response contains the total number of records in the table:

{
  "count": 8,
  "aggregations": {}
}

To count only the records that match a query, add the same q you plan to use in your search:

GET /v1/custom-tables/aggregated/:tableName?q=whatever

Use the count to decide how to read the data:

10,000 records or fewer: a normal search is enough.
More than 10,000 records: use scroll, as described below. Divide the count by your size to see how many pages to expect. For example, 25,000 records with a size of 2,000 take 13 pages.
After scrolling: compare the number of records you collected with the count, to check that you got everything.

Step 1 — Start the scroll

Call the search endpoint for your table and add scrollMinutes:

GET /v1/custom-tables/search/:tableName?q=whatever&scrollMinutes=5

Parameter	Description
`tableName`	The name of the custom table to read
`q`	The search query. Records that don't match are not returned
`scrollMinutes`	How long, in minutes, the data stays scrollable. Minimum 1, maximum 60
`size`	Optional. The number of records per page. Minimum 1, maximum 2,000, default 1,000

The response contains the first page of records and a scrollId:

{
  "count": 1000,
  "items": [ ... ],
  "scrollId": "abc123"
}

Step 2 — Fetch the next pages

Pass the scrollId from the previous response in the next request:

GET /v1/custom-tables/search/:tableName?q=whatever&scrollMinutes=5&scrollId=abc123

Every request returns the next page. Keep calling the endpoint with the scrollId until all the data has been fetched, which is when a response comes back with no more records in items.

note

When you use scrollMinutes or scrollId, the from parameter is ignored. A scroll always continues from where the previous request ended; you can't jump to a specific position.

Putting it together in a Code step

In a workflow, the natural place to run the scroll is a Code step. The step starts the scroll, keeps asking for pages until a page is empty, and returns the records to the next step.

The step needs two things from the platform:

The API address. Don't hard-code https://api.vince.live. Ask the platform for the address of the current environment with getContext('env', 'apiUrl'), so the same workflow works in development, staging and production. getContext is the Code step's counterpart of $context.env.apiUrl in expressions; see the $context Reference.
Authentication. Create an API Client with a role that can read the table, then set up a Connection that uses its Client ID and Secret. The example below calls it Vince API; use the name of your own Connection. authorizedAxios then authenticates every request for you.

const authorizedAxios = require('authorizedAxios');
const getContext = require('getContext');

module.exports = async function (prevStep, allSteps, trigger) {
    const tableName = 'my_table';

    // 1. Load the Connection and create an authenticated HTTP client
    const connection = await getContext('globals.connection', 'Vince API');
    if (!connection) {
        throw new Error('Connection "Vince API" not found');
    }
    const axios = await authorizedAxios(connection);

    // 2. Get the Vince Platform API address for the current environment
    const apiUrl = await getContext('env', 'apiUrl');
    const url = `${apiUrl}/v1/custom-tables/search/${tableName}`;

    // 3. Start the scroll, then keep asking for the next page
    const params = { q: 'whatever', scrollMinutes: 5, size: 2000 };
    const allRecords = [];
    let scrollId;

    while (true) {
        const res = await axios.get(url, {
            params: scrollId ? { ...params, scrollId } : params
        });

        const { items, scrollId: nextScrollId } = res.data;
        if (!items || items.length === 0) break;

        allRecords.push(...items);
        scrollId = nextScrollId ?? scrollId;
    }

    // 4. Whatever you return becomes this step's output
    return allRecords;
};

caution

A Code step times out after 3 minutes, and everything it returns is stored as the step's output. For very large tables, don't return every record. Reduce or process the records inside the step, or split the work over several steps.

tip

Handle each page as it arrives instead of collecting everything in memory first, especially for very large tables.

Tips

Choose scrollMinutes to fit your processing time. The data is only scrollable for the number of minutes you set, so pick a value long enough to fetch all pages (maximum 60). If it takes longer, start a new scroll.
Use a larger size (up to 2,000) to fetch fewer pages and make fewer requests.
Narrow the query where you can. q limits the scroll to the records you need.
Scroll to process data, not to display it. Don't load more than 10,000 records into a user interface; see When you need the data in the UI.
Scrolling also works for OpenSearch queries. The POST search endpoint accepts scrollMinutes and scrollId as well, for queries that need the full OpenSearch query syntax.

When you need the data in the UI

The table data viewer in Vince Platform can't load more than 10,000 records, and there is no way to page past that limit there.

warning

We absolutely do not recommend loading more than 10,000 records into a user interface, for example in an app. Rendering that many records heavily slows down the browser and makes the user experience poor. Scrolling is meant for moving and processing large amounts of data, not for showing all of it on screen at once.

If people need to work with the full table, send the data somewhere that is built for it:

VXL Live: deliver the data into Excel with a workflow. See Export Data from a REST API to VXL Live for the pattern.
An external system: let the system read the records through the scroll feature of the Custom Tables API, as described above.
An app built in App Builder: not recommended for loading all the data. If the app needs table data, keep what it loads and shows to a manageable size. Let users search and filter, and show a page of records at a time instead of the whole table.

When to use this​

The problem​

The solution​

Prerequisites​

Before you start — get the total count​

Step 1 — Start the scroll​

Step 2 — Fetch the next pages​

Putting it together in a Code step​

Tips​

When you need the data in the UI​

Further reading​