Joined List with only unique values

[MattLB]

Is their a standard methodology for creating a JL that contains only Unique values from the query/relation? E.g. -

1 | a
2 | a
3 | b
4 | c
5 | a
6 | b

would yield a,b,c

Thanks

[Darren_Murphy]

A bit of a round about way is as follows:

• Start with a joined list
• Convert that to an array with split text
• Make that unique with Unique Elements
• Take another joined list from the unique array

Another way would be to create a column in the source table that only contains unique values:

• Add a RowID column
• Create a single relation that matches the value you are concerned with with itself
• Use a lookup to fetch the RowID via the relation
• Add an if-then-else column:
  – If RowID is lookup RowID, then value
• Take a joined list of the if-then-else column

[MattLB]

@Darren_Murphy

• Start with a joined list
• Convert that to an array with split text
• Make that unique with Unique Elements
• Take another joined list from the unique array

I do the former (the above) and the initial queries can return 1,000+ results which I convert/convert/convert to get to the unique array/list. Final unique arrays have dozens or scores of unique items.

Is the alternative method better/more efficient or scales better? I can use this since most my tables have RowIDs. Not really sure what is efficient/fast/scale within Glide.

[burningmikey]

Did you consider using a JavaScript column?

[Darren_Murphy]

A third alternative would be JavaScript, as I see @burningmikey just suggested. You could feed your original Joined List into that and write some code to remove duplicates.

To be honest, I’m not sure which would be most efficient. If you’re dealing with a large number of rows, then I’d probably steer away from option 2, as self relations have been known to cause performance issues with very large tables.

[MattLB]

@burningmikey - not a Javascript person so I did not.

@Darren_Murphy - thanks as always, will stay the course since it works - just “looks ugly”

PS. I think Joined lists should offer a ‘unique only’ check box when creating. Would mirror “count unique” in Roll-ups to give you the list of unique items in the count.

[burningmikey]

Sounds good.

You know you can always use chatGPT to generate JavaScript for you. You just explain what you want to do, add the format of your data, show an example of the output you want and it will do the work for you.

[Darren_Murphy]

Just in case you wanted to give the JavaScript option a try:

const array = p1.split(', ');
const unique = [...new Set(array)];
return unique.join(',');

[MattLB]

@Darren_Murphy

const array = p1.split(', ');
const unique = [...new Set(array)];
return unique.join(',');

Worked as expected. Thanks…now another tool in the old toll box!