# Search API

This page is for developers who need to read data across related tables in one call, for example groups with their divisions, or claims with member and provider columns. It explains the Search API flow: choose a SearchConfig, describe the filter as criteria, run the search, and read and page through the results.

This changes production data
VBAPI clients connect to production, so the write calls on this page create or change real records in the database you name in `vbasoftware-database`. Try the steps in a non-production database first if VBA has given you one, use clearly fake values while you test, and keep the "Check it worked" calls, which only read data. "Try it" in the API reference also calls production.

Searching only reads data. The one example that writes is [Save criteria for reuse](#save-criteria-for-reuse), which creates a temporary criteria record.

## How it fits together

| Part | What it is | Operations |
|  --- | --- | --- |
| **SearchConfig** | A predefined set of related tables and the columns you may filter on and return. For example, the `GROUP` SearchConfig covers the `Groups` and `GroupDivision` tables. | [`GET /search-configs`](/openapi/vbasoftware/vbasoftware.api/search-config/listsearchconfigs), [`GET /search-configs/{searchConfigId}`](/openapi/vbasoftware/vbasoftware.api/search-config/getsearchconfig) |
| **Criteria** | The filter, like a SQL `WHERE` clause: a list of conditions on table columns. Send it inline with the search, or save it first and refer to it by key. | [`POST /criterias`](/openapi/vbasoftware/vbasoftware.api/criteria/createcriteria), [`GET /criterias/{criteriaKey}`](/openapi/vbasoftware/vbasoftware.api/criteria/getcriteria), [`GET /search-operators`](/openapi/vbasoftware/vbasoftware.api/search-operators/listsearchoperators) |
| **Search request** | The SearchConfig, the criteria, the columns to return, and paging. | [`POST /search`](/openapi/vbasoftware/vbasoftware.api/search-request/searchrequest) |
| **Search result** | The column list (`fieldMapping`) and the rows (`results`). | Returned by `POST /search` |


```mermaid
flowchart LR
    A["Choose a SearchConfig<br/>GET /search-configs"] --> B["Build criteria<br/>inline, or POST /criterias"]
    B --> C["Run the search<br/>POST /search"]
    C --> D["Read fieldMapping<br/>and results"]
    D -->|more pages| C
```

## 1. Choose a SearchConfig

[`GET /search-configs`](/openapi/vbasoftware/vbasoftware.api/search-config/listsearchconfigs) lists the SearchConfigs, optionally filtered with the `functionalArea` query parameter. Each one has an `id`, a `functionalArea`, a `description`, and `tableConfigs`. [`GET /search-configs/{searchConfigId}`](/openapi/vbasoftware/vbasoftware.api/search-config/getsearchconfig) returns one. Its `tableConfigs` list each table `name` and its `fields` (each with a `name` and `type`). Only these tables and columns can be used in that SearchConfig's criteria and selected fields.

The [SearchConfig reference](/developer-portal/reference/search-config-reference) lists every SearchConfig and its tables.

## 2. Build the criteria

Criteria are a list of conditions (`CriteriaDetail` objects), applied in `criteria_Seq` order:

| Field | Meaning |
|  --- | --- |
| `criteria_Seq` | Required. The order of this condition. |
| `criteria_Table` | The table, which must be in the SearchConfig, for example `Groups`. |
| `criteria_Column` | The column in that table, for example `Group_ID`. |
| `criteria_Data_Type` | Must be `string`, `date`, or `number`. |
| `criteria_Operator` | The comparison, for example `=`. [`GET /search-operators`](/openapi/vbasoftware/vbasoftware.api/search-operators/listsearchoperators) lists the operators. |
| `criteria_From_Value` | The value to compare with, always sent as a string. For a range, the start value. |
| `criteria_Thru_Value` | The end value of a range, for operators that take two values. Otherwise omit it. |
| `criteria_AndOr` | How this condition joins the next one: `And` or `Or`. Omit it on the last condition. |
| `criteria_Left_Paren`, `criteria_Right_Paren` | `(` and `)` to group conditions, for example `(x=1 OR x=2) AND (y=1 OR y=2)`. |


There are two ways to send criteria:

- **Inline.** Put the conditions in the search request's `criterias` array and set `criteria_Key` to `0`. Nothing is stored. Use this for one-off searches, so that you do not leave criteria records behind in your database.
- **Saved.** Create a criteria record with [`POST /criterias`](/openapi/vbasoftware/vbasoftware.api/criteria/createcriteria) and pass the returned `criteria_Key` in the search request. Use this when you run the same filter many times. See [Save criteria for reuse](#save-criteria-for-reuse).


## 3. Run the search

A search request needs three fields:

- `searchConfigId`: the SearchConfig `id`, for example `GROUP`.
- `criteria_Key`: `0` with inline `criterias`, or the key of saved criteria.
- `selectedFields`: the columns to return, each a `tableName` and `columnName` from the SearchConfig. Optional `sortPriority` and `sortDescending` set the sort order.


Optional fields set paging (`page`, `pageCount`, `include_Result_Details`; see [Paging](#paging)) and sampling (see [Samples of the results](#samples-of-the-results)).

This request returns the ID and name of group `DOCS-GRP1` and each of its division IDs, sorted by group ID:

C#
```csharp
    static JsonObject BuildSearchRequest() => new()
    {
        ["searchConfigId"] = "GROUP",
        ["criteria_Key"] = 0, // 0 means "use the inline criterias below"
        ["criterias"] = new JsonArray(new JsonObject
        {
            ["criteria_Seq"] = 1,
            ["criteria_Table"] = "Groups",
            ["criteria_Column"] = "Group_ID",
            ["criteria_Data_Type"] = "string",
            ["criteria_Operator"] = "=",
            ["criteria_From_Value"] = "DOCS-GRP1",
        }),
        ["selectedFields"] = new JsonArray(
            new JsonObject { ["tableName"] = "Groups", ["columnName"] = "Group_ID", ["sortPriority"] = 1, ["sortDescending"] = false },
            new JsonObject { ["tableName"] = "Groups", ["columnName"] = "Name" },
            new JsonObject { ["tableName"] = "GroupDivision", ["columnName"] = "Division_ID" }),
        ["page"] = 1,
        ["pageCount"] = 100,
    };
```

Python
```python
search_request = {
    "searchConfigId": "GROUP",
    "criteria_Key": 0,  # 0 means "use the inline criterias below"
    "criterias": [
        {
            "criteria_Seq": 1,
            "criteria_Table": "Groups",
            "criteria_Column": "Group_ID",
            "criteria_Data_Type": "string",
            "criteria_Operator": "=",
            "criteria_From_Value": "DOCS-GRP1",
        }
    ],
    "selectedFields": [
        {"tableName": "Groups", "columnName": "Group_ID", "sortPriority": 1, "sortDescending": False},
        {"tableName": "Groups", "columnName": "Name"},
        {"tableName": "GroupDivision", "columnName": "Division_ID"},
    ],
    "page": 1,
    "pageCount": 100,
}
```

TypeScript
```typescript
const searchRequest = {
  searchConfigId: "GROUP",
  criteria_Key: 0, // 0 means "use the inline criterias below"
  criterias: [
    {
      criteria_Seq: 1,
      criteria_Table: "Groups",
      criteria_Column: "Group_ID",
      criteria_Data_Type: "string",
      criteria_Operator: "=",
      criteria_From_Value: "DOCS-GRP1",
    },
  ],
  selectedFields: [
    { tableName: "Groups", columnName: "Group_ID", sortPriority: 1, sortDescending: false },
    { tableName: "Groups", columnName: "Name" },
    { tableName: "GroupDivision", columnName: "Division_ID" },
  ],
  page: 1,
  pageCount: 100,
};
```

The same request as JSON:

```json
{
  "searchConfigId": "GROUP",
  "criteria_Key": 0,
  "criterias": [
    {
      "criteria_Seq": 1,
      "criteria_Table": "Groups",
      "criteria_Column": "Group_ID",
      "criteria_Data_Type": "string",
      "criteria_Operator": "=",
      "criteria_From_Value": "DOCS-GRP1"
    }
  ],
  "selectedFields": [
    { "tableName": "Groups", "columnName": "Group_ID", "sortPriority": 1, "sortDescending": false },
    { "tableName": "Groups", "columnName": "Name" },
    { "tableName": "GroupDivision", "columnName": "Division_ID" }
  ],
  "page": 1,
  "pageCount": 100
}
```

Send it with `POST /search`:

C#
```csharp
        var response = await api.PostAsync("/search", BuildSearchRequest());
        foreach (var row in RowsAsDictionaries(response.Data!))
            Console.WriteLine($"{row["Groups.Group_ID"]} {row["Groups.Name"]} {row["GroupDivision.Division_ID"]}");
```

Python
```python
        response = api.post("/search", search_request)
        for row in rows_as_dicts(response.data):
            print(row["Groups.Group_ID"], row["Groups.Name"], row["GroupDivision.Division_ID"])
```

TypeScript
```typescript
  const response = await api.post<SearchResult>("/search", searchRequest);
  for (const row of rowsAsObjects(response.data)) {
    console.log(row["Groups.Group_ID"], row["Groups.Name"], row["GroupDivision.Division_ID"]);
  }
```

## 4. Read the results

`data` holds the search result:

| Field | Meaning |
|  --- | --- |
| `searchConfigId` | The SearchConfig that ran. |
| `criteria_Key` | The criteria key from the request (`0` for inline criteria). |
| `search_SQL_Statement` | The SQL that ran. It is filled in only when the request sets `include_Result_Details: true`. |
| `fieldMapping` | One entry per selected field: `tableName`, `columnName`, and `columnIndex`, its position in each row. |
| `results` | The rows. Each row is an array of values in `columnIndex` order. |


Example response (illustrative; field values are fake):

```json
{
  "data": {
    "searchConfigId": "GROUP",
    "criteria_Key": 0,
    "search_SQL_Statement": "",
    "fieldMapping": [
      { "tableName": "Groups", "columnName": "Group_ID", "columnIndex": 0 },
      { "tableName": "Groups", "columnName": "Name", "columnIndex": 1 },
      { "tableName": "GroupDivision", "columnName": "Division_ID", "columnIndex": 2 }
    ],
    "results": [
      ["DOCS-GRP1", "Sample Group One", "DOCS-DIV1"],
      ["DOCS-GRP1", "Sample Group One", "DOCS-DIV2"]
    ]
  },
  "error": null,
  "debug": { "activityID": "00000000-0000-0000-0000-000000000000" }
}
```

Use `fieldMapping` to turn the rows into named records instead of relying on column positions:

C#
```csharp
    // Turns the positional `results` rows into dictionaries keyed by "Table.Column", using `fieldMapping`.
    static List<Dictionary<string, string?>> RowsAsDictionaries(JsonNode result)
    {
        var names = result["fieldMapping"]!.AsArray().ToDictionary(
            f => f!["columnIndex"]!.GetValue<int>(),
            f => $"{f!["tableName"]}.{f["columnName"]}");
        return result["results"]!.AsArray()
            .Select(row => row!.AsArray()
                .Select((value, i) => (Name: names[i], Value: value?.ToString()))
                .ToDictionary(c => c.Name, c => c.Value))
            .ToList();
    }
```

Python
```python
def rows_as_dicts(result):
    """Turn the positional `results` rows into dicts keyed by "Table.Column", using `fieldMapping`."""
    names = {f["columnIndex"]: f"{f['tableName']}.{f['columnName']}" for f in result["fieldMapping"]}
    return [{names[i]: value for i, value in enumerate(row)} for row in result["results"]]
```

TypeScript
```typescript
/** Turns the positional `results` rows into objects keyed by "Table.Column", using `fieldMapping`. */
function rowsAsObjects(result: SearchResult): Record<string, unknown>[] {
  const names = new Map(result.fieldMapping.map((f) => [f.columnIndex, `${f.tableName}.${f.columnName}`]));
  return result.results.map((row) => Object.fromEntries(row.map((value, i) => [names.get(i) ?? String(i), value])));
}
```

## Sorting

Set `sortPriority` on the selected fields you want to sort by: `1` sorts first, `2` second, and so on. Set `sortDescending: true` on a field to sort it in descending order; it only takes effect when that field has a `sortPriority`. Fields without a `sortPriority` are not sorted. If no field has a `sortPriority`, rows are sorted by the first selected field.

## Paging

Search paging has its own rules, different from list operations:

- `pageCount` is the page size. It defaults to 750, and API calls may not ask for more than 750; a larger value is rejected with `400` ("For API calls, PageCount cannot exceed 750 records.").
- Pages start at 1. Page 0 is also accepted and returns the first page, so a loop that starts at 0 reads the first page twice. Start at 1.
- The `X-Pagination` response header (`TotalCount`, `TotalPages`, `CurrentPage`, `PageSize`) is accurate only when the request sets `include_Result_Details: true` and an explicit `pageCount`. Without them, `TotalCount` is `0`.


`include_Result_Details: true` also returns the SQL statement and counts every matching row, which is extra work for the database, so set it when you need the totals. This loop reads every page:

C#
```csharp
    // Yields every row of a search. pageCount may not exceed 750 for API calls.
    static async IAsyncEnumerable<Dictionary<string, string?>> SearchAllAsync(VbapiClient api, JsonObject request, int pageCount = 750)
    {
        for (var page = 1; ; page++) // pages start at 1; page 0 returns the same first page
        {
            var body = JsonNode.Parse(request.ToJsonString())!.AsObject();
            body["page"] = page;
            body["pageCount"] = pageCount;
            body["include_Result_Details"] = true;
            var response = await api.PostAsync("/search", body);
            foreach (var row in RowsAsDictionaries(response.Data!))
                yield return row;
            var totalPages = response.Pagination?["TotalPages"]?.GetValue<int>() ?? 0; // accurate only with include_Result_Details
            if (page >= totalPages)
                yield break;
        }
    }
```

Python
```python
def search_all(api, request, page_count=750):
    """Yield every row of a search. pageCount may not exceed 750 for API calls."""
    page = 1  # pages start at 1; page 0 returns the same first page
    while True:
        body = dict(request, page=page, pageCount=page_count, include_Result_Details=True)
        response = api.post("/search", body)
        yield from rows_as_dicts(response.data)
        total_pages = (response.pagination or {}).get("TotalPages", 0)  # accurate only with include_Result_Details
        if page >= total_pages:
            return
        page += 1
```

TypeScript
```typescript
/** Yields every row of a search. pageCount may not exceed 750 for API calls. */
async function* searchAll(api: VbapiClient, request: object, pageCount = 750) {
  for (let page = 1; ; page++) {
    // pages start at 1; page 0 returns the same first page
    const body = { ...request, page, pageCount, include_Result_Details: true };
    const response = await api.post<SearchResult>("/search", body);
    yield* rowsAsObjects(response.data);
    if (page >= (response.pagination?.TotalPages ?? 0)) return; // accurate only with include_Result_Details
  }
}
```

[Pagination and sorting](/developer-portal/concepts/pagination-and-sorting#search-paging-is-different) compares Search paging with list paging.

## Samples of the results

Instead of every matching row, a search can return a sample:

- `use_Random_Sample: true` with `random_Sample_Count` returns a random sample of that many rows (0 to 1000; limited to the page size, 750 by default). Add `random_Sample_Count_Is_Percent: true` to treat the count as a percentage from 0 to 100.
- `use_Nth_Result: true` with `nth_Result` returns every nth row.


## Save criteria for reuse

To reuse a filter, create it once with [`POST /criterias`](/openapi/vbasoftware/vbasoftware.api/criteria/createcriteria), then search with the returned `criteria_Key` and no `criterias` array. This writes a criteria record to your database.

C#
```csharp
        var criteria = (await api.PostAsync("/criterias", new JsonObject
        {
            ["searchConfigId"] = "GROUP",
            ["criteria_Type"] = "GROUP",
            ["description"] = "DOCS sample: one group",
            ["temporary"] = true,
            ["criteriaDetails"] = new JsonArray(new JsonObject
            {
                ["criteria_Seq"] = 1,
                ["criteria_Table"] = "Groups",
                ["criteria_Column"] = "Group_ID",
                ["criteria_Data_Type"] = "string",
                ["criteria_Operator"] = "=",
                ["criteria_From_Value"] = "DOCS-GRP1",
            }),
        })).Data!;
        var savedRequest = BuildSearchRequest();
        savedRequest.Remove("criterias");
        savedRequest["criteria_Key"] = criteria["criteria_Key"]!.GetValue<int>();
        var saved = await api.PostAsync("/search", savedRequest);
        Console.WriteLine($"{saved.Data!["results"]!.AsArray().Count} rows");
```

Python
```python
        criteria = api.post("/criterias", {
            "searchConfigId": "GROUP",
            "criteria_Type": "GROUP",
            "description": "DOCS sample: one group",
            "temporary": True,
            "criteriaDetails": [{
                "criteria_Seq": 1,
                "criteria_Table": "Groups",
                "criteria_Column": "Group_ID",
                "criteria_Data_Type": "string",
                "criteria_Operator": "=",
                "criteria_From_Value": "DOCS-GRP1",
            }],
        }).data
        saved_request = {key: value for key, value in search_request.items() if key != "criterias"}
        saved_request["criteria_Key"] = criteria["criteria_Key"]
        print(len(api.post("/search", saved_request).data["results"]), "rows")
```

TypeScript
```typescript
  const criteria = (
    await api.post("/criterias", {
      searchConfigId: "GROUP",
      criteria_Type: "GROUP",
      description: "DOCS sample: one group",
      temporary: true,
      criteriaDetails: [
        {
          criteria_Seq: 1,
          criteria_Table: "Groups",
          criteria_Column: "Group_ID",
          criteria_Data_Type: "string",
          criteria_Operator: "=",
          criteria_From_Value: "DOCS-GRP1",
        },
      ],
    })
  ).data;
  const { criterias: _inline, ...savedRequest } = searchRequest;
  const saved = await api.post<SearchResult>("/search", { ...savedRequest, criteria_Key: criteria.criteria_Key });
  console.log(`${saved.data.results.length} rows`);
```

The `Criteria` body takes `searchConfigId`, `criteria_Type` (the SearchConfig name), a `description`, `temporary`, and the `criteriaDetails` conditions. Leave out `criteria_Key`; the API assigns it.

- `temporary: true` means the criteria is not listed as a saved search. [`POST /cleanup-criteria`](/openapi/vbasoftware/vbasoftware.api/criteria/cleanupcriteria) removes temporary criteria down to the number set in the VBASoftware Control Panel, and [`POST /cleanup-criteria-by-user`](/openapi/vbasoftware/vbasoftware.api/criteria/cleanupcriteriabyuser) does the same for each user.
- `temporary: false` keeps the criteria as a saved search, following your company's criteria settings.


[`GET /criterias/{criteriaKey}`](/openapi/vbasoftware/vbasoftware.api/criteria/getcriteria) reads saved criteria back, and [`GET /criterias-where-clause/{criteriaKey}`](/openapi/vbasoftware/vbasoftware.api/criteria/getcriteriawhereclause) returns the `WHERE` clause built from it, which helps when a search returns rows you did not expect. [`DELETE /criterias/{criteriaKey}`](/openapi/vbasoftware/vbasoftware.api/criteria/deletecriteria) deletes criteria you no longer need.

## Search errors

| Status | Cause |
|  --- | --- |
| `400` `Invalid Page Count` | `pageCount` is over 750. |
| `422` `Invalid Entity` | A selected field's table or column is not in the SearchConfig (the detail names the entry), `selectedFields` is empty, or `criteria_Key` does not match saved criteria. |


See [Errors and troubleshooting](/developer-portal/concepts/errors) for the error body and other status codes.

## Related

- [SearchConfig reference](/developer-portal/reference/search-config-reference): every SearchConfig and its tables.
- [Extract data with the Search API](/developer-portal/playbooks/search-extract): a complete extract workflow.
- [Pagination and sorting](/developer-portal/concepts/pagination-and-sorting): paging for list operations.