Forum Discussion

DerekBanker's avatar
DerekBanker
Qrew Member
2 months 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 to process further. Here’s a breakdown of the fields I’m working with:

  • [Related Modification - Operator Access]
  • [Related Project - Operator Access]
  • [Related Phase - Operator Access]
  • [Note Replying To - Operator Access]
  • [Related User - Affiliation]

Quickbase’s formula language doesn’t support loops, assignments, or a direct Unique() function to filter out duplicates. Here are some of the approaches I’ve tried:

  1. Using Conditional If Statements
    I set up If statements that check each field’s value with Contains to see if it’s already present in the accumulating list. While this approach works in theory, I have been having trouble with type and syntax errors. It also quickly becomes verbose and difficult to maintain as the list of fields grows.
var text initialList = ToText([Related Modification - Operator Access]);

var text withProject = 
    If(not Contains(ToText(initialList), ToText([Related Project - Operator Access])), 
        List("; ", ToText(initialList), ToText([Related Project - Operator Access])),
        ToText(initialList));

... and so on for each field
  1. Using List and Split Functions with Unique() Equivalent
    I attempted to split the concatenated list and simulate unique functionality by adding fields conditionally. Unfortunately, Quickbase lacks a direct Unique() function for removing duplicates from lists, so this approach didn’t work as expected.

Help Requested

Has anyone found an efficient way to concatenate and de-duplicate multi-select text fields in Quickbase? Ideally, I’d like a solution that’s concise, scalable, and doesn’t require manually adding each field with repeated If and Contains checks. Are there workarounds, or perhaps Quickbase functionality I’m overlooking, that could streamline this task?

I would much prefer to keep the processing in the table as it pulls from other tables where the data can change. So it needs to be quite responsive and I don't want to have to declare an excessive amount of triggers. 

Any insights, examples, or alternative approaches would be greatly appreciated!

  • Mez's avatar
    Mez
    Qrew Cadet

    I had to solve a similar problem but it was limited to just one field. Since you're joining several fields with a semicolon, this should work nearly the same; keep the formula field using List() as you have, and then trigger the pipeline on new event for mods to this field. 

    I have two fields on my table: [List of IDs], [Unique list of IDs]

    I decided to use pipelines so I could use jinja where unique and sort are available. Here is the code I used taking the raw list of IDs in field [List of IDs] and valuing the other field [Unique list of IDs] with this. Code for the 'update' to this field [Unique list of IDs]. Hope this helps.

    {# get unique list of ids from text field with IDs #}
    {% if a.list_of_ids %}
    {% set ids = namespace(value=[]) %}
    {%- set strIds = a.list_of_ids -%}
    {% for itm in strIds.split(";") %}
    {% set ids.value = ids.value | append(itm) %}
    {% endfor %}
    {{ (ids.value | unique | sort) | join(",") }}
    {% else %}
    {% endif %}

     

  • 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

      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))