Data Validation Suite - Setup and Usage

Options
Grant_213559
Grant_213559 Posts: 11 admin
in Show and Tell

Catalytic Data Validation Suite is a portable workflow module available for installation in any team. By attaching a validation map to the data spreadsheet, the workflow is able to dynamically validate columns of data. Works out of the box with batteries included!

INPUT SHEET

CONFIGURATION MAP

OUTPUT TABLE

Configuration Table

The Data Validation Suite input table consists of four separate excel sheets within the same document.

Sheet1- Data Columns

Sheet2- Validation Map

Sheet3- Table Map (optional)

Sheet4- Validation Formulas (restricted)

INSTALLATION:

Please download the workflow at the bottom of the post and install into your team.

Also, download the demoTable.xlsx file in order to follow along with the tutorial:

DATA COLUMNS SETUP:

  1. On Sheet1 designate a column for a unique identifier. "Name" is used in the example below. Think of this as the "anchor column".
  2. Add additional columns. Column names must be unique and free of any leading or trailing whitespace, since they will be ultimately mapped to validation rules on Sheet2.
  3. Add data to columns. Example sheet is depicted below with examples of invalid data.


Sheet1

VALIDATION MAP:
1. Open Sheet2 of the same document.
2. In the "ColumnName" column, insert the desired column names from Sheet1.

**IMPORTANT:** The spelling of each respective column name on Sheet1 must match the value on Sheet2, otherwise, the process will fail.


Sheet2

  1. For each column you wish to validate, select a validation from the ValidationType menu. If the data fails to meet the specified condition, the value will be deemed invalid. ValidationType menu options are located on Sheet4 (restricted).
    • Additionally, if you aren't using a parameter, enter "none" (without quotes).
    • IMPORTANT: Make sure the validation type is appropriate for the given data type (string, number, etc.).
    • "Parameter2" is only necessary if using a "between" validation rule.
    • Between rules are evaluated as less than Parameter2 but greater than Parameter1- (Parameter1<Value)&&(Value<Parameter2)
    • For currency columns, please format to a decimal. Example configuration is in the screenshot below.
    • IF USING DATEFORMAT:
      • Enter "none" (without quotes) for ValidationTable, Parameter1, and Parameter2
      • DateFormat uses a date validation formula to check if a date column matches either the MM-DD-YYYY or MM/DD/YYYY format.
      • If the date item doesn't match either format, or has an invalid month and day combination, the output will be deemed invalid.

Sheet2 completed
  1. IF TABLE IS SELECTED: Click over to Sheet3
  2. For any "ColumnName" values with ValidationType set to Table, enter the respective ColumnName as its own column on Sheet3.
    • Similar to previous steps, the ColumnName spelling on Sheet3 MUST MATCH THE SPELLING ON Sheet2.
    • For the options you wish to validate against, enter each value as its own row in the corresponding column. Again, please be meticulous when it comes to spelling.


Example Table Setup on Sheet3

  1. Double check all spelling matches among shared columns. Screenshots are included below with color-coded mapping indicators for reference.

SHEET1

SHEET2

SHEET3

SHEET4 (RESTRICTED)

Now that all data is formatted and setup, we are ready to send it through the validation suite.

  1. Open the "Data Validation Suite" workflow.

  2. Click "Start Instance".

  3. Upload the formatted file to "Input Table" and enter your e-mail address. Click "Start".

  4. Wait for the process to complete. If a specified ColumnName on Sheet2 doesn't exist on Sheet1, you will receive an e-mail indicating a formatting error.

  5. When the process completes, you will receive an e-mail with both, the input sheet and the result sheet with all values that fail to meet the validation conditions.

Completed example files are also included at the bottom of this post (finishedTutorialDemo, finishedTutorialDemo2).

Validation Rules Explained

For those with a more technical background and are interested in understanding the Data Validation Suite's inner workings, the following screenshots illustrate how the Validation Formulas are executed.

IMPORTANT: These are merely explanations and are NOT additional steps required for workflow setup.

For each specified column, the Data Validation Suite performs the selected validation formula on the input value.

Each ValidationType on Sheet2

Maps to its respective Validation Formula in the Data Validation Suite's Validation Formulas table. These are not available for editing.

Categories