Roll up array unique - reflecting single data twice

Topic Labels: Formulas
434 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Why is array unique formula considering a single data twice? 

I have typed multiple topics and when I use the formula I can see topics repeated. Is there anyways to avoid repetitions?

1 Reply 1
Community Manager
Community Manager

Hi there!


ARRAYUNIQUE doesn't quite work as expected when performing on rollups and linked record fields in certain scenarios. For example, let's say you have an event record linked to multiple customers, and you're pulling in the products these customers use (also linked records) via a lookup field. You can see in this example all of the duplicate products that are being pulled in via the lookup, because it's pulling in the results for all the customers it's linked to.

The Gala event is linked to customers A and B, so the product lookup is showing us all the products that both customers are linked to, which includes any instance in which they are linked to the same product.



Let's say you want to get a de-duped list of every product an event will be using, and remove any of the duplicative results. This won't work using ARRAYUNIQUE() either in a formula or a rollup. You can see in both options below, that it doesn't prevent the duplicate products in all scenarios.



In short, what's happening here is that ARRAYUNIQUE returns the unique combinations of linked records. It is the set that is being operated on (which is why it's working for the Party event - which has two repeating instances of PY, PZ). We don't currently have a way for ARRAYUNIQUE to return the unique subcomponents - like all the other instances you see above.


To work around this limitation, you can create a lookup field of the linked product field back in the Customer table...



and then perform the ARRAYUNIQUE() function on that product lookup field within a rollup to get the desired unique results:





Hope this helps point you in the right direction!