Merging records and retaining category data

[thegasman2000]

I have a table with 1000 rows containing films information. I have a field called provider, which lists Netflix amazon prime etc. The issue is when a title is on both amazon and Netflix I end up with 2 rows. The provider field has their respective provider names in and the field itself is a multi-select type. What I want is one record but with the provider, field to contain both. Like "Netflix, amazon" How do I go about this? It needs to be a dynamic automation. as when Netflix removes it from their library I want to retain the record but remove just that category. I have views set up filtering by the value of this provider field too. 

I added the dedupe extension but don't see how to get what I need from it. 

Thanks 

[TheTimeSavingCo]

Hm, I'm going to assume you're just talking about cleaning up your current data, let me know if I'm making the wrong assumption here

Assuming the only data you want to keep is the name and the provider, you could:
1. Create a linked field to a new table
2. In your original table, paste all of "Title" values of the records into the linked field
  - This will result in your new table having one record per title only
3. In your new table, create a lookup field on the "Provider" field
  - This should now show "Netflix, Amazon" if a title is available on both providers
4. In your new table, convert the lookup field into a multi select field
5. In your original table, convert the linked field into a single text field

You should now have a new table that contains one record per title, with a multi select field that shows the providers

[thegasman2000]

I think it's easier I show you for clarity. 

Here is an example record that has all the data for a certain film Sonic The Hedgehog 2. 

As it is currently available on two platforms, Amazon Prine and Paramount Plus, my data importing scripts have brought it in twice. The data is identical except for the multi-select field "provider". What I am after is for the table to dynamically find duplicate entries, using a number of fields such as IMDB_ID for example, and combining them so I have one entry with both providers selected in the multi-select field. 

Your solution, if I understand right, is to copy all titles, unique titles, into another table and use a lookup field for the provider information. I'm not sure that makes much sense here as I still need all the other data. 

Thanks 

[thegasman2000]

I just figured out how to do what I want in the dedupe extension but I have to do it manually. Can I automate this? 

[TheTimeSavingCo]

> Your solution, if I understand right, is to copy all titles, unique titles, into another table and use a lookup field for the provider information. I'm not sure that makes much sense here as I still need all the other data.
Yeah, kinda.  You'd end up with:

You could manually create rollup fields with the formula `ARRAYUNIQUE()`to grab the rest of the data and you'd end up with exactly what you want I think.  To fully automate it you'd have an automation that'd trigger if the linked field was empty and its action would be to paste the title value into the linked field
Link to base

If you didn't want to create rollup fields the only way I can figure to solve this would be with a script I reckon.  Maybe someone else has an idea

re: I just figured out how to do what I want in the dedupe extension but I have to do it manually. Can I automate this? 
I don't think so I'm afraid

[thegasman2000]

For anyone else who is trying to figure this out... I managed it with automation as shown below. Just to make it super clear 🙂