Removing duplicates from a string

[Mar_Castro]

Hi all!!
I have a list of participants in a project, but as this list of participants is a lookup from several tasks linked to the project, it has duplicated items. I want to count the number of unique participants.

I’ve tried transforming this list of participants into a string, I know how to count the number of participants in that string but I need to remove the duplicates. Does anybody know how to remove duplicates from a string?

I know how to remove completely a participant from the string (using SUBSTITUTE), but I don’t know how to make it appear only once.

SUBSTITUTE({Participants}, Find, “”)

(Find is the name of the table with the name of the participant I want to remove)

Thanks in advance!!!

[Justin_Barrett]

ScottWorld:

This is extremely unintuitive & unexpected behavior to me!

ARRAYFLATTEN() only works with an array that contains other arrays, and a rollup of a linked record field doesn’t produce that. Linked record fields feel like they should produce arrays, but they produce comma-separated strings. My gut says that’s where a lot of the confusion comes from, not just for you but for a lot of others. Because links are a collection, the easy assumption is that Airtable gives you an array when using that link field anywhere else. The only place you do get an array from a linked record field is in JavaScript (i.e. the Scripting app, or a scripting action in an automation).

[ScottWorld]

Thanks for much for chiming in, @Justin_Barrett.

So linked records are a “collection” instead of an “array”?

You mentioned that we get linked records as an array via JavaScript. It also seems like we also get linked records as an array when using the API (via a simple CURL request or via an automation platform like Integromat or Zapier).

I wonder if Airtable could simplify this across the board, and always make linked records act like arrays — even within the Airtable interface itself. I have been using Airtable for years now, and didn’t realize any of this! :crazy_face: But that might be because I’ve been using Integromat & the API a lot.

[Justin_Barrett]

ScottWorld:

So linked records are a “collection” instead of an “array”?

My gut says that linked record fields are most likely stored as arrays internally. Why they’re not presented as arrays through Airtable formulas is a mystery. Sadly, we’re not likely to see it change because so many formulas exist that expect linked fields to output strings.

ScottWorld:

You mentioned that we get linked records as an array via JavaScript. It also seems like we also get linked records as an array when using the API

True. I was just focusing on internal scripts.

[kuovonne]

Justin_Barrett:

My gut says that linked record fields are most likely stored as arrays internally.

Justin, thanks for chiming in on this thread, and this previous thread on the same topic, where you first explained this issue to me.

The read format for lookup fields is now published as an array in the documentation for scripting and custom apps. However, these arrays contain slightly different data. In scripting, you get an array of field values (along with some other data not mentioned in the documentation). In custom apps, the array is an array of objects that contain both the field value and the linked record id. This tells me that under the hood, the information is not stored exactly as it appears when using the api.

[Justin_Barrett]

kuovonne:

The read format for lookup fields…

I appreciate the breakdown on lookup fields, but I was talking about linked record fields in that comment you quoted. :slightly_smiling_face: Also, I was speculating on how they are stored internally by Airtable. In most cases, I imagine that the internal representation is very close to how Airtable presents the data via various APIs. Assuming that links are stored internally in arrays, and with many (if not most) users also assuming something similar when they first start interacting with linked record fields via formulas, it’s even more of a mystery why linked record fields are passed to formulas as strings and not arrays. Arrays are easily turned into strings, but Airtable’s formulas provide no ways of making arrays (cue the long-standing request for a SPLIT() function), so what benefit is there in pre-converting an array of links into a string that a) instantly limits what the user can do, and b) forces them to add a lookup field to create the array that a link field could have passed?

[kuovonne]

Justin_Barrett:

but I was talking about linked record fields in that comment you quoted.

Oops. I must have gotten distracted.

Justin_Barrett:

I imagine that the internal representation is very close to how Airtable presents the data via various APIs.

I agree.

Justin_Barrett:

it’s even more of a mystery why linked record fields are passed to formulas as strings and not arrays.

Notice that lookup fields have the exact same problem, only in reverse.

People expect lookup fields to behave like strings in formulas, but they don’t. We have to manually convert lookup fields to strings in the formula if we want to use them in functions that expect string inputs.

Maybe Airtable was trying to avoid this confusion, which is far more common than the issue of trying to flatten arrays of linked records.

Justin_Barrett:

Sadly, we’re not likely to see it change because so many formulas exist that expect linked fields to output strings.

I wish there were a way for formula fields to automagically do type conversion for linked record and lookup fields. If the value is inside an array function, convert it to an array of values, otherwise convert it to a string. But I guess that it is just too hard to tell with rollup formulas.

[Mar_Castro]

Wow!! It’s working!!
@kuovonne @Justin_Barrett and @ScottWorld Thank you so much for your help!!

Today I’ve learned a lot about Airtable, and yet I feel as there are so many things I still don’t know :grinning_face_with_big_eyes:

[ScottWorld]

Justin_Barrett:

Arrays are easily turned into strings, but Airtable’s formulas provide no ways of making arrays (cue the long-standing request for a SPLIT() function), so what benefit is there in pre-converting an array of links into a string that a) instantly limits what the user can do, and b) forces them to add a lookup field to create the array that a link field could have passed?

This is a great example of why I wish Airtable had some sort of a product manager who was paying attention to all of these things, and then addressing these flaws in the product.

Or at the very least, addressing these issues in the formula documentation.

Otherwise, we’re just left trying to to figure all of this out for ourselves.