• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Field Validation in Form

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
Ram123022203
Asked:
Ram123022203
  • 14
  • 14
  • 2
  • +1
1 Solution
 
CRAKCommented:
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
 
ZvonkoSystems architectCommented:
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
 
ZvonkoSystems architectCommented:
'morning CRAK :-)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
CRAKCommented:
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
 
ZvonkoSystems architectCommented:
Sure! You are right :-)
0
 
CRAKCommented:
;-))
Thanks!
0
 
ZvonkoSystems architectCommented:
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
 
CRAKCommented:
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
 
ZvonkoSystems architectCommented:
You are right. For this little points has he/she enough to read :-)

0
 
CRAKCommented:
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
 
ZvonkoSystems architectCommented:
Whom are you talking about bro? :-)

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

0
 
CRAKCommented:
Our bro.... what whas his name again....? The <|:-) -one!

ok.
0
 
ZvonkoSystems architectCommented:
:-)
0
 
Ram123022203Author Commented:
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
 
CRAKCommented:
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
 
ZvonkoSystems architectCommented:
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
 
Ram123022203Author Commented:
Thanks Zvonko,

Its working fine.

Rgds,
Ram123
0
 
ZvonkoSystems architectCommented:
Ok.

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

0
 
CRAKCommented:
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
 
ZvonkoSystems architectCommented:
your FIRST solution was for thirty, I am now bagging for more :-)
If you get hundred then I will get three times :-)

0
 
CRAKCommented:
For running off with my solution???
I guess you'll then be offering me another 500 * "A" as redemption!?
;-))
0
 
ZvonkoSystems architectCommented:
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
 
CRAKCommented:
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
 
ZvonkoSystems architectCommented:
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
 
CRAKCommented:
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
 
ZvonkoSystems architectCommented:
Who cares about thirty points! :-)
0
 
CRAKCommented:
...but the arguing is so much fun!
0
 
ZvonkoSystems architectCommented:
That's why we are still in :-)
0
 
CRAKCommented:
WE are....
I wonder if Ram is....
;-))
0
 
8wmasCommented:
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
 
CRAKCommented:
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

Technology Partners: 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!

  • 14
  • 14
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now