Lookup Field filtering/limiting number of items shown

[Jasonl2l]

What is the proposed idea/solution?

User would like to limit the items show to unique values only.

I.e. when a lookup field references a multi-select/single-select field from a linked record. Suppose there is 20 linked records, each of which has a multiselect cell filled in with 3 available options, Name 1, Name 2, Name 3. Supposed in the list of 20 linked records, there are 7 with the name 1 option selected, 9 with the name 2, and 4 with name 3. In the lookup field, I will see all iterations listed out, ("name 1", "name 1", "name 1", etc.... "name 3." totaling a string of 20 names. Ideally the user would like to just see "name 1" once, "name 2" once, and "name 3" once, since all of the other iterations are just duplicates. If the items are not unique, they are not duplicated in the string.

The best way to do this would be to add an additional toggle switch for "Limit the items shown to unique values."

How does is solve the user problems?

Consolidates data, making it both more printable, easier to read, and removes the need to have an additional formula field to accomplish the same effect. 

How was this validated?

Not sure if this question is applicable. 

Who is the target audience?

Any user primarily working in project management, task delegation, and anyone who like to run automated reports. 

[ScottWorld]

Actually, there is a little-known trick to making this work:

1. Start with your linked record field.

2. Create a lookup field that looks up your multi-select field (based on the linked record field).

3. Create a formula field with this formula:

ARRAYUNIQUE({Your Lookup Field Here})

This is one of the biggest secrets in the Airtable universe — most Airtable employees themselves don't even know this trick!

p.s. If you ever need to hire an expert Airtable consultant to help you with anything, please feel free to contact me through my website: Airtable consulting — ScottWorld

[Sho]

Hi @ScottWorld, 
Does it really work?
I have never been able to use the ARRAYFLATTEN function the way I want.

In my case, I use the Rollup field and ARRAYUNIQUE(values) to remove duplicates if it is a multi-select.

[ScottWorld]

I am unable to get ARRAYUNIQUE(values) to work with a multi-select field, because Airtable sees each record as a separate array. But try my solution above and see if it works for you!

[Sho]

Curiously this is works with Rollup fields. ARRAYFLATTEN is not needed.

[ScottWorld]

Can you post a screenshot of how you got it to work?

[Sho]

There was a mistake on mine. I had chosen exactly the same options, so the duplicates were just removed. Duplicates are not removed if the selections are different.
But one thing is certain: ARRAYFLATTEN does not work.

[ScottWorld]

Yep, ARRAYFLATTEN definitely doesn’t work under any circumstances in Airtable. In fact, I just realized that my formula above doesn’t even need ARRAYFLATTEN(). I just removed it from my post above. 

[Jasonl2l]

Hello,

The above unfortunately misses the point of what I am trying to accomplish.

Please take a look at the screenshots below to get a better idea of what I am try to do.

### = Companies attending the event
nnn = is the employee assigned to cover those companies.

Table 1:

Table 2:

Ideally... the "Event Coverage" column in table 1 will only ever show 3 names - since in reality there are only 3 employees attending.

However, because the employees assigned to cover each company (shown in table 2 in column "Employee Coverage") are listed in different orders, even though they are the same composite list of employees, the rollup field still treats each "employee coverage" cell as a unique value.

This of course does not occur when referencing a single select field because it is impossible to list a single option in a different order.

You will notice for event 3, that only one instance of AAA, BBB, & CCC are listed, that is because the two linked companies for that event, have the list of Employee Coverage in the same order. 

I don't think it's possible to accomplish what I am looking for with the current tools in Airtable, although I am sure you could set up some complicated formula field, I would hope to not have to set up another field to accomplish this, since it still requires the link-to-record and lookup fields, making it 3 fields needed to accomplish what should be simple.

[Tito_Alverio]

Copy and pasting a method that got what I was looking for, which sounds similar to what you're wanting, unique values from lookups, This is my reply to Airtable Support:

1. Create the rollup field in Table A on the Linked Table B’s field I want to rollup, in this case a multiselect field.
2. Take that same rollup field that was just created, and change it to a Lookup field.
   1. The lookup field will now display as text, instead of the colorful word bubbles you usually see from multi-select fields.
3. Create a formula field in the Table A, and ARRAYUNIQUE the “formerly a rollup now a lookup field” field.
4. Ta-da

Is Airtable aware of this strange behavior? If so, can we make it simpler and more intuitive to get unique values out of rollup or lookup fields?

[Jasonl2l]

Very interesting! There are so many quirks like this it's hard to keep track.