Cross Field Validation - how to do it in Datatables/Editor - two examples

Dirk Fincley

DataTables and the Editor are great for creating CRUD Applications fast and there are many built in validators available. However it is often necessary to perform "cross field" validation, i.e. to validate one field based on the contents of another field or other fields.
I give two simple examples here for anyone else who needs to know how to do this.

Example 1 - ensure that one numeric field never contains a value greater than another numeric field in the same query.

This shows that the field called "prop.usable_area" must be less ta or equal to the field called "prop.total_area". Note in this example there is a left join therefore all fields MUSTbe fully qualified.

The validation goes inside the .php file after any other validators. Here we see the "numeric" and "notEmpty" validators present so we already know that both fields contain numeric data.

In this case "total_area" which is in the "prop" table and is hence referred to as "prop.total_area" must not be smaller than "usable_area" or to give it its full and required name "prop.usable_area".

We define a customiseable validator using "function ( $val, $data, $opts )" .
Here

• $val is the value in question ("prop.usable_area")
• $data refers to all the available data or fields in this query (or on this form).
• $opts are the options for these but here we do not need to use this only $val and $data. The code is:-

Field::inst( 'prop.total_area' )
    ->validator( 'Validate::notEmpty' )
    ->validator( 'Validate::numeric' )
    ->validator( 'Validate::minMaxNum', array( 'min'=>0, 'max'=>99999.99 )),
Field::inst( 'prop.usable_area' )
    ->validator( 'Validate::notEmpty' )
    ->validator( 'Validate::numeric' )
    ->validator( 'Validate::minMaxNum', array( 'min'=>0, 'max'=>99999.99 ))
    ->validator( function ( $val, $data, $opts ) 
           {
           if  ($val >  $data['prop']['total_area'])  
               {
               return 'Usable Area must be less than or equal to Total Area';   }
           else
               {
               return true;
               }
            } ), ... (rest of fields)    

So basically we are saying "is prop.usable_area greater than prop.total_area?". If so issue the error message "'Usable Area must be less than or equal to Total Area" under field prop._usable_area. It is important to return this or to return "true" if there is no error (otherwise you will get the red "Error field" message.

Example 2 - ensure when one field value is selected that another field contains a non zero number or when another value is selected that the other field must contain zero

Here again there is a left join in operation and hence you have to use the table reference before the field name, so "terrace", which is a radio button to denote whether or not a property has a terrace, is referred to as "prop.terrace".

Here I put the code after "prop.terrace_area". What I am trying to achieve is basically

1) when a property has a terrace the terrace_area field must be non-zero to specify the size of the terrace

and

2) when a property does not have a terrace the terrace_area field must be zero. It would make no sense otherwise.

Note again that the basic validators (numeric and notEmpty) are left and the extra cross field one is added afterwards.

The .php file contains the additional validator( function ( $val, $data, $opts ) function and the needed logic.Note the references to the second field (or third etc) has to be in the form $data['prop']['terrace'] where prop is the table and terrace the field name.This format is crucial so take careful note of the punctuation! immediately after the $data is the table name enclosed in single quotes within square brackets and then the field name enclosed in single quotes within square brackets.

What we are saying here in the first part of the if statement is "when prop.terrace_area contains a value greater than zero AND prop.terrace is set this is OK - return true. Note this is needed or you will get an "error field" message "Terrace area prohibited when no terrace present".

After the second "else" we are saying "when the $val field (prop.terrace_area) contains zero and prop.terrace is set (to denote a terrace exists) issue an error message - terrace area is required when terrace present. Note again the "true" return for the good non error message return. Note this is again required or you get an "error field" message.

Apologies for the "inverse" logic but for some reason the "=" sign did not work for me and therefore I used the "<>" not equals sign. Perhaps this is by design but if anyone know how to get the equals sign to work please share the knowledge to me and others.

Note after the If/else logic remember that the clauses must be contained in curly braces. Also after the whole on my logic a coma is needed as this denotes the end of the prop.terrace_area definition and there are other fields after it.

Field::inst( 'prop.terrace' )
    ->validator( 'Validate::notEmpty' ),        
Field::inst( 'prop.terrace_area' )
    ->validator( 'Validate::numeric' )
    ->validator( 'Validate::minMaxNum', array( 'min'=>0, 'max'=>999.99 ))   
    ->validator( function ( $val, $data, $opts ) 
                {
                if  ($val >  "0")  
                    {
                    if ($data['prop']['terrace'] <> 'No' )  
                        {
                        return true;    
                        }
                    else
                        {
                        return 'Terrace area prohibited when no terrace present';
                        }
                    }
                else
                    {
                    if ($data['prop']['terrace'] <> 'No' )  
                        {
                        return 'Terrace Area required when terrace present';
                        }
                    else
                        {
                        return true;    
                        }
                    }
                } ),   

I hope this helps someone who has struggled with getting cross field validation to work.

allan

Hi Dirk,

This is fantastic - thanks for sharing it with us!

Regards,
Allan

crush123

Thanks for this post.

I have an event planner where i wanted to ensure the end date/time was always after the start date/time, and this approach works perfectly

Dirk Fincley

Great. You are very welcome. If you need extra conditions you can just nest more if/else logic to put the extra conditions into (effectively "and-ing" the outer condition wit the inner ones.
I have used this method often without problems. Remember that you need to code EVERY exit condition with either an error message or a return "true" otherwise you will receive an "Error field" message.

Dirk Fincley

I realised after I posted this that the inverse logic could easily have be replace with by using:-

if ($data['prop']['terrace'] == 'Yes' ) instead of
if ($data['prop']['terrace'] <> 'No' )

This would make te code more readable and we should all strive for more readable code. "==" is of course the PHP equality sign.