Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Validation of Fields

anybody has a code for validation the validation I created have problems not all works these are the validations needed for account no.

FIELD NAME: acctno

->unique account no. no same account number is allowed to be inputted
->acct no. must have 10 digits
->acct no. must be numeric no letters allowed
->acct no. cannot be negative
->zero values not allowed

I created a script but some of it works the others do not ....hope u can help me out here ....
0
emem
Asked:
emem
  • 5
  • 4
1 Solution
 
Bozzie4Commented:
Create a view with the documents that contain acctno.  Put acctno in the first column, sort it on that column.   Create a second column, put another field there, just to return a value.

Now in the field acctno (NUMBER field) , enter in the input validation section :

lu := @dblookup( "":"nocache" ; @dbname ; "THEVIEW" ; acctno ; 2 );
@if( @iserror( lu ) ; ""  ;  @return(@failure( "The acctno. is not unique")) ;
@if( @length(  @text( acctno ) ) != 10 then ; @return(@failure( "The acctno. must have 10 digits (NOTE: this will fail for 00000000001 type numbers !)" )); "" );
@if( acctno <= 0 ; @return(@failure( "Acctno. can't be negative, or zero" )) ; "" );
@success

Note that you can't have numbers like 0000000005 with this approach, nor a simple 5 !

Also not that it still is possible to create duplicate values using this method.  If you really want to avoid duplicate numbers, you'll have to change the design.  Do a search on 'force unique values' or something, it should get you some results.  The reason for this is that someone else can create a document at the same time as you.  When you work on more then 1 server, this risk becomes even bigger (between replications you are not sure you have all numbers)

cheers,

Tom
0
 
Bozzie4Commented:
Ah and another remark : you should check the @dblookup first without the @iserror, because if you make a mistake in the formula there, he will always assume the acctno. is unique ....

Tom
0
 
Sjef BosmanGroupware ConsultantCommented:
Tom, I suppose your formulae need adaptation, for editing and saving an existing document will always show a duplicate (namely the current document).
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Bozzie4Commented:
Yes, correct, sjef , didn't think about that one :-)

So change the view, change the second column, put in the formula:
@text( @documentuniqueid )

lu := @dblookup( "":"nocache" ; @dbname ; "THEVIEW" ; acctno ; 2 );
@if( @iserror( lu ) ; ""  ; @if( lu = @text(@documentuniqueid);""; @return(@failure( "The acctno. is not unique"))) ;
@if( @length(  @text( acctno ) ) != 10 then ; @return(@failure( "The acctno. must have 10 digits (NOTE: this will fail for 00000000001 type numbers !)" )); "" );
@if( acctno <= 0 ; @return(@failure( "Acctno. can't be negative, or zero" )) ; "" );
@success

cheers,
Tom
0
 
ememAuthor Commented:
i'll try this one thanks ...get back to you
0
 
ememAuthor Commented:
got an error this error appears "An operator or semicolon was expected but none was encountered:'then'...there is a semicolon but still the error appears
0
 
ememAuthor Commented:
I was able to correct the error..... but the validation  still it accepts letters
0
 
Bozzie4Commented:
Yes, but you must change the field type to number.  There is no actual check that what you entered, is a number, in my validation, this depends on the field being a number field.

You could add that, though (mind you there are situations where @isnumeric will yield false positives !)

lu := @dblookup( "":"nocache" ; @dbname ; "THEVIEW" ; acctno ; 2 );
@if( @iserror( lu ) ; ""  ; @if( lu = @text(@documentuniqueid);""; @return(@failure( "The acctno. is not unique")))) ;
@if( @isnumeric( acctno ) ; "" ; @return( @failure( "The acctno. must be a number." )));
@if( @length(  @text( acctno ) ) != 10 then ; @return(@failure( "The acctno. must have 10 digits (NOTE: this will fail for 00000000001 type numbers !)" )); "" );
@if( acctno <= 0 ; @return(@failure( "Acctno. can't be negative, or zero" )) ; "" );
@success

Tom
0
 
ememAuthor Commented:
I changed @Isnumeric to @Isnumber ..i'll give you feed back
0
 
Bozzie4Commented:
Yes, sorry, Isnumeric is the LS version :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now