Extract data with the Search API

This playbook is for integrators and data teams who need a bulk extract from VBA, for example a nightly eligibility file for a vendor. It exports every enrollment row for one group, with member name, plan, and plan dates, to a CSV file, paging through the results 750 rows at a time.

The samples read your credentials from environment variables:

The samples read your credentials from environment variables, so nothing secret is written into code. Set them in the terminal you run the samples from:

$env:VBAPI_BASE_URL    = "https://vbapi.vbasoftware.com/vbasoftware"
$env:VBAPI_API_KEY     = "your-api-key"
$env:VBAPI_CLIENT_ID   = "your-client-id"
$env:VBAPI_CLIENT_CODE = "your-client-code"
$env:VBAPI_DATABASE    = "YOUR_DATABASE"
$env:VBAPI_USERNAME    = "your-api-username"
$env:VBAPI_PASSWORD    = "your-api-password"
$env:VBAPI_USER_AGENT  = "YourCompany-Integration/1.0"

In production code, load these from your secrets manager instead.

GET /search-configs/{searchConfigId}

A SearchConfig defines which tables and columns a search can use. The ENROLLMENT config includes Members, SubEnrollment, MemEnrollmentPlan, Groups, Plans, and other enrollment tables (see the SearchConfig reference). SearchConfigs can differ between databases, so the samples first confirm that every column they select exists in yours. The API also rejects a search that selects a table or column the config does not include.

        var config = (await api.GetAsync($"/search-configs/{SearchConfigId}")).Data!;
        var available = config["tableConfigs"]!.AsArray()
            .SelectMany(t => t!["fields"]!.AsArray().Select(f => $"{t["name"]}.{f!["name"]}".ToLowerInvariant()))
            .ToHashSet();
        var missing = SelectedFields
            .Select(f => $"{f.tableName}.{f.columnName}")
            .Where(name => !available.Contains(name.ToLowerInvariant()))
            .ToList();
        if (missing.Count > 0)
            throw new InvalidOperationException($"Not in {SearchConfigId} for this database: {string.Join(", ", missing)}");
        

The samples select these columns:

Sort on columns that together identify a row, as here, so the order is the same on every page you request.

POST /criterias

This is the one write in the playbook: it saves a criteria record that filters on the group. temporary: true keeps it out of the saved searches list. criteria_Operator and the values are combined into the search's filter, so use a plain comparison such as = and set criteria_Data_Type to string, date, or number.

        var criteria = new
        {
            searchConfigId = SearchConfigId,
            criteria_Type = SearchConfigId,
            description = $"DOCS extract for group {GroupId}",
            temporary = true, // not kept as a saved search
            criteriaDetails = new[]
            {
                new
                {
                    criteria_Seq = 1,
                    criteria_Table = "SubEnrollment",
                    criteria_Column = "Group_ID",
                    criteria_Data_Type = "string",
                    criteria_Operator = "=",
                    criteria_From_Value = GroupId,
                },
            },
        };
        var criteriaKey = (await api.PostAsync("/criterias", criteria)).Data!["criteria_Key"]!.GetValue<int>();
        

The request body, as JSON:

{
  "searchConfigId": "ENROLLMENT",
  "criteria_Type": "ENROLLMENT",
  "description": "DOCS extract for group DOCS-GRP1",
  "temporary": true,
  "criteriaDetails": [
    {
      "criteria_Seq": 1,
      "criteria_Table": "SubEnrollment",
      "criteria_Column": "Group_ID",
      "criteria_Data_Type": "string",
      "criteria_Operator": "=",
      "criteria_From_Value": "DOCS-GRP1"
    }
  ]
}

Save from the response: data.criteria_Key (used in steps 3 and 5).

POST /search

criteria_Key, searchConfigId, and selectedFields are required. Paging works like this:

• page starts at 1. Page 0 is accepted but also returns the first page, so a loop that starts at 0 reads the first page twice.
• pageCount is the number of rows per page. It defaults to 750, and the API rejects values above 750.
• Stop when a page returns fewer rows than pageCount. That is the last page.
• The X-Pagination response header is only accurate when the request sets include_Result_Details: true. Without it, TotalCount is 0. The samples do not need totals, so they do not set it.

            var total = 0;
            await using (var writer = new StreamWriter(OutputFile))
            {
                for (var page = 1; ; page++) // pages start at 1; page 0 also returns the first page, so never start at 0
                {
                    var result = (await api.PostAsync("/search", new
                    {
                        criteria_Key = criteriaKey,
                        searchConfigId = SearchConfigId,
                        selectedFields = SelectedFields,
                        page,
                        pageCount = PageCount,
                    })).Data!;
                    var rows = WritePage(writer, result, includeHeader: page == 1);
                    total += rows;
                    if (rows < PageCount)
                        break; // a short page is the last page
                }
            }
            Console.WriteLine($"Wrote {total} rows to {OutputFile}");
            

Each call sends a body like this one:

{
  "criteria_Key": 12345,
  "searchConfigId": "ENROLLMENT",
  "selectedFields": [
    { "tableName": "Members", "columnName": "Subscriber_ID", "sortPriority": 1 },
    { "tableName": "Members", "columnName": "Member_Seq", "sortPriority": 2 },
    { "tableName": "Members", "columnName": "First_Name" },
    { "tableName": "Members", "columnName": "Last_Name" },
    { "tableName": "MemEnrollmentPlan", "columnName": "Plan_ID", "sortPriority": 3 },
    { "tableName": "MemEnrollmentPlan", "columnName": "Plan_Start", "sortPriority": 4 },
    { "tableName": "MemEnrollmentPlan", "columnName": "Plan_End" }
  ],
  "page": 1,
  "pageCount": 750
}

Each result has fieldMapping, which names the table and column for each position, and results, an array of rows where each row is an array of values. Use fieldMapping[].columnIndex to find each column's value in a row rather than assuming the rows follow the order of selectedFields.

Example response (illustrative; field values are fake, and search_SQL_Statement is shortened):

{
  "data": {
    "searchConfigId": "ENROLLMENT",
    "criteria_Key": 12345,
    "search_SQL_Statement": "SELECT ...",
    "fieldMapping": [
      { "tableName": "Members", "columnName": "Subscriber_ID", "columnIndex": 0 },
      { "tableName": "Members", "columnName": "Member_Seq", "columnIndex": 1 },
      { "tableName": "Members", "columnName": "First_Name", "columnIndex": 2 },
      { "tableName": "Members", "columnName": "Last_Name", "columnIndex": 3 },
      { "tableName": "MemEnrollmentPlan", "columnName": "Plan_ID", "columnIndex": 4 },
      { "tableName": "MemEnrollmentPlan", "columnName": "Plan_Start", "columnIndex": 5 },
      { "tableName": "MemEnrollmentPlan", "columnName": "Plan_End", "columnIndex": 6 }
    ],
    "results": [
      ["DOCS-0001", "01", "Sample", "Member", "DOCS-PLN1", "2030-01-01T00:00:00", null],
      ["DOCS-0001", "02", "Sample", "Dependent", "DOCS-PLN1", "2030-01-01T00:00:00", null]
    ]
  },
  "error": null,
  "debug": { "activityID": "00000000-0000-0000-0000-000000000000" }
}

The samples write one header row (Members.Subscriber_ID, Members.Member_Seq, and so on) and then each row, quoting values that contain commas, quotes, or line breaks:

    static int WritePage(StreamWriter writer, JsonNode result, bool includeHeader)
    {
        // fieldMapping says which row position holds each column.
        var columns = result["fieldMapping"]!.AsArray()
            .OrderBy(m => m!["columnIndex"]!.GetValue<int>())
            .ToList();
        if (includeHeader)
            writer.WriteLine(string.Join(",", columns.Select(m => Csv($"{m!["tableName"]}.{m["columnName"]}"))));
        var rows = result["results"]!.AsArray();
        foreach (var row in rows)
            writer.WriteLine(string.Join(",", columns.Select(m => Csv(row![m!["columnIndex"]!.GetValue<int>()]?.ToString()))));
        return rows.Count;
    }

    static string Csv(string? value) =>
        value is null ? "" : value.IndexOfAny(new[] { ',', '"', '\n', '\r' }) >= 0 ? $"\"{value.Replace("\"", "\"\"")}\"" : value;
    

DELETE /criterias/{criteriaKey}

The samples delete the criteria they created, even if the search fails part-way. A delete returns 204 with no body.

            await api.DeleteAsync($"/criterias/{criteriaKey}");
            

POST /cleanup-criteria is a different, administrative operation: it trims all temporary criteria in the database down to the count set in the Control Panel and returns 202. An extract does not need it.

Open the CSV and check that the header row lists the seven columns and that the row count printed by the sample matches what you expect for the group. To get the total from the API, run one search with include_Result_Details: true and an explicit pageCount, and read TotalCount from the X-Pagination header.

• No criteria record: SearchRequest also accepts the filter inline. Send criteria_Key: 0 and put the criteria details in criterias; the API then skips the criteria lookup and passes the inline details to the search. This avoids the write in step 2, but needs confirmation against the live API before you rely on it.
• Check the filter: GET /criterias-where-clause/{criteriaKey} returns the filter that a saved criteria produces, which helps when a search returns no rows.
• Only active enrollments: add a second criteria detail on MemEnrollmentPlan.Plan_End (for example, empty or after today) joined with criteria_AndOr. The right definition of "active" for your clients needs SME confirmation.
• Safe to re-run? Yes. The search only reads data, and each run creates and deletes its own criteria. Each run overwrites the CSV file.

The full runnable samples are in the portal repository under _samples/playbooks/search-extract/: SearchExtract.cs (run with the shared C# runner as SearchExtractPlaybook), search_extract.py, and searchExtract.ts. Each uses the shared client helper for its language and writes enrollments.csv in the current folder.

• Concepts: Search API, Pagination and sorting, Rate limits and performance
• Reference: SearchConfig reference
• Previous playbook: Disenroll a member or group
• Ask the AI: "Using the Extract data with the Search API playbook, write a TypeScript function that exports all claims for one group to CSV."