Querying Data

Vault CDMS uses queries to track inquiries into entered data (for data Items and Event Dates) that is missing, out of range, or otherwise questionable. In CDB, you can view queries, open a query on an Item, respond to any existing queries with a comment, and close any queries that don’t require any further action.

In Vault EDC, there are two types of queries: system and manual queries. Vault generates system queries by running checks on user-entered data and creating a query when data doesn’t meet specifications defined during study design. For example, Vault creates a system query when an entered date is outside of a certain range. Manual queries are those created by a clinical research associate (CRA), a data manager, or a clinical coder. Any query-related actions that you perform within CDB also apply within Vault EDC. For example, if you create a query on an Item in CDB, that query displays on the Item in EDC.

You can also create manual queries on third party data, but those queries only exist within Vault CDB. To return queries to the third party data source, you can export your queries and manage the collaboration with the third party data provider. When responding to a third party query in CDB, you can record the response from the third party data provider to set the query’s status to Answered, and then once the data point is corrected, you can close the query.

Viewing Queries in Workbench

To view queries in CDB Workbench, select a Study, and then navigate to the Queries page, either using the Study menu or the Navigation Drawer. This opens the Queries page on the Core tab.

By default, Workbench includes this set of query listings for each Source in your study. For example, if your study has two sources, EDC and Labs, Workbench generates two sets of listings and labels them with the source name.

• Closed Queries: Lists all Queries in the Closed status from this data source in your Study
• Outstanding Queries: Lists all Queries in the Open or Answered statuses from this data source in your Study
• Query Messages: Lists all Queries from this data source in your Study

The Closed Queries and Outstanding Queries listings contain the following columns:

Property	CQL	Description
ID	@QRY.ID	The id autogenerated for the query.
Name	@QRY.Name	The Name of the query as defined in EDC (VV#####). Note that for third party data, this number is still a unique identifier.
Status	@QRY.Status	The current Status of the query (Open, Answered, or Closed)
Category	@QRY.Category	The origin of the query (Manual or System-generated)
FirstMessage	@QRY.FirstMessage	The first Query Message created in the query (comment entered when opening the query). This is not a list of all query messages (comments) for a query.
LatestMessage	@QRY.LatestMessage	The latest (most recent) Query Message created in the query (comment entered when responding to the query). This is not a list of all query messages (comments) for a query.
CreatedDate	@QRY.CreatedDate	The date and time the query was created (opened).
CreatedBy	@QRY.CreatedBy	The username of the user who created (opened) the query.
LastModifiedDate	@QRY.LastModifiedDate	The date and time when the query was last modified.
LastModifiedBy	@QRY.LastModifiedBy	The username of the user who last modified the query.
ItemName	@QRYItemName	The Name of the Item that the query was opened on. If the query is on the Event Date, this returns null.
EventName	@QRY.EventName	The Name of the Event associated with the query.
EventDate	@QRY.EventDate	The Event Date of the Event associated with the query.
SubjectName	@QRY.Subject	The Name of the Subject associated with the query.
SubjectStatus	@QRY.SubjectStatus	The Subject Status of the Subject associated with the query.
Site	@QRY.Site	The Site assigned to the Subject associated with the query.
SiteCountry	@QRY.SiteCountry	The Study Country of the Site assigned to the Subject associated with the query.
Form	@QRY.Form	The Name of the Form associated with the query. If the query is on the Event Date, this returns null.
FormSeqNbr	@QRY.FormSeqNbr	The Sequence Number of the Form associated with the query. If the query is on the Event Date, this returns null.
FormSource	@QRY.FormSource	The Source that the query’s data point originated from, for example, “Lab”, “EDC”, or “eCOA”.
ItemGroupSeqNbr	@QRY.ItemGroupSeqNbr	The Sequence Number of the Item Group associated wtih the query. If the query is on the Event Date, this returns null.
OriginSystem	@QRY.OriginSystem	The Name of the Source System where the query originated (EDC, CDB, etc.). In the current release, CQL only returns the value “CDB” for this property for queries opened via Checks in CDB.
OriginName	@QRY.OriginName	The Name of the Listing where the query was opened from. If the query wasn’t opened in CDB, this retrns null.
OpenToReply	@QRY.OpenToReply	Whether the Query is open to reply.
RuleID	@QRY.RuleID	The ID of the Rule that generated the query (for system-generated queries).
TriggerID	@QRY.TriggerID	The ID of the custom Trigger that generated the query.
CausedDataChange	@QRY.CausedDataChange	Whether the Query resulted in a data change.
RowExternalID	@QRY.RowExternalID	The Row External ID assigned to the row during data import.

The Query Messages listings contain the following columns:

Property	CQL	Description
ID	@QRY.ID	The id autogenerated for the query.
SubjectName	@QRY.SubjectName	The Name of the Subject associated wtih the Query.
Status	@QRYMSG.Status	The Status of the Query Message (Open, Answered, Closed).
Text	@QRYMSG.Text	The text of the message.
CreatedDate	@QRYMSG.CreatedDate	The date and time that the query message was created.
CreatedBy	@QRYMSG.CreatedBy	The username of the user who created the query message.

Sorting the Query Listing

In both the Outstanding and Closed subtabs, you can sort the query listing.

To sort the query listing:

1. Hover over the column header for the column you want to sort by.
2. Click Sort (filter_list) to open the Sort menu.

3. Select Ascending or Descending.

4. Click OK.

If a column already has a sort order applied, Workbench shows the Sort icon ( for ascending or for descending). You can click these in the Column Header to change or remove the sort order.

To reset (remove) the sort order, open the Sort menu and click Reset.

Queries in the Cell Details Panel

You can also work with individual queries from listings in the Cell Details panel. To open the Cell Details panel, click the Cell you want to view in the listing’s datasheet. To close the panel, click Close () or click the Cell a second time.

You can drag the top edge of the Cell Details panel to resize it, or click Pop Out () to open it in a new window.

Query Statuses

Queries in Vault CDMS have three (3) statuses: Open, Answered, and Closed. These statuses indicate which actions have been taken and what still needs to occur on a query.

Status	Meaning
Open	Open indicates that a query has not been addressed. This is the status assigned to all newly created queries.
Answered	Answered indicates that a query has been addressed. For example, the site has responded with the reason that the queried value is out of range.
Closed	Closed indicates that a query requires no further action or discussion. A CRA or data manager can reopen a closed query in Vault EDC if needed.

Syncing Query Statuses with EDC

Vault updates the Query Status of a query to match EDC whenever you click to view that query in Workbench’s Query Details panel. Vault also updates queries and imports any newly created queries from EDC during the Workbench Export job and subsequent import in Workbench, which typically runs each night.

If you have an open query in Workbench, and a Vault EDC user has answered the query since the last time you viewed that query in the Query Details panel (but before the next import run), Workbench updates the query’s status to Answered when you view that query’s details in Workbench.

Opening a Query

You can open a new query from the Cell Details panel within a listing. Note that in the current release, you can only create queries on EDC Items. To create a query on an Event Date, return to the Review tab in EDC to do so.

To open a query:

1. Navigate to the listing.
2. Click the Cell for the Item on which you want to open a query.
3. Click Create Query.

4. Enter a comment in the text field.

5. Click Save.

You can open multiple queries in a single action from a listing.

1. Navigate to the listing.
2. Select the Checkboxes for the rows you want to query.

3. Optional: To select all rows on the current page, select the Select All checkbox.

   

4. Click Create Query .

   

5. Enter a Query Message.
6. Click Submit.

Responding to a Query

You can respond to queries on execution data with a comment. Note that you can’t respond to queries when the related data Item or Event Date is locked. Your response then displays in the Query Details and Cell Details panel, as well as the Last Message column of the query listing. If there are now more than two (2) responses to the query, CDB lists the responses in chronological order, from oldest to newest.

From a Query Listing

To respond to a query:

1. Navigate to your Study in Queries > Core.

2. Click a Query to view it in the Query Details panel.

3. Enter your query response.
4. Click Reply.

Vault adds your comment to the query.

From the Cell Details Panel

To respond to a query:

1. Navigate to the listing.
2. Click the Cell for the Item containing the query.
3. Enter a comment.
4. Click Reply.

Vault adds your comment to the query.

Respond to Multiple Queries

You can respond to multiple queries with the same message in one action from a query listing.

1. Navigate to a query listing.
2. Select the Checkboxes for the rows containing queries that you want to respond to.

3. Optional: To select all rows on the current page, select the Select All checkbox.

   

4. Click Reply Query .

   

5. Enter a comment.

   

6. Click Save.

Closing a Query

Once a system or manual query is resolved, and no further actions are required, you can close a query. Note that you can’t close a query when the related data Item or Event Date is locked. Closing a query moves it from the Outstanding queries list to the Closed queries list. Refresh the page to see this change. Closing a query also removes any query-related cell highlighting from listings referencing the Item or Event Date.

From a Query Listing

To close a query:

1. Navigate to your Study in Queries > Core.

2. Click a Query to view it in the Query Details panel.

3. Optional: Enter a comment.
4. Click Close.

From the Cell Details Panel

To close a query:

1. Navigate to the listing.
2. Click the Cell for the Item on which you want to close a query.
3. Optional: Enter a comment.
4. Click Close.

Close Multiple Queries

You can close multiple queries in a batch action from both query listings and data listings.

To close multiple queries from a query listing:

1. Navigate to the listing.
2. Select the Checkboxes for the rows containing queries you want to close.

3. Optional: To select all rows on the current page, select the Select All checkbox.

   

4. Click Close Query .

   

5. Optional: Enter a comment.

   

6. Click Save.

To close multiple queries from a data listing:

1. Navigate to the listing.
2. Select the Checkboxes for the rows containing queries you want to close.

3. Optional: To select all rows on the current page, select the Select All checkbox.

   

4. Click Close Query . This opens the Close Query panel.

   

5. For Close Query on, select either the Event Date or the Item that you want to close queries on.

   

6. Optional: Enter a comment in Message Details.

   

7. Click Close.

System Queries

Vault CDMS runs a check and creates a system query on any item value that is out of a defined range. That range is defined during study design.

The following system query types are defined automatically:

• Required: When creating an Item Definition, a study designer can choose to mark a field as required. If this field is left empty when a form is submitted, Vault creates a system query (“This field is required.”).
• Range: When creating an Item Definition, a study designer can define a range for a field value. If the entered value is outside of that range, Vault creates a system query (“Value is out of range.”). Note that this query type is only for numeric values, not dates.
• Future Date: When creating an Item Definition, a study designer can disallow future dates. If a user enters a future date, Vault creates a system query (Future dates are not allowed.).
• Future Date (Event Dates): When creating an Event Definition, a study designer can indicate that future dates aren’t allowed. Vault can then create a system query on any Event Date that is later than the current date when entered.
• Out of Window (Event Dates): When creating an Event Definition, a study designer can create a window of allowed dates for an Event. If configured, Vault can create a system query any time an Event Date falls outside of this window.

Study designers can define additional data validation rules in Studio. Based on those rules, Vault CDMS can create additional system-generated queries.

Exporting Queries

You can export all outstanding or closed queries in your study as a CSV file for documentation and external review. The CSV file contains the same columns as the query listing, and any sorting or filters you apply in the listing also apply to the exported list.

To export queries:

1. Navigate to the Queries page for your Study.
2. Click the Title of the query listing you want to export.

3. From the Queries () menu, select Generate CSV.

4. In the CSV generated successfully notification, click CSV generated to download the CSV file.