Solved

Formula Question....

Posted on 2004-09-20
14
239 Views
Last Modified: 2013-12-18
Hi...Have two fields in a form A1 & A2.

both the fields can store multiple values in it. each value is 13 bytes long.

Now I've to put a validation so that no duplicates can be entered.
In A1's input translation I've @Unique(A1)
In A2's input translation I've @Unique(A2)  

But i also want to restrict this. If a value in in A1 and the same value is in A2. it should give an error message.

I did put the following in A2's input validation.
@If(a2 = a1;@Failure("cannot enter duplicates");@Success)...

But this works only for some values.(Not sure why... may be becuase A1 and A2 are multi-value fields)

thanks
Kalios
0
Comment
Question by:kalios
14 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12104281
use

@If(a2 *= a1;@Failure("cannot enter duplicates");@Success)...

~Hemanth
0
 
LVL 2

Author Comment

by:kalios
ID: 12104451
great so *= is the trick..

Hemanth, is there a way to show the duplicate in the error message.

thanks
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12104532
There is..
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 2

Author Comment

by:kalios
ID: 12104537
How?
0
 
LVL 2

Author Comment

by:kalios
ID: 12104601
sorry, i've no more points left. i'll have to buy them. i'll for my next question.
0
 
LVL 14

Expert Comment

by:p_partha
ID: 12104722
Kalios
Use @keywords

Partha
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12105676
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 35 total points
ID: 12105683
Let me paste the formula here

List := A1 : A2;
num := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
idList := @Subset( num *+ num *+ num; @Elements(List));
tmp1 := @Unique(@Replace(List; List; idList));
tmp2 := @Trim(@Replace(idList; tmp1; ""));
@Replace(tmp2; idList; List);
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 12105843
Neat, been a while since I saw that one.  Ah, the joys of list processing.
0
 
LVL 2

Author Comment

by:kalios
ID: 12107047
I can just say ONLY one thing..

SUPER HEMANTH - If I could increase the font size I would do it to the biggest.

thanks
Kalios
0
 
LVL 2

Author Comment

by:kalios
ID: 12107094
It returns all the duplicates. I've used  num*+ num*+ num*+ num  as i've more values here.

also, once I save the document it displays me all the duplicates which is great. but i also want this.
once i click ok on the message all the duplicates should be removed from A2.


thanks
Kalios.
0
 
LVL 2

Author Comment

by:kalios
ID: 12111888
Hemanth, I get the list of all the duplicates as return which is great. is it possible to remove duplicates from A2 by using formula or do i have to write a script for that.
0
 
LVL 2

Author Comment

by:kalios
ID: 12112160
ok i get it...

a := @Replace(A2;fieldwhereistoredduplicatesfromyourcode;"");
@If(fieldwhereistoredduplicatesfromyourcode = a2; @SetField("a2";a) ;"");
@Command([ViewRefreshFields])

this works fine.... can i do any better.
kalios
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12112807
You are good with the code
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

786 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