The Advanced Query / Customization page is used to access customization records from either the FDB Standard table, the VA Custom tables, or both at the same time. This allows you to research existing records, make customizations, or export data.
The Advanced Query / Customization page is accessed in one of two ways.
Click the Advanced Query/Customization tab on the navigation bar near the top of the page. This will open a blank query.
Click a link from one of the summary tables displayed on the Home tab. This will generate a query appropriate to the context of the link that was clicked. In the example below, a query displaying criteria to display the unassigned Drug-Drug Interaction records will be produced.
After values are selected for those two fields, additional fields display in which the user can create their Query.
Fields | A drop-down list of the fields available to be queried against for the Concept selected. Different concepts will have different fields eligible for query. |
Filter | A drop-down list of the filters that can be used in the Query. See Query Filters. |
Value | A blank field into which the user inserts the particular value they are searching for/by |
And/Or | If the user is creating a query searching for one set of criteria AND an additional set of criteria, the user will utilize the 'And' option. After selecting 'And' an additional row of 'Field', 'Filter' and 'Value' will be displayed to be filled out. If the user is creating a query searching for one set of criteria OR another set of criteria, the user will utilize the 'Or' option. See And/Or Usage Example for additional information. |
Query button | The user clicks this to run the written query. |
Add Default DRC Query button | Dose Range Only. Adds two standard criteria (Concept Type = '6' and AGEHIGHINDAYS >= '6570) to the query. |
'Include Historical records' checkbox | The user checks/selects this box to include records in the Query results that are considered historical versions of active records. No action can be taken on them, they can only be viewed. |
'Clear Query' button | The user clicks this if they want to totally remove the Query they have written above. |
'Save Query' button | The user clicks this if they want to save the Query they have written above. |
'Query Name' | The user enters a name for the Query they have written above, and want to save. |
To create a query
To see approved records with an interaction description equal to "anti" or "Lido", build the query as follows:
Field | Filter | Value | And/Or |
---|---|---|---|
Interaction description | Equals | anti | And |
Status | Equals | approved | Or |
Interaction description | Equals | Lido | And |
Status | Equals | approved |
If you build the query below, you will get approved records with an interaction description = "Anti", but you will get all records with an interaction description of "Lido", regardless of status.
Field | Filter | Value | And/Or |
---|---|---|---|
Interaction description | Equals | anti | And |
Status | Equals | approved | Or |
Interaction description | Equals | Lido |
PECS allows you to save a complete query so that you and other PECS users can run a specific query without having to re-build it every time. This feature is not available to users with the Requestor role. Note: the state of the Historical Records check box will not be saved with the query; if desired, it must be re-selected after the query is loaded at run-time.
To save a query
PECS allows you to run a previously saved query with the same Concept and content (VA, FDB, or Both). You can run queries that you have saved or those that other users have saved.
To run a saved query
You can delete queries you have created and saved. Note that the delete operation is immediate; you will not be warned that the query is about to be deleted and there is no undo option.
To delete a Saved Query
A saved query can be renamed by loading it then adding a different name in the Query Name field.
To rename a saved query
You can change the sort order of results of your query by clicking on the column headings in the display grid. Clicking once will display the records in ascending order (A to Z, 1-2-3 etc.) based on the contents of the column of the header you clicked; clicking a second time display the records in descending order (Z to A, 3-2-1, etc.). A small arrow indicates the direction of the current sort.
For VA records, the default sort order is by the ‘Action Date’ value, from newest to oldest. This puts the VA Customizations that have been updated most recently at the top of the returned list. By default, FDB records are displayed in the order they appeared in the update file sent by FDB. However, they can be re-sorted by clicking a column header.
Note: Due to technical database restrictions, not all fields can be used to determine the sort order. For example, Concept ID Description on a Dose Range query cannot be used to sort the query results. Clicking these columns will have no result and the current sort order will be retained.
Query results for both VA and FDB records can be exported to an Excel spreadsheet file.
Note: The maximum number of records that can be exported is 10000. If your query produces more than 10000 results, the first 10000 records will be exported to the spreadsheet and a message indicating the actual number of results will appear on the Criteria tab.
To export the query results
Running a query will sometimes return an error message.
In many cases, the error is caused by the query returning too many results which causes the database to time-out. Try re-writing the query with more specific criteria. For example, enter "Aspirin" as the Concept ID Description in a Dose Range query instead of "a" as the Concept ID Description. This will reduce the number of results and potentially prevent database time-outs. Rule of thumb: always be as specific as possible when creating a query.
An error may also appear if the selected Filter is not appropriate for the data type.