Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

formula for a view

I've a form named "Cus" in which one of the field has the following properties.

fieldname = MTNO
Type = text,
allow multiple values is checked.

in some of the forms this field has duplicate values in it.

I want to create a view which will show only records which has duplicate values in this field.

Please advise on what would the SELECT statement be in the view.

thanks
Kalios

0
kalios
Asked:
kalios
  • 10
  • 9
  • 7
  • +3
3 Solutions
 
CRAKCommented:
Select @Elements(MTNO) != @Elements(@Trim(MTNO))
0
 
CRAKCommented:
Note that if the field should contain empty entries, those documents would show to!
0
 
Sjef BosmanGroupware ConsultantCommented:
CRAK! Pssst! It's not @Trim, it's @Unique...
0
Industry Leaders: 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!

 
ZvonkoSystems architectCommented:
Here is your SELECT formula:

SELECT (FORM = "Cus" & (@Implode(MTNO) != @Implode(@Unique(MTNO))))

0
 
kaliosAuthor Commented:
BEAUTIFUL , I used Zvonko's, It works like a charm. Can I pick the duplicate values into a variable and show it up in one of the column in this view.

thanks
Kalios
0
 
Sjef BosmanGroupware ConsultantCommented:
Actually, CRAK's corrected formula is faster (Implode is slow):

    SELECT Form="Cus" & @Elements(MTNO)<>@Elements(@Unique(MTNO))

Duplicates can be shown in a column, see http://www.experts-exchange.com/Q_20789842.html
   
0
 
ZvonkoSystems architectCommented:
Yeap!

Add a Column with this formula:

@ReplaceSubstring(@Implode(MTNO;";");@Unique(MTNO)+";";"")


0
 
Sjef BosmanGroupware ConsultantCommented:
Won't work.
Check that
    @ReplaceSubstring("ABC;CDE;ABC;CDE"; "ABC;":"CDE;"; "")
will result in
    "CDE"
0
 
ZvonkoSystems architectCommented:
Take this:

@Unique(@Explode(@ReplaceSubstring(@Implode(@Unique(MTNO)+";"+MTNO;";");@Unique(MTNO)+";"+@Unique(MTNO);";")))


0
 
ZvonkoSystems architectCommented:
Slightly shorter:

@Explode(@Unique(@Trim(@Replace(@Unique(MTNO)+";"+MTNO;@Unique(MTNO)+";"+@Unique(MTNO);";"))))


Zvonko



0
 
ZvonkoSystems architectCommented:
Here slighty better readable:


@Trim(@Explode(@Unique(@Replace(@Unique(MTNO)+";"+MTNO;@Unique(MTNO)+";"+@Unique(MTNO);";"))))



0
 
ZvonkoSystems architectCommented:
Is this an advantage:


$UV:=@Unique(MTNO);
@Trim(@Explode(@Unique(@Replace($UV+";"+MTNO;$UV+";"+$UV;";"))))



0
 
kaliosAuthor Commented:
Zvonko, I used this,

@Trim(@Explode(@Unique(@Replace(@Unique(MTNO)+";"+MTNO;@Unique(MTNO)+";"+@Unique(MTNO);";"))))

this is eliminating duplicates and showing only values from the field that are not duplicates.
I want to see only duplicates.

0
 
CRAKCommented:
Wow....
Such a simple question and such a load of comments.
Good point Sjef: @Unique instead of @Trim! We should have some kind of a remote desktop installed. I could use a "pssst" like that every now and then!
;-))
0
 
ZvonkoSystems architectCommented:
My tests yield only duplicate values.
What were your values?
Are you sure that your values are separated to multiple values? Look in Document item properties.

0
 
Sjef BosmanGroupware ConsultantCommented:
I cannot recollect who it was with the duplicate values in a multiple value variable. Blast! There was a post about how to obtain only the duplicates from a list of values. Can't think of the wasy to do that anymore. Aaaaargh! Alzhomer again...
0
 
ZvonkoSystems architectCommented:
You mean it was shorter than my @formula?!?  :-)
0
 
Sjef BosmanGroupware ConsultantCommented:
No, eh, not shorter but, eh, better...

Sjef :)
0
 
kaliosAuthor Commented:
The values I've in the MTNO field in one of the record are as follows:

1172235198   ; 1142313824   ; 1124166688   ; 1171098812   ; 0605377812   ; 0671364626   ; 0699190916   ; 2796283992   ; 2701033039   ; 2796284565   ; 2649401222   ; 2628401223   ; 2649401222   ; 2628401223   ; 0203817672   ; 3588207244   ; 0202817776   ; 3378139277   ; 2371186309   ; 2376268516   ; 2399243455   ; 3355167738   ; 2317176392   ; 2265003840   ; 2314075706   ; 2324075709   ; 2344075704   ; 2265003158   ; 3347085949   ; 3347086546   ; 3359127978   ; 3357167700   ; 2890473651   ; 1004722834   ; 1018180338  

There are 35 values in the MTNO field above.

In the above values 2628401223 is duplicate. It is there 2 times.
Now I only want to display this value in the new column.  But instead it is displaying all.
0
 
Sjef BosmanGroupware ConsultantCommented:
Got it again!

n:= @Explode("0 1 2 3 4 5 6 7 8 9"; " ");
nn:= @Subset(n *+ n; @Elements(MNTO));

@Replace(@Trim(@Replace(nn; @Replace(@Unique(MNTO); MNTO; nn); "")); nn; MNTO)
0
 
Sjef BosmanGroupware ConsultantCommented:
Sorry, I checked (with MY formula ;), there are two duplicates:
2649401222  and 2628401223  
0
 
Sjef BosmanGroupware ConsultantCommented:
If you have many values, my formula will be a problem, most certainly in a view. If necessary, i.e. when there are more than 99 documents, but less than 1000, replace
   nn:= @Subset(n *+ n; @Elements(MNTO));
with
   nn:= @Subset(n *+ n *+n; @Elements(MNTO));
0
 
CRAKCommented:
> Sorry, I checked (with MY formula ;), .....

You sound like a salesman Sjef!
<LOL>

Come on Zvo! It's <|;-) 's turn!
0
 
Stan ReeserTechnology StrategistCommented:
Now I rememeber why I avoid formulas like the plague!!! LOL
0
 
kaliosAuthor Commented:
Splendid. I used this and it works.....
n:= @Explode("0 1 2 3 4 5 6 7 8 9"; " ");
   nn:= @Subset(n *+ n *+n; @Elements(MNTO));

@Replace(@Trim(@Replace(nn; @Replace(@Unique(MNTO); MNTO; nn); "")); nn; MNTO)

thanks
Kalios
0
 
CRAKCommented:
sreeser,
Don't you agree that formula's like this demonstrate the power if @functions?
Especially list operations... @Exlpode, @Implode, @Word, @Unique, @Trim.... you always need to write (or load) additional functions (with temporary variables, loops etc.) in LS to archieve the same!

How would you solve this then? Read the values, loop through the resulting array and.... store duplicates in a hidden field, which can then be showed in the view? Yak! ;-))
I have a feeling that kalios is better off with the current solution!

Of course, LS also has great advantages, but @functions are often underestimated! This was a demonstration of true art!
<LOL>
0
 
Sjef BosmanGroupware ConsultantCommented:
CRAK,

> This was a demonstration of true art!

Tears come in my eyes... It's too much

Sjef :D

Kalios, these formulae tend to make a view  s l o w , for the first two lines are evaluated for every document again. I suppose you only have a few documents matching the original condition, and that the view has a temporary nature, so it won't matter much. Normally, it's better to put long formulae in a computed-field, saves a lot of time in the view.
0
 
CRAKCommented:
A ha.... this is where sreeser is going to post the ultimate solution!
;-))
0
 
ArunkumarCommented:
Zvo Bro !!!

you are here ?  Wow... I was just mentioning in one of my questions that I am missing you all...  Come join my question. We can have our usual casual chat.

:-)
Arun.
0
 
ZvonkoSystems architectCommented:
Hi Bro!

Which Q do you mean?
http:Q_20933940.html
http:Q_20910237.html

I will travel next morning to Eastern Holidays and will be back on Tuesday.
0
 
CRAKCommented:
The 1st one Zvo... the -40.html
0
 
ZvonkoSystems architectCommented:
Ah, ok, thanks. See you there.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 10
  • 9
  • 7
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now