Avoiding duplicate values in lookup fields

[Sri1]

Hello'

I am trying to create a table 'Customer Interested Events' which maintains basic customer information (customer e-mail is PK) and additional fields  (selectable fields) 'Event Category' , 'Event Location'.

Based on the customer interested events another table 'Event Manager' would be populated which is linked with above table 'Customer Interested Events'. Purpose of this table is to assign a suitable manager against each Event Category. The PK in this table is a computed field "Event Category+Location" and has additional fields like 'customer name', 'location','event category' (lookup fields from the linked table), 'event manager'.

I am trying to maintain one unique record against each (event category, location) with all corresponding customer names, locations merged in the respective fields (if multiple customers booked for the same event category, location) so that one manager can be assigned.

While merging customers against an (event category,location), I could see PK  has repeating values as lookup fields have duplicate values, if multiple customers booked for same event,location.

Please suggest how we can avoid duplicate values in lookup fields 

Thanks

[kuovonne]

You cannot remove duplicates from lookup fields. You can convert the lookup field to a rollup field and use the ARRAYUNIQUE(values) function to remove duplicates. Note that if you are pulling values from two or more tables away, correctly configuring the rollup and lookups involved can get a bit trickier. 

[Sri1 · Accepted solution]

I found an alternate way to achieve this. Thanks for sharing feedback in short time.

Thanks

[Thomas_de_Beauc]

Hi 👋, I came up with a pretty satisfying solution using the Scripting automation. You can find it here ✨