Field validation rules are limited to a certain number of functions, such as equals, greater than and less than, in list, and empty.  However, this does not mean that validation is limited to this built-in functionality.  With a bit of creativity, it's possible to validate a record using any almost logic SQL is capable of supporting.  The most straightforward technique to accomplish this is through the use of flag fields. 

Simple Transformation Flags

Email addresses are often entered by system end users, and so often contain unreliable information.  We'll use the AdventureWorks EmailAddress table an example for this exercise.  

One of the most common validations for email addresses is to simply ensure they all conform to the following format: address@host.topLevelDomain.  This can be done entirely with native TimeXtender functionality.  

The first step is to create a custom field that can be used as an error flag.  In this example, we'll call the field IsValidFormat.  It can be tempting to make this field a boolean data type, but this prevents us from doing math (e.g. SUM aggregation) based on the field. Instead, let's make it an Integer. 

We can then add two fixed transformations to set the field to 0, then 1, in that order.  Finally, we add a condition to the "1" fixed transformation to have it only run when the value in EmailAddress is LIKE "%@%.%".  

The result should look something like this:

Now we have have a field with two possible values: 1 or 0.  We can add a field validation to only include the record if the IsValidFormat flag equals 1.  The final end result should look like this:

Note that we could have simply left the IsValidFormat field empty in the case that the email address isn't valid, but relying on NULL values may also affect our ability to do math based on this field. 

Lookup Flags

It's possible to validate records in one table based on data contained in another.  One way to do this would be to create a complicated custom transformation or script - but in most cases we can just use an ordinary TimeXtender conditional lookup.  

In this example, let's suppose we have an Excel spreadsheet created by our company's data stewards that contains "junk" addresses that technically meet our requirements for formatting in the last example, but are clearly invalid.  An example of an address like this might be "1@1.1".

In this case, our flag field will be the conditional lookup itself.  There are a number of ways we can do this.  The approach we will take is to copy the DW_Id system field from our Excel table into our Address table, then rename it rename it to something more friendly, like IsJunkAddress.

As with last time, we can use a validation rule that looks at whether this field is empty, but using transformations to set it equal to zero or one makes it much easier to do math with.  Note that we have to be a little bit more careful with the transformations in this case, because they have conditions that reference the transformed field itself.

The end result might look something like this:

Custom transformation flags

The flags we've seen so far ultimately rely on TimeXtender's native condition functionality.  These native conditions have two limitations: they can't use AND logic, and they don't allow comparisons to dynamically calculated values, like GETDATE().  In cases like these, we can use a custom transformation or a custom condition to get this functionality. 

In this example, we've been given a requirement to expire addresses over a certain age, but with a twist - the time at which the address ages out of the system varies depending on the kind of person the address belongs to.  In order to support this requirement, we've copied PersonType from the Person table into the EmailAddress table using a conditional lookup.

To create this flag, we will make another custom field with an integer data type called IsExpired.  We can then add either a custom transformation or normal transformations with custom conditions.

The custom transformation takes the form of a case statement, and might look like this: 

And the field would end up looking like this:

Adding a custom condition would similarly look like this: 

And the end state like this:

Note that since AdventureWorks is always a few years old, this particular validation rule may remove some or all of the records from the EmailAddress table depending on which version you are using.