Solved

Validation of Fields

Posted on 2004-04-02
10
230 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now