Automating Queries with Checks

CDB includes Checks, which help automate the cleaning process, monitor it, and adjust it as necessary, enabling data managers to focus on discrepancies that truly need detailed review. Checks, like rules in Vault EDC, automate the cleaning process for the most repeatable discrepancies. Each Check can generate queries on form Items or Event Dates that meet the check’s criteria. The check can automatically close queries once the associated data no longer meets the check’s criteria.

Prerequisites

Users with the standard CDMS Data Manager and CDMS Lead Data Manager study roles can perform the actions described below by default. If your organization uses custom roles, your role must grant the following permissions:

Type Permission Label Controls
Functional Permission Workbench Tab

Ability to access and use the Data Workbench application, via the Workbench tab

Functional Permission
Functional Permission View CDB Queries

Ability to access the Queries page and view Queries within CDB

Functional Permission Browse View

Ability to access the Views tab within Workbench and browse Views. Ability to save a View as a Check

Functional Permission Create Listing

Ability to create private listings (includes public listings, export listings, and check listings when combined with the Public Access permission)

Functional Permission Modify Listing

Ability to edit the CQL statement and properties of private listings (includes public listings, export listings, and check listings when combined with the Public Access permission)

Functional Permission Delete Listing

Ability to delete a public listing or check

Functional Permission Public Access

Ability to create or modify a public listing, when combined with the Create Listing and Modify Listing permissions

Functional Permission Generate CSV

Ability to generate a CSV for a listing, view, or check

Functional Permission Edit CQL

Ability to edit the CQL statement for a listing in the CQL Editor

Functional Permission Manage Deployments

Ability to create and manage study Environments and deploy Studies from EDC Tools, manage and deploy vault-level configuration from Tools > System Tools, and manage and deploy listings, checks, and views in CDB

If your Study contains restricted data, you must have the Restricted Data Access permission to view it.

Learn more about Study Roles.


Accessing Checks

You can access the Checks page from either the Study menu () on the Studies page or from the Navigation Drawer() after you’ve selected a Study.

This opens the Checks page. This page has two (2) tabs: flash_on Automated and edit_square Undefined.

Automated lists Checks that have an automated query action defined. Undefined lists Checks that don’t yet have an automated query action defined.

Checks List Columns

The Checks page has a list of all Checks for a Study. This list has the following columns:

Column Description
Title The Title of the check.
Action The Item or Event Date that the query action runs against.
Last Run The date and time the check was last run.
Run Status The status of the check’s most recent run.
Category The Category assigned to the check.
Objective The Objective of the check.
Source The Sources referenced by the check’s CQL statement.
Created The date and time the check was created.
Created By The user name of the user who created the check.
Modified The date and time the check was last modified.
Modified By The user name of the user who last modified the check.
Last Uploaded The date and time the Study was last imported into CDB.
Latest Download The link () to download the last generated CSV of the check listing.
Forms This column lists each Form referenced by the check.
Views This column lists each View referenced by the check. If the check doesn’t reference any Views, this column is blank.

How to Open a Check

From either tab within the Checks page, you can click a check’s Title to open the Check.

You can also open a Check from the Check menu:

  1. Navigate to Checks for your Study.
  2. Locate the Check that you want to open in the list.
  3. Hover over the Title to show the Check menu ().

  4. From the Check menu, select Open.

Search for a Check

You can search for a specific Check. You can search in the Title, Category, Objective, and Description columns. This search uses “contains”.

To search:

  1. Navigate to Checks for your Study.
  2. Click Search.
  3. Select a Column Name to search.

  4. Enter your Search Text.
  5. Click Search or press Enter to search.

  6. Click a view’s Title in the Results section to open it.
  7. Click Close () to close the Search Checks panel.

Sort & Filter

You can sort and filter the Checks page by the following columns:

If a column already has a sort or filter applied, Workbench shows the Sort icon ( for ascending or for descending) and the Filter icon (filter_list). You can click these in the Column Header to edit the sort or filter. You can also sort and filter columns that don’t already have a sort and filter.

How to Sort

To sort the Checks page:

  1. Navigate to the Checks page for your Study.
  2. Locate the column you want to sort by.
  3. In that column, hover to show the Sort & Filter button.
  4. Click Sort & Filter (filter_list).
  5. Click to expand Sort by.
  6. Select Ascending or Descending for the sort order.

How to Filter

To filter the Checks page:

  1. Navigate to the Checks page for your Study.
  2. Locate the column you want to sort by.
  3. In that column, hover to show the Sort & Filter button.
  4. Click Sort & Filter (filter_list).
  5. Click to expand Condition.
  6. Select an Operator. Workbench uses the entered Value and chosen Operator to compare the values within the column. Learn more about the available comparison operators in the CQL Reference.
  7. If required, enter a Value compare values against. Note that you can only use a static value and not a function. For dates, use the YYYY-MM-DD format.
  8. Click OK.

How to Reset a Filter

To reset (remove) a filter from a column, open the Sort & Filter menu and click Clear.

Hide Check Columns

You can hide and show columns as needed without removing them from your check using the Hide Columns option.

This setting persists across the object and Study until you unhide the columns.

CDB represents hidden columns with an orange, dotted line. CDB shows one dotted line for each set of hidden columns (columns next to each other that are all hidden).

To hide a column:

  1. Navigate to your check.
  2. Hover over the Column Header to show the Sort & Filter menu (filter_list).
  3. Click Hide Column.

  4. CDB hides the column. Hidden columns are indicated by a dotted orange line. Click Unhide Columns to show all hidden columns.

Show the CQL for a Check

To show the CQL for a check:

  1. Navigate to Checks for your Study.
  2. Locate the Check that you want to view in the list.
  3. Hover over the Title to show the Check menu ().
  4. From the Check menu, select Show CQL.

  5. CDB opens the CQL statement in a new dialog. When finished, click Close ().

Create a Check

You create Checks by saving a listing, view, or another check as a Check. That listing’s CQL statement is used as the criteria for which records to capture and open queries against.

To create a check:

  1. Navigate to the Listing or View that you want to use to create your check.
  2. From the Listing or View menu (), select Save As > Check.

  3. Enter a Title.
  4. Enter a Short Title.
  5. Select a Category. The default category is Check.
  6. Optional: To create a new category, clear the Category field’s text and enter your category name. If a category with the same name doesn’t already exist, press Enter or select Add Category from the drop-down.

  7. Optional: Enter an Objective and Description.
  8. Click Save.

Copy Checks with Save As

You can also create a Check based on another check using Save As. This doesn’t copy the query action. So, this is useful when you want to use the same check CQL to open queries against multiple items or event dates.

To create a Check based on another check:

  1. Navigate to Checks for your Study.
  2. Locate the Check you want to copy in the list.
  3. From the Check menu, select Save As.
  4. Update the Properties to the values you want for the new Check.
  5. Click Save.

Edit a Check

You can edit Checks. When you save your changes, if a row no longer meets the check’s criteria, CDB will close the queries associated with that row.

See below for details about editing the query action associated with the check.

To edit the check’s properties:

  1. Navigate to Checks for your Study.
  2. Click the Title of the check you want to edit to open it.
  3. From the Check menu, select Properties.

  4. Make the required changes.
  5. Click Save.

To edit the check’s CQL statement:

  1. Navigate to Checks for your Study.
  2. Click the Title of the check you want to edit to open it.
  3. Click CQL Editor.

  4. Make the required changes.
  5. Click Apply.

Delete a Check

You can delete checks, but note that doing so closes all outstanding queries created by the check.

To delete a Check:

  1. Navigate to Checks for your Study.
  2. Locate the Check you want to delete in the list.
  3. Hover over the Title to show the Check menu.
  4. From the Check menu, select Delete.

  5. Enter a Reason for deleting the Check. CDB records this in the deletion log for the Study.

  6. Click Delete.

Once you click Delete, CDB begins closing all queries created by the check that aren’t closed.

Generate a CSV

You can download a check’s results as a CSV for offline review or use in an external system. When Workbench exports check results, it includes all data shown in the check, according to the currently applied CQL statement.

To generate a CSV file of your check:

  1. Open the check.
  2. From the Check menu (), select Generate CSV. Generate CSV action

  3. Workbench begins a job to create the CSV file. When finished, Workbench shows a notification with a link to download the CSV in the bottom-left corner of the screen. You can click this link to download the CSV file. CSV generated notification

Once any user generates a CSV for a check, Workbench shows a download link for that CSV in the Latest Download column of the Checks page. Click the Download icon () to download the CSV file.

Tooltip for Latest Download CSV

Automating Queries

A check will open a query on a chosen Item or Event Date

When Do Checks Run

CDB runs the check once you save, and then CDB will continue to run the check upon deployment and hourly.

Add an Automated Query Action

To add an automated query action to a Check:

  1. Navigate to Checks > Undefined for your Study.
  2. Click the Title of the Check to open it.
  3. From the Actions menu (), select Automated Query Action.

  4. Select an Item or Event Date for Create Query on.
  5. For Exclude Items with Existing Queries, select from Manual, EDC System, and Workbench Check. You can select one, two, or all three. If you don’t select one of these checkboxes, CDB will open a query regardless of any existing queries.
  6. Enter a Query Message.
  7. Click Save.

Once you click save, CDB runs the check. After that initial run, CDB will run the check hourly and upon deployment.

Edit an Action

You can edit the query action associated with a Check.

To add an automated query action to a Check:

  1. Navigate to Checks > Undefined for your Study.
  2. Click the Title of the Check to open it.
  3. From the Actions menu (), select Automated Query Action.

  4. In the Query Action panel, click Edit.

  5. Make the required changes.
  6. Click Save.

Once you click save, CDB runs the check. Future runs of the check will only reference the currently defined action.

If you change the target object, queries on the original target object are automatically closed.

Delete an Action (Set as Undefined)

You can delete a query action by setting the target object as Not Defined, which moves the check into the Undefined page.

To delete an action:

  1. Navigate to Checks > Automated for your Study.
  2. Click the Title of the Check to open it.
  3. From the Actions menu (), select Automated Query Actions.

  4. In the Query Action panel, click Edit.

  5. Select Not Defined for Create Query on.

  6. Click Save.

You can now add a new action to the check.

View Queries Created by a Check

You can view a query listing any Queries created by a Check. From the Query Actions panel, click any one of the query listing links near the top of the panel.

These links open a query listing filtered to only show queries created by the Check and in the given status (if you clicked one of the status links). Queries Generated shows all queries in all statuses created by the check.

Form Change Log

You can download the Form Change Log for a Check to review historical changes to the forms referenced by the Check.

To download the Form Change Log:

  1. Navigate to the Check that you want to review.
  2. From the Check menu (), select Form Change Log.

  3. Workbench begins a job to create the log file. When finished, Workbench shows a notification with a link to download the log in the bottom-left corner of the screen. You can click this link to download.