I have a multi-select field of colors. I have some records that have the same color listed mulitple times (i.e., yellow). I would like to remove the duplicates. I have tried the work-around in "Rollup Array Unique?" (make the field a lookup field and then use arrayunique(lookup field)), but I still get duplicates. Any other suggestions of how to get rid of the dupes?
+19Are you seeing two pills with yellow or one pill with yellow yellow?
edited to add: can you post a screenshot of the duplicates?
+23Hi,
turn your field type to a single-select. In field config you will have a list of values available to copy-paste. You can can paste in anywhere as usual list, sort or use other means to deduplicate.
After you have deduped list, you can paste it to a new text field. If you turn it to a multi-select, it will automatically add all options to a new field setup.
+4pressGO_design wrote:
Are you seeing two pills with yellow or one pill with yellow yellow?
edited to add: can you post a screenshot of the duplicates?
In the lookup field, I see them as pills. In the rollup field, text. Attached is a screen shot of both fields.
+19Nicole_Hillborn wrote:
In the lookup field, I see them as pills. In the rollup field, text. Attached is a screen shot of both fields.
OK. I think I know what's going on. But I might be wrong, which means that this answer won't help you. I hope it will, though.
Here's what I'm imagining:
You have a table with all of your Polishes, and that table is set up with a text field for Polish Name and a multiselect for all of the colors you have that Polish in. Maybe you're recording orders, so you have multiple records for each Polish because you order different colors at different times. So you have two records for color A - one from the time you ordered the Red, White, & Blue order and one from the time you ordered Red, Yellow & Orange. It might look something like this
Polish Name (text) | Colors (multi-select) | Order Date
A | Red, White, Blue | 7/4/22
A | Red, Yellow, Orange | 10/31/22
The issue you're having is that Airtable doesn't look at those records and think, "OK, she has 5 types of Polish A: one in Red, one in White, one in Blue, one in Yellow, and one in Orange." Airtable looks at that record and thinks, "OK, she has 2 types of Polish A: one is and Red&White&Blue and the other is Red&Yellow&Orange." There's no overlap between the two records, so Airtable sees them both as unique values. The only time you're going to be able to dedupe things is if you have 2 records with the same colors in the same order, like
A | Red, White, Blue | 7/4/22
A | Red, White, Blue | 7/4/21
A | Red, Yellow, Orange | 10/31/22
If you want to create a table where you can see at a glance what colors of polish are available to be used in Manis, you're going to need to create formula fields for each color and then rollup those fields in the Manis table. The formula fields would be something like
if(find("Red", Colors), "Red")
if(find("Blue", Colors), "Blue")
if(find("Yellow", Colors), "Yellow")
etc etc
Link to base that you can copy and play with here
Like I said above, I may be completely off-base and not understanding what you're trying to do. And it might be easier to do this with a script or a regex? I'm old school (or at least half of old school), and I like using formulas and helper fields...... If you need more help, feel free to DM me.
+4Alexey_Gusev wrote:
Hi,
turn your field type to a single-select. In field config you will have a list of values available to copy-paste. You can can paste in anywhere as usual list, sort or use other means to deduplicate.
After you have deduped list, you can paste it to a new text field. If you turn it to a multi-select, it will automatically add all options to a new field setup.
Sorry, not understanding why I would change the field to a single select. Polishes can have more than one color associated with them, so making it single select wouldn't seem to help me in the long run.
+23Nicole_Hillborn wrote:
Sorry, not understanding why I would change the field to a single select. Polishes can have more than one color associated with them, so making it single select wouldn't seem to help me in the long run.
I had to type 'duplicate' then turn, but nevertheless, it won't work as I thought.
So, you have to apply the script that will take field options, dedupe list, and create new field with deduplicated options.
Try this, but put your table name
const table=base.getTable('List')
const flds=table.fields.filter(f=>f.type=='multipleSelects')
if(!flds.length) throw new Error('No multiselect fields in this table');
const ask=(flds.length>1)? await input.buttonsAsync('Select field: ',flds.map(f=>f.name)):flds[0].name
const vals=[...new Set(table.getField(ask).options?.['choices'].map(x=>x['name']))]
output.inspect(vals)
await table.createFieldAsync(ask+'_deduped','multipleSelects',{choices:vals.map(v=>({name:v}))})
upd: tested - it doesn't set color. a few minutes to improve script
now it transfer name with color
const table=base.getTable('YOUR_TABLE_NAME_HERE')
const flds=table.fields.filter(f=>f.type=='multipleSelects')
if(!flds.length) throw new Error('No multiselect fields in this table');
const ask=(flds.length>1)? await input.buttonsAsync('Select field: ',flds.map(f=>f.name)):flds[0].name
const vals=[...new Map(table.getField(ask).options?.['choices'].map(x=>[x['name'],x['color']]))]
output.inspect(vals)
const newitem=arr=>({name:arr[0],color:arr[1]})
await table.createFieldAsync(ask+'_deduped','multipleSelects',{choices:vals.map(newitem)})
Note: new field will be added to the end of field list and might be hidden, so you will need to unhide it
+19@Alexey_Gusev I tried your script and I am getting the "no multiselect fields" error. Is that because I'm using it in the table where the colors are lookups?
+23pressGO_design wrote:
@Alexey_Gusev I tried your script and I am getting the "no multiselect fields" error. Is that because I'm using it in the table where the colors are lookups?
Yes, lookups are just reflection of data located in other table.
If you don't need to change multiselect field settings, but just want to see unique values in Rollup field, your rollup should be ARRAYUNIQUE(values).
But that won't work in your case. Multiselect field by design should not allow user to repeat choices in a single cell. But your multiselect field setup contain repeated choices, so you have two 'Yellow' in field setup and it counts as two different 'Yellow'. You can even change one of them to have lighter color, to see the difference. And therefore ARRAYUNIQUE will not deduplicate them, as they are different Yellows (even if they had same color and text).
If you don't want to run it on Polish table to avoid possible corruption of setup, you can duplicate table and try script on it. But be warned - if you duplicate table with links, it will create the same set of links, and after you remove this table, backlink fields will stay in each linked table, you need to clean them by hand. So, for test purposes, I would suggest to duplicate whole base, and test script on it. But indeed, script is harmless - it just create new multiselect field, called 'Color Family_deduped' in 'Polish' table.
+4Alexey_Gusev wrote:
Yes, lookups are just reflection of data located in other table.
If you don't need to change multiselect field settings, but just want to see unique values in Rollup field, your rollup should be ARRAYUNIQUE(values).
But that won't work in your case. Multiselect field by design should not allow user to repeat choices in a single cell. But your multiselect field setup contain repeated choices, so you have two 'Yellow' in field setup and it counts as two different 'Yellow'. You can even change one of them to have lighter color, to see the difference. And therefore ARRAYUNIQUE will not deduplicate them, as they are different Yellows (even if they had same color and text).
If you don't want to run it on Polish table to avoid possible corruption of setup, you can duplicate table and try script on it. But be warned - if you duplicate table with links, it will create the same set of links, and after you remove this table, backlink fields will stay in each linked table, you need to clean them by hand. So, for test purposes, I would suggest to duplicate whole base, and test script on it. But indeed, script is harmless - it just create new multiselect field, called 'Color Family_deduped' in 'Polish' table.
Sorry, think I need to clarify a bit. I have 2 tables-one polish and the other manis. Polish contains the multi-select field with the colors. The manis table has a lookup to the colors field in the polish table. A mani can contain more than one polish. Those polishes may have a color in common and I was trying to dedupe those colors in the mani table on the lookup field. Hope this clears things up a bit. Not sure if it changes what to do to dedupe the field.
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.