Solved

Validation of Fields

Posted on 2004-04-02
10
233 Views
Last Modified: 2013-12-18
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
Comment
Question by:emem
  • 5
  • 4
10 Comments
 
LVL 15

Expert Comment

by:Bozzie4
ID: 10739579
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
 
LVL 15

Expert Comment

by:Bozzie4
ID: 10739585
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10739847
Tom, I suppose your formulae need adaptation, for editing and saving an existing document will always show a duplicate (namely the current document).
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 15

Expert Comment

by:Bozzie4
ID: 10740068
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
 

Author Comment

by:emem
ID: 10762920
i'll try this one thanks ...get back to you
0
 

Author Comment

by:emem
ID: 10763489
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
 

Author Comment

by:emem
ID: 10763527
I was able to correct the error..... but the validation  still it accepts letters
0
 
LVL 15

Accepted Solution

by:
Bozzie4 earned 150 total points
ID: 10763591
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
 

Author Comment

by:emem
ID: 10763627
I changed @Isnumeric to @Isnumber ..i'll give you feed back
0
 
LVL 15

Expert Comment

by:Bozzie4
ID: 10763773
Yes, sorry, Isnumeric is the LS version :-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question