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.

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, using the Title, Category, Objective, Description, and Source columns. This search uses “contains”.

To search:

1. Navigate to your Study in Workbench.

2. In the top navigation bar, click Search ().

3. By default, Workbench searches across all object types. You can select and clear checkboxes in the Type drop-down to change which objects Workbench is searching.
   

4. Select the Column to search. The default is Title.
5. Enter your Search Text.
6. Click Search or press Enter to search.
7. Click the check Title in the Results section to open it.

Workbench shows your search results in the Results section of the Search panel.

Click Close () to close the Search 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 ().

Favorite Checks

You can mark checks as favorites to easily access them later from the Favorites menu.

To mark a check as a favorite:

1. Navigate to the check that you want to favorite.

2. Click Add to Favorites ().

3. You can now access it from the Favorites menu ().

To remove it from your favorites, click Remove from Favorites (), or click Remove () for it in the Favorites menu.

To access your favorites, click to open the Favorites menu () in the top navigation bar after selecting a Study.

You can click the linked Object Name to open that check.

You can filter the list of objects by type.

Create a Check

1. Navigate to your Study in Workbench.
2. From the Create New menu, select Check.
3. Drag and drop Properties from Available Properties to Selected Properties to add them to your check. You can drag a group of properties, or click Expand to choose individual properties.
4. Click Remove () to remove a property from your check.
5. Click Items in the left-hand Listing Builder menu.
6. Drag and drop Forms from Available Properties to Selected Properties to add them to your check. You can drag an entire Form, or click Expand to choose individual Items on that Form.
7. Click Remove () to remove an item from your check.
8. Click Arrange in the left-hand Listing Builder menu.
9. Drag and drop properties, forms, and items to reorder them.
10. Click Rows in the left-hand Listing Builder menu.
11. Select a Row Structure:
    • By Subject: This option attempts to display data from multiple forms across events in a single row for the subject, independent of the study schedule, and displays all data where subjects match across events. Any selected schedule-related fields, such as Event Date or Event Name are set to null in the listing.
    • By Schedule: This option displays form data from different events on separate rows. We recommend this option if schedule-related fields are required. This is the default option for all core listings.
12. Click Columns in the left-hand Listing Builder menu.
13. Select a Column Structure:
    • Wide (Side by Site): With this option, each column represents a single unique item or item property.
    • Stacked (Union): With this option, each column can contain multiple items or item properties stacked together, where the user can include up to five (5) items and/or item properties per column.
14. Click Aliases in the left-hand Listing Builder menu.
15. Enter a Column Alias for any columns that you want to use non-default headers.
16. Click Sort & Filter in the left-hand Listing Builder menu.
17. Use the Sort & Filter menu to apply sort orders and filters to any columns.
18. Optional: Click Preview to preview your check.
19. Click Validate and Save.
20. Enter a Title for your check. Note that this Title must be unique within your Study.
21. Enter a Short Title.
22. Choose a Category from your check.
23. 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.
24. Enter a Description.
25. Enter an Objective.
26. Click Save. Workbench saves your check and opens it.

Create a Check from an Existing Listing, View, or 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.

If you created your check in the Listing Builder, you can edit it with the Listing Builder or by modifying its CQL statement in the CQL Editor. Otherwise, you can only edit the check’s CQL or its properties.

To modify a check in the Listing Builder:

1. Navigate to your Study in Workbench.
2. Open the Checks page from either the Study menu or the Navigation Drawer.
3. Locate the listing you want to edit.
4. Click the listing’s Title to open it.
5. Click Modify.
6. Make your changes.
7. Optional: Click Preview to preview your listing. This opens a preview of your listing in a new window.
8. Click Validate and Save.

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.

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.

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.

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.