Using filters and conditions in validation rules


In data validation rules, filters and conditions work together to determine the records that fail with validation errors.

Filters

Use filters to limit your query to specific records. Filters are optional.

Examples of things that you might want to filter records on:

  • Active records (filter on the status of objects, HCPs, HCOs, Addresses, Licenses, or Parent HCOs.)
  • Non-candidate records (check that the candidate flag field is set to False)
  • Addresses in multiple countries (include the Address Country in your filter, otherwise the rules will be applied to all addresses for records in the specified primary country)

Conditions

Use conditions to define the situation that will cause the rule to fail. At least one condition is required; for example, add a condition so that the validation rule fails on HCP records that do not list a specialty.

Condition operators

The following operators are available for adding filters and conditions to a rule:

  • Find / Not Find - Values are not case-sensitive.
  • Equals / Not Equals - Values are not case-sensitive.

    When this operand is used with sets of objects (for example, Sets of Addresses and Sets of Parent HCOs), it can be used to create rules to check for a specific count of sub-objects and relationship objects; for example to check for a specific count of objects. For more information, see Validate the count of objects.

  • Is Null / Is Not Null - Can be used for reference fields.
  • In / Not In - Used for string and reference fields. It specifically looks for values in fields and it ignores blanks or nulls.
  • Exists / Does Not Exist - Used to determine if a sub-object or relationship object exists or does not exist within a rule. Choose one of the three "set" of fields (address, license, or Parent HCO) or a custom sub-object or relationship object for this condition. No value is required. Note that rules are only applied to local records. If there are only Veeva managed or third-party managed sub-objects or relationship objects, they are not detected during this check - only local records are flagged.
  • Is Duplicate - Used to determine if a sub-object or relationship object has duplicate values; for example, duplicate licenses on the same HCP. Identify the fields that you want to compare within the set in the Value column. The comparison is exact. If two records are identical but one field used in the comparison has a value and the other does not (for example, license type) the records are not considered duplicates. Note that rules are only applied to local records. If there are duplicates between local, Veeva-managed or third-party managed fields, they are not detected during a duplicate validation check - only duplicate local records are flagged.
  • Match Regular Expression / Not Match Regular Expression - Can be used for values that are free text; for example, to find invalid characters in name fields (!?+=#$%&*). (Network uses Java Regular Expressions).
  • Less Than / Greater Than - Can be used for fields containing numeric values; for example, fields with rankings or dates.
  • Between - Can be used for fields containing numeric values; for example, fields with rankings or dates.

    This operand looks at the values between what's specified, not including what's specified. For example, if you provide the values of 1 and 3, the rule will only fire/fail for a value of 2. If you provide values of 1 and 2, the rule will never fire because there is no value between 1 and 2.

Examples for using filters and conditions

Review the following examples to understand how to use filters and conditions to define your data validation rules.

Example 1 - Using one filter and one condition

This data validation rule is limited to run on all active HCP records. The rule fails on active HCP records that do not have a specialty listed.

Filters: Field = HCP Status, Condition = Equals, Value = Active

Conditions: Field = Specialty 1, Condition = Is Null

Example 2 - Using no filters with two conditions (using AND)

This data validation rule runs on all HCP records. The rule fails on active HCPs and any HCPs (active or inactive) that do not have a specialty listed.

Filters: none

Conditions: Field = HCP Status, Condition = Equals, Value = Active, And/Or = AND

Conditions: Field = Specialty 1, Condition = Is Null

Note that the outcomes of Example 1 and Example 2 are the same, but using a filter saves on performance and productivity because the rule stops checking as soon as a filter does not apply.

Example 3 - Using filters with two conditions (using OR)

This data validation rule is limited to run on all active HCPs records. The rule fails on active HCPs that do not have a first name or last name listed.

Filters: Field = HCP Status, Condition = Equals, Value = Active

Conditions: Field = First Name, Condition = Is Null, And/Or = OR

Conditions: Field = Last Name, Condition = Is Null

Example 4 - Using no filters with multiple conditions (using AND and OR)

This data validation rule runs on all HCP records. The rule fails on active HCPs that do not have a first name and all HCPs (active or inactive) that do not have a last name. The absence of a filter to check for active HCPs means that the Active HCP Status condition is grouped with the first name condition only. The last name check is done for all HCPS, regardless of status.

Filters: None

Conditions: Field = HCP Status, Condition = Equals, Value = Active, And/Or = AND

Conditions: Field = First Name, Condition = Is Null, And/Or = OR

Conditions: Field = Last Name, Condition = Is Null

Example 5 - Using no filters with multiple conditions (using AND)

This data validation rule runs on all HCP records. The rule fails on active HCPs that do not have a first name and do not have a last name.

Filters: None

Conditions: Field = HCP Status, Condition = Equals, Value = Active, And/Or = AND

Conditions: Field = First Name, Condition = Is Null, And/Or = AND

Conditions: Field = Last Name, Condition = Is Null

Example 6 - Using a Regular Expression in a rule

This data validation rule runs on all License records. The rule fails on active licenses that do not have a two-character entry in the Licensing Authority field. The regular expression checks that letters are used and not numbers, and ensures the length is exactly two characters. If the entry in the Licensing Authority field contains something other than uppercase or lowercase letters and is not two characters in length, the rule will fail.

Filters: Field = License Status, Condition = Equals, Value = Active

Conditions: Field = Licensing Authority, Condition = Not Match Regular Expression, Value = ^[a-zA-Z] {2}$

This regular expression is helpful to check for case for general validations.

To check specific text for case, wrap the text with /b on either end of the regular expression.