Advanced Input Checking in WFA with custom Errors

I'm at a customer and I was, once again, bumping into a very recurring issue : how to have advanced input checking.  In WFA you can have cascaded drop down boxes and some minor dependencies (using the input dependency), but every now and then it just doesn't cut it.

I've cursed many times over the last years that WFA doesn't have advanced input validation.  If one day, HTML5 would be used as a front-end GUI, we could use javascript or even jquery.  But now it's flash, and we only have the few options WFA can offer.

Or Not ?

I must have had a very good night sleep, as it all-of-a-sudden struck me how easy this was.
You just add 2 additional input fields (create a dummy constant to create them if needed)

• InputError (label : Error Message)
• InputCheck (label : Input Check) (Mandatory : TRUE and lock values)

The input check field is an easy one, just add this SQL query :

SELECT
   CASE
      WHEN '${InputError}' = 'OK' THEN 'OK' ELSE ''
   END;

The input error, can be as complex as you want.  Let's assume the following test :
If field1 = 'string', then field2 has to be a 5 char string, but if field1 = 'number', then field2 has to be a 3 digit number.  In any other case, it doesn't matter.  And remember, you can make this as complex as you want.

SELECT
   CASE
      WHEN '${field1}' = 'string' # case string
        THEN
          CASE
             WHEN '${field2}' REGEXP '[a-zA-Z0-9]{5}'  # check if field2 is string
               THEN 'OK'
               ELSE 'Field1 must be a 5 char string' # error
          END
      WHEN '${field1' = 'number' # case number
        THEN  
          CASE
             WHEN '${field2}' REGEXP '[0-9]{3}'  # check if field2 is number
               THEN 'OK'
               ELSE 'Field1 must be a 3 digit number' # error
          END
     ELSE 'OK' # Default to OK

   END;

So how does this work ?
Well, the InputError Query is doing all the checking and MySql has many ways of checking, string manipulation etc...  You just have to return 'OK' if all is ok, or return an error message.

The inputCheck Query will return 'OK' if the message is ok, or will return '' (=empty) otherwise.  As this field is mandatory, WFA will flag it as a problem, and as the values are locked, you must fix the issue.