Search Overview
Searching in VBAPI is done using a search paradigm involving a SearchConfig, a SearchCriteria, and a SearchRequest. Each is explained below with examples.
SearchConfig
A SearchConfig is a pre-defined grouping of tables and columns. This grouping is used as the base for the search from which data will be queried. For example, if you list all SearchConfigs, you will find that the SearchConfig named CLAIMBATCH joins together the tables CLAIMBATCH, CLAIMBATCHDETAIL, CLAIMBATCHES, PROVIDER, MEMBERBENEFICIARY, MEMBERS, and PROCEDURECODE. Running a search using this SearchConfig will be able to filter and return any column from these tables.
SearchCriteria
A SearchCriteria can be thought of as the where clause of a SQL statement. This is the component that holds the filtering rules that are applied against a SearchConfig.
Think about this in the context of building multiple rows in which each row is a specific criteria filter. The criteria filters may be linked together using the AND/OR value at the end. In the example below, both criteria must be met.
You may also apply left/right parenthesis to group criteria filters. The example below is similar to the previous example but it now wraps the first two filters in left/right parenthesis. It also includes an OR with an additional pair of criteria filters. This means that the results will be included if the data row matches either the first set of criteria filters OR the second set.
Example
Below is a Criteria filter that will retrieve all records in which the Procedure_Code is 99212.
{
"searchConfigId": "CLAIMBATCH",
"criteria_Key": 84,
"criteria_Type": null,
"criteria_User": null,
"description": "Claim Batches with Proc Code 99212",
"criteriaDetails": [
{
"criteria_Key": 84,
"criteria_Seq": 1,
"criteria_Left_Paren": null,
"criteria_Table": "CLAIMBATCHDETAIL",
"criteria_Column": "Procedure_Code",
"criteria_Data_Type": "string",
"criteria_Operator": "=",
"criteria_From_Value": "99212",
"criteria_Thru_Value": null,
"criteria_Right_Paren": null,
"criteria_AndOr": null
}
],
"temporary": false
}SearchRequests
A SearchRequest applies the criteria filter to the SearchConfig and specifies the columns to be returned. For this example, assume that our SearchConfig is CLAIMBATCH and the SearchCriteria we created was ID number 84. To run this search and return information about the Member, Claim, and Procedure Code, the request body might look something like this:
Example
{
"criteria_Key": 84,
"searchConfigId": "CLAIMBATCH",
"selectedFields": [
{
"tableName": "Members",
"columnName": "First_Name"
},
{
"tableName": "Members",
"columnName": "Last_Name"
},
{
"tableName": "Members",
"columnName": "VIP_Flag"
},
{
"tableName": "ClaimBatch",
"columnName": "Batch_Claim"
},
{
"tableName": "ClaimBatch",
"columnName": "Batch_Number"
},
{
"tableName": "ProcedureCode",
"columnName": "Procedure_Code"
},
{
"tableName": "ProcedureCode",
"columnName": "Procedure_Group"
},
{
"tableName": "ProcedureCode",
"columnName": "Description"
}
]
}Sorting
In VBAPI, search results can be sorted based on the columns specified in the SearchRequest object. The results are sorted in ascending order using the first table and column name provided in the selectedFields array of the SearchRequest.
Sorting Logic
The sorting logic is straightforward: the results are automatically sorted in ascending order by the first table/column name combination listed in the selectedFields.
Example
Consider the following SearchRequest object: In this example, the search results will be sorted in ascending order based on the First_Name column from the Members table, as it is the first table/column combination listed in the selectedFields.
{
"criteria_Key": 84,
"searchConfigId": "CLAIMBATCH",
"selectedFields": [
{
"tableName": "Members",
"columnName": "First_Name"
},
{
"tableName": "Members",
"columnName": "Last_Name"
},
{
"tableName": "Members",
"columnName": "VIP_Flag"
},
{
"tableName": "ClaimBatch",
"columnName": "Batch_Claim"
},
{
"tableName": "ClaimBatch",
"columnName": "Batch_Number"
},
{
"tableName": "ProcedureCode",
"columnName": "Procedure_Code"
},
{
"tableName": "ProcedureCode",
"columnName": "Procedure_Group"
},
{
"tableName": "ProcedureCode",
"columnName": "Description"
}
]
}Key Points
- Default Sorting: The default sorting is in ascending order.
- Primary Sort Column: The primary sort column is the first table/column name specified in the selectedFields array.
Implementation Details
The sorting is handled automatically by the API based on the first specified field in the SearchRequest. No additional parameters are needed to enable sorting.
This sorting mechanism ensures that the results are organized and predictable based on the primary field of interest specified in the search request.
SearchResults
The results of a SearchRequest will contain the full SQL statement that was used to produce the results, the column mapping for the results, and rows of data that align with the column mapping.
Example
{
"searchConfigId": "CLAIMBATCH",
"criteria_Key": 84,
"search_SQL_Statement": "sql statement omitted for brevity",
"fieldMapping": [
{
"tableName": "Members",
"columnName": "First_Name",
"columnIndex": 0
},
{
"tableName": "Members",
"columnName": "Last_Name",
"columnIndex": 1
},
{
"tableName": "Members",
"columnName": "VIP_Flag",
"columnIndex": 2
},
{
"tableName": "ClaimBatch",
"columnName": "Batch_Claim",
"columnIndex": 3
},
{
"tableName": "ClaimBatch",
"columnName": "Batch_Number",
"columnIndex": 4
},
{
"tableName": "ProcedureCode",
"columnName": "Procedure_Code",
"columnIndex": 5
},
{
"tableName": "ProcedureCode",
"columnName": "Procedure_Group",
"columnIndex": 6
},
{
"tableName": "ProcedureCode",
"columnName": "Description",
"columnIndex": 7
}
],
"results": [
[
"Jane",
"Doe",
"0",
"17",
"4",
"99212",
"5",
"Office/outpt visit, est, prob"
],
[
"John",
"Doe",
"0",
"3",
"566",
"99212",
"5",
"Office/outpt visit, est, prob"
]
// results truncated for brevity...
]
}Pagination
To manage large sets of results, VBAPI supports pagination. This allows you to retrieve results in a paginated manner, ensuring efficient data transfer and handling.
Pagination Headers
The API response includes a custom header X-Pagination that provides details about the pagination status.
Request
Include the following parameters in your SearchRequest object to handle pagination:
- Page: The current page number.
- PageCount: The number of items per page.
Example
{
"criteria_Key": 84,
"searchConfigId": "CLAIMBATCH",
"selectedFields": [
{
"tableName": "Members",
"columnName": "First_Name"
},
{
"tableName": "Members",
"columnName": "Last_Name"
},
{
"tableName": "Members",
"columnName": "VIP_Flag"
},
{
"tableName": "ClaimBatch",
"columnName": "Batch_Claim"
},
{
"tableName": "ClaimBatch",
"columnName": "Batch_Number"
},
{
"tableName": "ProcedureCode",
"columnName": "Procedure_Code"
},
{
"tableName": "ProcedureCode",
"columnName": "Procedure_Group"
},
{
"tableName": "ProcedureCode",
"columnName": "Description"
}
],
"page": 0, // 0-based index for search. 0 = first page
"pageCount": 100
}Response Headers
The X-Pagination header in the response will contain a JSON object with the following properties:
- TotalCount: The total number of items available.
- TotalPages: The total number of pages available.
- CurrentPage: The current page number.
- PageSize: The number of items per page.
Example
X-Pagination: {
"TotalCount": 1000,
"TotalPages": 9,
"CurrentPage": 0,
"PageSize": 100
}