Advanced Query/Customization

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.

Accessing the Advanced Query/Customization Page

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.

query_AccessFromTab.png

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.

query_AccessFromHomeTable.png

Top

Build A Query Panel

query_DDIUnassignedBuldExample.png

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.

Top

Create A Query

To create a query

  1. On the Advanced Query/Customization tab, select a Concept.

    Select Concept

  2. Select what data you want to view-- VA, FDB, or Both.

    Select Data Type

  3. In the "Enter a value to build a query" area, select the Field you want to use as a query criteria. The available field options will be determined by the Concept you selected earlier.

    Select Field

  4. Select the Filter you want to impose on the Field. See Query Filters for additional information.

    Select Filter

  5. Enter a Value to use as  your query criteria. The Value must be appropriate for the Field and Filter or an error will be generated. See Query Specifics for additional information.

    Enter Value

  6. To add additional criteria to the query, make a selection from the And/Or list.

    And/Or List

  7. To include Historical Records in the query, select the Include Historical Records check box.
  8. When all criteria have been added, click the Query button. The results will display below the query panel.
  9. To see details of the record, select the link in the Select column. This will allow you to view the details of the record and customize it. Note that Drug Pairs cannot be customized directly; they must be customized through the related Drug-Drug Interaction. Click the link to the related DDI for further action on the drug pair.

    advqry_exportDetail.png

Top

And/Or Usage Example

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  

Query Specifics

  1. Use the YYYY-MM-DD date format for searching date fields within a query
  2. Date values can only use the following filters::

Top

Save a Query

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

  1. Create a query in the Build A Query panel. See Build A Query for additional information.
  2. Enter a name for the query in the Query Name field. The name must contain at least five characters and cannot be longer than 64 characters..  
  3. Click Save Query.

    Save Query Button

  4. The saved query will appear in the My Queries list.

My Queries List

Run a Saved 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

  1. On the Advanced Query/Customization tab, select a Concept.
  2. Select what data you want to view-- VA, FDB, or Both.
  3. In the Run a Saved Query sub-panel, select either My Queries or Other Users's Queries, then select the query you want to run.
  4. Click the Load button. This will add the components of the saved query to the Build a Query panel.

    Load A Saved Query

  5. Click the Query button to run the query. You may also select additional criteria to alter or enhance the saved query.

Delete 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

  1. On the Advanced Query/Customization tab, select a Concept.
  2. Select what data you want to view-- VA, FDB, or Both.
  3. In the Run a Saved Query sub-panel, select My Queries; you cannot delete a query that was created by another user, then select the query you want to delete.
  4. Click the Delete button. The query is deleted.

Rename 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

  1. On the Advanced Query/Customization tab, select a Concept.
  2. Select what data you want to view-- VA, FDB, or Both.
  3. In the Run a Saved Query sub-panel, select My Queries; you cannot  rename a saved query created by another user.
  4. Enter a new the Query Name field.
  5. Click Save Query. The new query name will appear in the My Queries list in place of the original query.

Sorting Query Results

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.

Sort Query Results

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.

 

Export Query Results

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.

10K Query Export Limit Message

To export the query results

  1. After performing a query, click the Export button on the appropriate query results panel.
  2. Export Query Results

  3. Click Open to display the query report;  click Save to save a copy of the report to your system.
  4. Click Open to display, Save to make a local copy

  5. The spreadsheet contains two tabs:

Query Errors

Running a query will sometimes return an error message.

Query 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.