Help

Cleaning up unused Multiple Select data

733 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Jason_Fairchild
4 - Data Explorer
4 - Data Explorer

I’m working on a directory that has a Categories column set to the Multiple Select field type.

I’ve cleaned up a good number of mistyped entries in this column, which created a lot of unused categories.

Whenever I filter by a category, I see all of the unused data in the list. Is there any way I can remove all unused data?

1 Reply 1
dataLizard37
5 - Automation Enthusiast
5 - Automation Enthusiast

Try this script!

let settings = input.config({
    title: `Identify and remove select field options that are not in use`,
    items: [
        input.config.table("table", { label: `Table` }),
        input.config.field("field", { parentTable: `table`, label: `Field` }),
    ],
});

let { table, field } = settings;

let fieldData = table.getField(field.id)
let fieldType = fieldData.type
if(fieldType != "singleSelect" && fieldType != "multipleSelects" ) throw("Only works with single select or multiple select fields")

let query = await table.selectRecordsAsync({fields: [field]})
let inUse = new Object;
for (let r of query.records){
    let selectedOptions = r.getCellValue(field)
    if(selectedOptions){
        if(fieldType === "multipleSelects"){
            for(let option of selectedOptions){
                inUse[option.name] = 1
            }
        }
        else{
            inUse[selectedOptions.name] = 1
        }
    }
}

let updatedOptions = new Array
let notInUse = new String

for(let option of field.options.choices){
    if(inUse[option.name]){
        updatedOptions.push(option)
    }
    else{
        notInUse = notInUse + "\n - " + option.name
    }
}
if(notInUse === ""){
    output.text("All select field options are in use")
}
else{
    output.text("The following options are not in use:" + notInUse)
    let proceed = await input.buttonsAsync('Proceed with deletion of the listed options? *CAUTION: This cannot be undone.*', ['Delete', 'Do nothing']);

    if (proceed === "Delete") {
        output.text('Deleting..');
        await field.updateOptionsAsync(
            {choices: updatedOptions},
            {enableSelectFieldChoiceDeletion: true},
        )
        output.text('Done');
    } else {
        output.text('Nothing deleted.  Ending.');
    }
}