Forum Discussion

TimT's avatar
TimT
Qrew Trainee
2 years ago

Removing duplicates from a textlist

Hello! Was hoping I can get help with this problem. I'm using a query formula to get a list of emails from another unrelated table and it works just fine, however, it gives me duplicates which i...
formula queries
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
The trick is to flag the source record with a flag on the source record as to it its's the Record with the lowest Record ID# of all its "brothers".

I can help with the syntax if need be but pull a text list Query within the source table to pull back all the records with the same email address as the record.  Pull in the value of Field ID # 3 which is the Record ID#. into a text list query result then convert to regular text and hive off the leftmost value.  That will be the lowest record # of all the brothers with the same email address.  Then have an IF statement formula to check if the Record ID# of the brother is the lowest one and if so flag it as OK to pull.

The adjust your current formula query to only pull in ones that are flagged as being OK to pull.​​

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
TauryAnderson1's avatar
TauryAnderson1
Qrew Member
2 years ago
I like your solution for removing duplicates, but now I have on that is more complicated.  My formula query pulls text values from it's sibling records where we might have a list like this:

red
blue
green
green
blue
red
red

I want my formula query result to be:

red; blue; green

not 

red; blue; green; green; blue; red; red

I am basically trying to use a Formula Query to get Summary field results from sibling records. 

(There is a wide ranch of variability of choices in the field in question, so I cannot call out each option individually in a formula)

------------------------------
Taury Anderson
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    2 years ago
    Can you post your formula query code?

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • TauryAnderson1's avatar
      TauryAnderson1
      Qrew Member
      2 years ago
      var number field = 311;
      var text deebid = [_DBID_ITEM_DETAIL];
      var text PETfilters = "{'314'.EX.'" & [Related Project (formula)] & "'}"
      & "AND{'400'.EX.'" & [Substrate Size] & "'}"
      & "AND{'368'.EX.'" & [PET Bottle Color] & "'}"
      & "AND{'367'.EX.'" & [Label/Substrate Type] & "'}"
      & "AND{'271'.EX.'" & [Label/Substrate Supplier] & "'}"
      & "AND{'370'.EX.'" & [Closure Finish] & "'}"
      & "AND{'371'.EX.'" & [Closure Color] & "'}";
      var text CANfilters = "{'314'.EX.'" & [Related Project (formula)]&"'}"
      & "AND{'400'.EX.'" & [Substrate Size] & "'}"
      & "AND{'374'.EX.'" & [End/Lid Color] & "'}"
      & "AND{'163'.EX.'" & [Tab Color dropdown] & "'}"
      & "AND{'252'.EX.'" & [TabType] & "'}";
      var text GLASSfilters = "{'314'.EX.'" & [Related Project (formula)] & "'}"
      & "AND{'400'.EX.'" & [Substrate Size] & "'}"
      & "AND{'369'.EX.'" & [Glass Bottle Color] & "'}"
      & "AND{'367'.EX.'" & [Label/Substrate Type] & "'}"
      & "AND{'271'.EX.'" & [Label/Substrate Supplier] & "'}"
      & "AND{'370'.EX.'" & [Closure Finish] & "'}"
      & "AND{'371'.EX.'" & [Closure Color] & "'}";
      var text ALUMITEKfilters= "{'314'.EX.'" & [Related Project (formula)]&"'}"
      & "AND{'400'.EX.'" & [Substrate Size] & "'}"
      & "AND{'370'.EX.'" & [Closure Finish] & "'}"
      & "AND{'158' .EX. '" & [Related End/Closure] & "'}"
      & "AND{'371'.EX.'" & [Closure Color] & "'}"
      & "AND{'91' .EX. '" & "CAN" & "'}"
      & "AND{'157' .EX. '"& "BOTTLE CAN" & "'}";

      var text filters = If([Primary Substrate]="PET", $PETfilters,[Primary Substrate]="GLASS", $GLASSfilters,[Primary Substrate]="CAN" and [Container Design dropdown]="BOTTLE CAN", $ALUMITEKfilters, [Primary Substrate]="CAN",$CANfilters);

      var text query = ToText(GetFieldValues(GetRecords($filters, $deebid), $field));

      $query

      ------------------------------
      Taury Anderson
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        2 years ago
        This might work as a brute force solution.

        var text RawList = ToText([My Query field with duplicates]);

        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

        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------