Exclude Duplicate/Repeating Values via Lookup or Rollup

[bredah]

Hi everyone! I have an Airtable base for a virtual event my team is planning for Learning at Work Week. The base includes the following:

• An "Activities" table with all of the activities that learners can complete, each worth a certain point value. The people who have the most points at the end of the week win a prize! 
• A form (linked here) where learners input their email address (their identifier) and can multi-select (via a lookup from the Activities table) the different activities they did during the week. The form can be submitted throughout the week as various activities are completed, or at the end of the week once all activities are done - it's the users' choice! 
• A "Form Responses" table to store all form responses.
• A "Participants" table that is automated to create a record that links the users' email address to their form response(s) as they're submitted.

Within the "Participants" table, I have a lookup field that lists all of the activities that that user logged via the form. This is fine, but we are trying to plan for people forgetting which activities they logged. If they submit the form multiple times throughout the week (which we're encouraging) and they forgot what activities they already logged, they might pick the same option again when submitting their response, thus creating duplicate activities within that lookup field. For example, if Bob filled out the form twice and selected "Listening to the Sustainability podcast" both times, it's going to count twice towards his activities (and his overall points), even though all activities are only intended to be completed once. 

I tried to do this using a rollup with the formula ARRAYUNIQUE(values) but duplicate values were still included. Does anyone have any ideas?

Thankful for any help you can provide! 

[anton-vatech-io]

Hey Bredah,

In the Form Responses table you can set Link field to "Filter record selection by a condition" and set that Participants has none of Participants. 

Given that you name all Participants link fields the same.

Thanks,

[AlliAlosa · Accepted solution]

This is because a lookup of a multi select field is technically an array of arrays - a list of lists.

Say submission 1 contains options A and B, and submission 2 contains options A, B, and C.

When you lookup or rollup these values, airtable sees them as [["A", "B"], ["A", "B", "C"]].

In other words, it doesn’t look at the individual values, it looks at each submissions’ options as a whole. That’s why ARRAYUNIQUE() doesn’t work in this case. Theoretically, you should be able to use a combination of ARRAYUNIQUE() and ARRAYFLATTEN(), but... apparently, this is a bug that still hasn't been fixed when it comes to multi-select fields.

Check out this post: https://community.airtable.com/t5/other-questions/rollup-array-unique/td-p/116315

In it, the brilliant @kuovonne and @ScottWorld provide a workaround to get this to function how you would want.

To quote Kuovonne: 

1. Create a rollup field that rolls up the multi-select field. Set the formula to values. Save the rollup field.
2. Convert the rollup field to a lookup field. Notice that the lookup will be formatted as plain text instead of the usual pill format.
3. Create a new formula field that refers to the lookup field (that used to be a rollup) and use the formula ARRAYJOIN(ARRAYUNIQUE(ARRAYFLATTEN({lookup field})), ", ")