?
Solved

Field Validation in Form

Posted on 2003-02-25
33
Medium Priority
?
267 Views
Last Modified: 2013-12-18
I have a field by name 'SerialNumber', which holds serial numbers of hardware components.
But the problem is it is allowing duplicate entries when i save the form.

How do i check the uniqueness of the field, i.e., if the serial number entered exists it should give a message saying 'Serial No. exixts', is there example formula or code(LS) to do this?

Its for Notes Client(5.0.11).

I have a view 'byserialno' in which the first column is sorted.

Can i have the code sample for either the Formula or the LotusScript
0
Comment
Question by:Ram123022203
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 14
  • 2
  • +1
33 Comments
 
LVL 13

Accepted Solution

by:
CRAK earned 120 total points
ID: 8023800
You would have to create a view to do that:
Col 1 (sorted): <SerialNumber>
Col 2 : @Text(@DocumentUniqueID)

Serial No.'s validation formula should perform a dblookup in that view, using the SerialNumber as key and retrieving col 2 (doc Id's).

If you get >1 element returned, you know there's something wrong (should not occur!)
If you get no results (use @IsError() to check the @DbLookup's result), valudation = @Success
If 1 result is returned, there are two options:
You've found the SAME / CURRENT document (=2nd save), or:
You've found a DIFFERENT document.

Check if your document's @Text(@DocumentUniqueID) is listed in as a result. If it differs: @Failure("msg")
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8023835
Put this in Field's SerialNumber InputValidation:

FIELD SerialNumber:=SerialNumber;
found:=@DbLookup("":"NoCache";"";"byserialno";SerialNumber;1);
@If(@IsError(found);@Success;@Failure("used SerialNumber: "+SerialNumber))

0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8023841
'morning CRAK :-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:CRAK
ID: 8025900
Sorry Zvonko, I haven't been online a lot today...

In your example... what if a user saves a document (new ser. no) and without any change save again....?
It triggers the @Failure!
That's where I started involving the doc id in the validation.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8027430
Sure! You are right :-)
0
 
LVL 13

Expert Comment

by:CRAK
ID: 8027520
;-))
Thanks!
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8027726
Should I extend my proposal to your recommendation?
Now I mean when we are both here this small points are really not a question :-)

0
 
LVL 13

Expert Comment

by:CRAK
ID: 8031766
It could help Ram123 if he/she needs sample code. I've already described the solution in my comment, including the column description of the view.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8031786
You are right. For this little points has he/she enough to read :-)

0
 
LVL 13

Expert Comment

by:CRAK
ID: 8031877
Where has my bro gone that provides full service, no matter how small the amount of points is?

Any news on your job Zvo?
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8032343
Whom are you talking about bro? :-)

There are big news, but better I tell you that on our line :(

0
 
LVL 13

Expert Comment

by:CRAK
ID: 8032701
Our bro.... what whas his name again....? The <|:-) -one!

ok.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8032903
:-)
0
 

Author Comment

by:Ram123022203
ID: 8046720
Hello,

Sorry that i could not reply as i was on leave.

I am not getting the error message, its still saving duplicate entries.

My settings are as follows:

FORM
Form Name: EquipmentAsset
fieldName : SerialNumber (Type:Text, editable)

VIEW

View Name: Asset By SerialNo
View Selection Formula : SELECT Form = "EquipmentAsset"

Column 1:
ColumnTitle : Serial Number
formula: @Trim(x:y)
Sort: Ascending
Type :Standard
Show multiple values as seperate entities

Column 2:
ColumnTitle : Tyep
field: Type
Sort: None
Type :Standard

As you said i inserted a column between col1 & col2 and named it Unique ID (hidden) and gave the formula @Text(@DocumentUniqueID)

And in my form for the SerialNumber field gave the formula for Input Validation.When i entered a serial number which is present in the d/b it just saved.

I don't whats wrong, or where am i going worng?

0
 
LVL 13

Expert Comment

by:CRAK
ID: 8047235
Please add the validation formula as well...
If @DbLookup fails save is allowed, no matter the reason of the failure. Probable causes include: s/n wasn't found (some typo), or the entire view(name) wasn't found.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8047273
Hello buddy, hello Ram,

there are to much typos in your setting Ram.

Ok, let's change.

VIEW:
Add below the view name "Asset By SerialNo" into the field labeled Alias this name: byserialno

The first column has to be sorted (is already) but not a Formula then a Field should be the value for this Column:
SerialNumber

Insert a second column in this view and make it hidden.
You can make this column as last one in the view (append the column) and make it hidden. Important is only to have this additional column NUMBER correlate to your SerialNumber validation formula. So let us here assume you decide for the second Column.
Here is the Formula for the second column in your view:
@Text(@DocumentUniqueID)

Now the Form "EquipmentAsset"
The only thing you have to set in this Form is the InputValidation for the Field "SerialNumber".
Here is the InputValidation formula for the field "SerialNumber":
FIELD SerialNumber:=SerialNumber;
found:=@DbLookup("":"NoCache";"";"byserialno";SerialNumber;2);
@If(@IsError(found);@Success;found=@Text(@DocumentUniqueID);@Success;@Failure("used SerialNumber: "+SerialNumber))


That's all.

Good luck,
Zvonko



0
 

Author Comment

by:Ram123022203
ID: 8056089
Thanks Zvonko,

Its working fine.

Rgds,
Ram123
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8056407
Ok.

What about increasing the points and giving me an A+ :-)

0
 
LVL 13

Expert Comment

by:CRAK
ID: 8059926
What about an entry "for CRAK" with an even higher no. of points? After all: anyone could have come up with the A solution, but who came up with THE solution first?
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8060081
your FIRST solution was for thirty, I am now bagging for more :-)
If you get hundred then I will get three times :-)

0
 
LVL 13

Expert Comment

by:CRAK
ID: 8062473
For running off with my solution???
I guess you'll then be offering me another 500 * "A" as redemption!?
;-))
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8062517
Oh, come on. Do you really believe I would not be able to solve that problem???

For ***500*** you have to offer better tricks ;-)

0
 
LVL 13

Expert Comment

by:CRAK
ID: 8063578
I know you can... I know you would have... if/when the 2nd save-issue would occur.
But I did so first! So obviously the larger part of the score is mine.
;-P
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8063655
Did you see CRAK? This 30 would not be enough, but the next one and I am jumping over you (except you get this 500 in advance :)

0
 
LVL 13

Expert Comment

by:CRAK
ID: 8064465
And when you arrive in the 1st column you start a 4th id right? Just to push me further down in the list.....
What a great friends I have here at EE!

(Do you think Ram waits for us to end this argument before assigning me the points?)
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8066210
Who cares about thirty points! :-)
0
 
LVL 13

Expert Comment

by:CRAK
ID: 8071096
...but the arguing is so much fun!
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8071226
That's why we are still in :-)
0
 
LVL 13

Expert Comment

by:CRAK
ID: 8072026
WE are....
I wonder if Ram is....
;-))
0
 

Expert Comment

by:8wmas
ID: 23979517
I have a form with a computed field created by joining three other fields (location, date, time) to create a appointmentref, the idea being that the same time slot at the same location can not be taken by more than one records.

I want to be able to ensure the field is unique by checking against a hidden view which lists all records sorted by appointmentref.

0
 
LVL 13

Expert Comment

by:CRAK
ID: 23981004
You should post that as a new question: draws attention of all other experts (not just the ones participating in this question).
However, if you use your appointmentref instead of the serialnumber, things should be exactly the same!
Have you thought of a way to detect overlapping appointments?
E.g. {Room 1@Jan 1, 12:00-13:00} vs. {Room 1@Jan 1, 12:30-13:30}
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…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 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