data validation using two database columns

Classic Servoy
1

Hi,

I have two columns in my database for which I would like to validate user input – called cc and unit.

If cc is in a certain range, then unit can be set to certain values. If cc is in another range, then unit can be another set of values. The rules are the same across all my applications.

For example if cc is 500-599, unit can be 0-9. If cc is 600-699, then unit can be 10-19.

Is there a way to do this using only database column validation, or do I need to do this in an onDataChange method? I’m new to Servoy so I wanted to know how some veterans would handle this.

My only gripe about the onDataChange validation is that it handles errors differently from the db column validation. In db column validation an automatic popup appears and the bad number turns red, etc. If I want the onDataChange to do this then I must re-invent the wheel. Otherwise the user gets a different experience on columns that are db validated vs onDataChange validated. Hope what I’m saying is clear.

Anyway, please help!

Thanks

2

Welcome, I’m very new too but I believe I can help. I think you simply set the validation type to servoy.GlobalMethodValidator then add your validation function to globals.js, referencing the two fields. Something like this…

function CrossValidate() {
	// Returns true (validation passed) for certain conditions, false (validation failed) otherwise
	if (forms.<yourForm>.cc >= 500 && forms.<yourForm>.cc <= 599)
	{
		if (forms.<yourForm>.unit >= 0 && forms.<yourForm>.unit <= 9)
		{
		return true;
		}          
	return false;
	}
	
	if (forms.<yourForm>.cc >= 600 && forms.<yourForm>.cc <= 699)
	{
		if (forms.<yourForm>.unit >= 10 && forms.<yourForm>.unit <= 19)
		{
		return true;
		}          
	return false;
	}
	
	// However you want to handle cases where cc < 500 or cc > 699 goes here
}

I’m sure there is a neater way to use the if statements, but it’s nearly midnight. Obviously replace with your form name.

Let me know if it works for you

3

yeah, I guess that sort of works.

But what if you are using these columns on more than one form? The fact that it is a global validator means to me that it could be used by the whole application, across many forms. So you would need to reference the actual database column, not the form field, and I’m not sure how to do that.

4

I agree it would be better to do this globally. I’m not sure how to do this with multiple columns as you’re requesting, anyone?

5

Hi all,

Ithink that you can use the currentController to get the name of the current form , obviously having the column(s) to be validated in its DataSource.
May be the code bellow does the job.

function CrossValidate() {
   var currentControllerName = currentController.getName();
   // Returns true (validation passed) for certain conditions, false (validation failed) otherwise
   if (forms[currentControllerName].cc >= 500 && forms[currentControllerName].cc <= 599)
   {
      if (forms[currentControllerName].unit >= 0 && forms[currentControllerName].unit <= 9)
      {
      return true;
      }          
   return false;
   }
   
   if (forms[currentControllerName].cc >= 600 && forms[currentControllerName].cc <= 699)
   {
      if (forms[currentControllerName].unit >= 10 && forms[currentControllerName].unit <= 19)
      {
      return true;
      }          
   return false;
   }
}

Hope this help

6

its better not to do such things in the validator or the oninsert/update methods.
Those are data layer methods, you shouldnt touch ui at that place. Because they can be called from anything
Try to separate ui and data layer in the code.

7

Thanks for everyone’s responses.

Johan, do you have a suggestion how I should do the validation? This is actually a fairly common scenario for us, so I would prefer to do it the right way.

8

if i look quickly

you can use the onInsert and/or onUpdate validation.
there you get the complete record. So you can ask the record.cc when you want to test unit (and visa versa)

Maybe if you really want it on column validation with the global method validator, then we could see if we could introduce some extra parameter
that would be the record object so that you get a bit more info in the validator. (currently you get only the value)
You could make a case for this.

9

This post is from 2010 (more than two years ago). But what’s the way to go now?

I think that now it’s better to put this type of validation in the entities (table_name_entity.js files). Don’t need to use the ‘GlobalMethodValidator’ or the globals on ‘onRecordInsert/Update’ events.

I think that we don’t need to put validation in any global scope anymore. What do you think?

I any case, what is the best practice or advice in this regard?

Best,
Carlos