Forum Discussion

DerekBanker's avatar
DerekBanker
Qrew Member
15 days ago

In-App Formulas - Removing Duplicates From A List or String

I'm working on a formula to concatenate values from multiple multi-select text fields while removing any duplicates in the output. My goal is to create a unique, semicolon-delimited list that’s easy ...
formulas
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
15 days ago

Here is an example of a generic formula to remove duplicates.   You should be able to copy paste and simply insert you own field name.

var text RawList = [insert you field which has the possibly duplicated values in a semi colon delimited list];

var text A = Trim(Part($RawList,1,";"));

var text B = If(not Contains($A,Trim(Part($RawList,2,";"))), List("\n", $A, Trim(Part($RawList,2,";"))), $A);

var text C = If(not Contains($B,Trim(Part($RawList,3,";"))), List("\n", $B, Trim(Part($RawList,3,";"))), $B);

var text D = If(not Contains($C,Trim(Part($RawList,4,";"))), List("\n", $C, Trim(Part($RawList,4,";"))), $C);

var text E = If(not Contains($D,Trim(Part($RawList,5,";"))), List("\n", $D, Trim(Part($RawList,5,";"))), $D);

var text F = If(not Contains($E,Trim(Part($RawList,6,";"))), List("\n", $E, Trim(Part($RawList,6,";"))), $E);

var text G = If(not Contains($F,Trim(Part($RawList,7,";"))), List("\n", $F, Trim(Part($RawList,7,";"))), $F);

var text H = If(not Contains($G,Trim(Part($RawList,8,";"))), List("\n", $G, Trim(Part($RawList,8,";"))), $G);

var text I = If(not Contains($H,Trim(Part($RawList,9,";"))), List("\n", $H, Trim(Part($RawList,9,";"))), $H);

var text J = If(not Contains($I,Trim(Part($RawList,10,";"))), List("\n", $I, Trim(Part($RawList,10,";"))), $I);

var text K = If(not Contains($J,Trim(Part($RawList,11,";"))), List("\n", $J, Trim(Part($RawList,11,";"))), $J);

var text L = If(not Contains($K,Trim(Part($RawList,12,";"))), List("\n", $K, Trim(Part($RawList,12,";"))), $K);

var text M = If(not Contains($L,Trim(Part($RawList,13,";"))), List("\n", $L, Trim(Part($RawList,13,";"))), $L);

var text N = If(not Contains($M,Trim(Part($RawList,14,";"))), List("\n", $M, Trim(Part($RawList,14,";"))), $M);

var text O = If(not Contains($N,Trim(Part($RawList,15,";"))), List("\n", $N, Trim(Part($RawList,15,";"))), $N);

var text P = If(not Contains($O,Trim(Part($RawList,16,";"))), List("\n", $O, Trim(Part($RawList,16,";"))), $O);

var text Q = If(not Contains($P,Trim(Part($RawList,17,";"))), List("\n", $P, Trim(Part($RawList,17,";"))), $P);

var text R = If(not Contains($Q,Trim(Part($RawList,18,";"))), List("\n", $Q, Trim(Part($RawList,18, ";"))), $Q);

var text S = If(not Contains($R,Trim(Part($RawList,19,";"))), List("\n", $R, Trim(Part($RawList,19,";"))), $R);

var text T = If(not Contains($S,Trim(Part($RawList,20,";"))), List("\n", $S, Trim(Part($RawList,20,";"))), $S);

var text U = If(not Contains($T,Trim(Part($RawList,21,";"))), List("\n", $T, Trim(Part($RawList,21,";"))), $T);

var text V = If(not Contains($U,Trim(Part($RawList,22,";"))), List("\n", $U, Trim(Part($RawList,22,";"))), $U);

var text W = If(not Contains($V,Trim(Part($RawList,23,";"))), List("\n", $V, Trim(Part($RawList,23,";"))), $V);

var text X = If(not Contains($W,Trim(Part($RawList,24,";"))), List("\n", $W, Trim(Part($RawList,24,";"))), $W);

var text Y = If(not Contains($X,Trim(Part($RawList,25,";"))), List("\n", $X, Trim(Part($RawList,25,";"))), $X);

var text Z = If(not Contains($Y,Trim(Part($RawList,26,";"))), List("\n", $Y, Trim(Part($RawList,26,";"))), $Y);

$Z

  • DerekBanker's avatar
    DerekBanker
    Qrew Member
    15 days ago

    Hello Mark, 

    Thank you for your reply! I saw that as a potential solution, but considering the age of the post, I was hoping there might be something newer and cleaner. 

    Here's my implementation.

    var text RawList = List("; ",
   ToText([Related Modification - Operator Access]),
   ToText([Related Project - Operator Access]),
   ToText([Related Phase - Operator Access]),
   ToText([Note Replying To - Operator Access]),
   ToText([Related User - Affiliation])
);

var text A = Trim(Part($RawList, 1, ";"));

var text B = If(not Contains($A, Trim(Part($RawList, 2, ";"))),
   List("; ", $A, Trim(Part($RawList, 2, ";"))),
   $A);

var text C = If(not Contains($B, Trim(Part($RawList, 3, ";"))),
   List("; ", $B, Trim(Part($RawList, 3, ";"))),
   $B);

var text D = If(not Contains($C, Trim(Part($RawList, 4, ";"))),
   List("; ", $C, Trim(Part($RawList, 4, ";"))),
   $C);

var text E = If(not Contains($D, Trim(Part($RawList, 5, ";"))),
   List("; ", $D, Trim(Part($RawList, 5, ";"))),
   $D);

Split(Trim($E))