Creating an Excel Validation File

The excel file used to validate processes via the Workflow Review Tool must be correctly configured to ensure the tool works as expected. 

The excel file attached below can be downloaded and used as a template.

File Name

Download

Validity Check Template.xlsx

Validity_check_Template.xlsx

You are free to create your own excel validation file, but the names of the sheets and the columns must be exactly as in the template, otherwise the Workflow Review Tool will not work correctly.

Any additional sheets or columns added to the file will not be considered by the review tool.


The validation file contains 5 sheets, where you can define validation rules on specific areas of your process:

  • Parametrization - contains rules on task options, such as specifying that options must have specific values, or be defined via process variables

  • SQL Query - contains rules which check the validity of SQL queries RULEX 4.6

  • Functions - contains rules on Data Manager functions which either must not be used in the workflow, or which can only be used via process variables

  • Alerts - contains rules on which alerts must be present, or must not be present in specific circumstances

  • Names - contains generic rules on the names of objects such as tasks, process variables, alerts and the workflow itself.

  • Workflow - contains a series of rules which can be defined on the workflow, for example, it cannot contain tasks in error status, or it must contain at least one task of a certain type.


Parametrization

Column

Description

Description

You are free to enter a description, or leave the column blank, or even remove the column if you prefer.
Any additional columns you add to those already present will not be considered by the Workflow Review Tool.

Rule Type

The type of message which will be generated by the rule. 
This column is free, but we recommend using error or warning
Errors are listed first in the output file.

Category

The category name of Rulex tasks. This mapping can be found in the Category Mapping page.
N:B: You can specify either the category or the task, but not both.

Task

The name of your specific tasks. Multiple tasks can be specified, in brackets, comma separated - e.g. (myexcel, mytextfile).
N.B. You can specify the category or the task, but not both.

Option

The name of the task option to be used in the rule. Multiple options can be specified, in brackets, comma separated.
The names of options can be found in the Parametric Options tab of each task in Rulex, or in the corresponding page in the Rulex Process Developer Manual.
The asterisk * can be used as a wildcard, meaning all tasks, while *- means all tasks except those specified.

Parametrized

You can specify whether an option must be defined by a process variable or not.
Possible values are:

  • true: the value must be expressed as a process variable,

  • sensitive: the value must be expressed as a sensitive (i.e. encrypted) process variable.

If left blank there is no constraint to express the value as a process variable.

Acceptable Variable Names

The list of all acceptable variable names, enclosed in quotes and divided by commas - e.g. "sens_var", "my_var".

This option is considered only if parametrized is true or sensitive.

Acceptable Values

The list of all acceptable values, enclosed in quotes and divided by commas - e.g. "john.smith@gmail.com", "paolo.rossi@gmail.com".
Process variables associated to the option will also be checked.

CondAtt1

Optional conditions that can be added to the rule that must be satisfied for the rule to be applied.
There is no limit to the number of conditions you can add, but all conditions will need to be satisfied (i.e. the relationship between conditions is AND).
To add conditions with the OR operator applied, add a new row to the sheet for each new condition.

CondValue1

Enter the possible values for the attribute specified in the CondAttx column.
Multiple values can be listed, enclosed in quotes and divided by commas - e.g. "mysharepointurl1", "mysharepointurl2"

Example rules:


SQL Query   RULEX 4.6

Column

Description

Description

You are free to enter a description, or leave the column blank, or even remove the column if you prefer.
Any additional columns you add to those already present will not be considered by the Workflow Review Tool.

Rule Type

The type of message which will be generated by the rule. 
This column is free, but we recommend using error or warning
Errors are listed first in the output file.

Category

The category name of Rulex tasks. 

The only two tasks where you can generate SQL queries are Conditional Import and Import from Database, so the two possible values here are:

  • conditionalImport

  • sourcesql.

This mapping can be found in the Category Mapping page.

N:B: You must specify either the category or the task, but you cannot specify not both.

Task

The name of your specific tasks.
Multiple tasks can be specified, in brackets, comma separated - e.g. (myexcel, mytextfile).

"*" is a wildcard, meaning all tasks, while "-*" means all tasks except xx.

N.B. You must specify either the category or the task, but you cannot specify not both.

Parameter

In this option, you specify whether the values you include in the Values option, must or must not be included in the SQL query.

Possible values are contains or not contains.

Values

The list of values that must be checked in the SQL query, enclosed in quotes and divided by commas. For example "JOIN", "SELECT".

Whether they must be present, or must not be present, is defined in the Parameter option.

Allow Empty

Define whether or not the query may be left empty. 

Possible options are "true" or "false".

CondAtt1

Optional conditions that can be added to the rule that must be satisfied for the rule to be applied. For example "type", which will only check rules where the type corresponds to a specific value, such as SQL Server.
There is no limit to the number of conditions you can add, but all conditions will need to be satisfied (i.e. the relationship between conditions is AND). To add conditions with the OR operator applied, add a new row to the sheet for each new condition.

CondValue1

Enter the possible values for the attribute specified in the CondAttx column.
Multiple values can be listed, enclosed in quotes and divided by commas - e.g. if the CondAtt1 value is type and the CondValue1 is "SQL Server", the rule will only check queries when the type is SQL Server.

Example rules:

Functions

Column

Description

Description

You are free to enter a description, or leave the column blank, or even remove the column if you prefer.
Any additional columns you add to those already present will not be considered by the Workflow Review Tool.

Rule Type

The type of message which will be generated by the rule. 
This column is free, but we recommend using error or warning
Errors are listed first in the output file.

Function

The name of the Data Manager function onto which you want to add a condition.

Usage Type

The condition you want to apply to the function. There are two possible conditions:

  • parametrized - you can only use the function via a process variable

  • forbidden - you cannot use this function anywhere in your workflow.

Example rules:


Alerts

Column

Description

Description

You are free to enter a description, or leave the column blank, or even remove the column if you prefer.
Any additional columns you add to those already present will not be considered by the Workflow Review Tool.

Rule Type

The type of message which will be generated by the rule. 
This column is free, but we recommend using error or warning
Errors are listed first in the output file.

Alert

The type of alert the rule should be applied to. Possible alerts are:

  • start

  • end

  • duration

  • rows

  • error

Value

Define whether the alert must be defined or not:

  • True - the alert must have been defined

  • False - the alert must not be defined

Recipients

Optionally specify whether the recipients of the error message must be defined in a process variable or not:

  • parametrized - the recipient must be defined in a process variable

  • (blank) the recipient can be entered manually. 

CondAtt1

An optional that specifies whether the alert applies to a specific task or category. Possible values are:

  • task

  • category

CondValue1

Enter the possible values for the attribute specified in the CondAttx column.
Multiple values can be listed, enclosed in quotes and divided by commas - e.g. "mysharepointurl1", "mysharepointurl2"

Example rules:


Names

Column

Description

Description

You are free to enter a description, or leave the column blank, or even remove the column if you prefer.
Any additional columns you add to those already present will not be considered by the Workflow Review Tool.

Rule Type

The type of message which will be generated by the rule. 
This column is free, but we recommend using error or warning
Errors are listed first in the output file.

Target

The type of object the naming restriction should be applied to. Possible values are:

  • task

  • workflow

  • process variable

  • alert

Parameter

The type of naming constraint to be applied to the target. Possible values are:

  • maxlength

  • excludechar

Value

The value that can be assigned to the naming constraint defined in the Parameter option.

The value can either be a numerical value for maxlength, or a list of characters which cannot be used, with no comma separator for excludechar.

Example rules:


Workflow

Column

Description

Description

You are free to enter a description, or leave the column blank, or even remove the column if you prefer.
Any additional columns you add to those already present will not be considered by the Workflow Review Tool.

Rule Type

The type of message which will be generated by the rule. 
This column is free, but we recommend using error or warning
Errors are listed first in the output file.

Parameter

The rule you want to apply to the workflow. Possible values are: 

  • hasname: the workflow must have at least one task with the name specified in the Value column, e.g."sqllite"

  • hascategory: the workflow must have at least one task which belongs to the category specified in the Value column, e.g. "sourcesql"

  • maxparallel: the number of workflows that can be computed in parallel must correspond to the number specified in the Value column, e.g. 4. This value is defined in Rulex in the Process Execution Parameters.

  • nohanging: the workflow must not contain tasks that are not connected to validparent, unless they are sources. Possible values are True if the rule must be applied, otherwise False.

  • nodirtytask: the workflow must not contain tasks in DIRTY status. Possible values are True if the rule must be applied, otherwise False.

  • noerrortask: the workflow must not contain tasks in ERROR status. Possible values are True if the rule must be applied, otherwise False.

  • nowarningtask: the workflow must not contain tasks in WARNING status. Possible values are True if the rule must be applied, otherwise False.

  • noncomputedtask: the workflow must not contain tasks in READY status. Possible values are True if the rule must be applied, otherwise False.

Value

The value the constraint selected in the Parameter column must have.

Examples rules: