Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Separate multiselect options into separate fields

Topic Labels: Formulas
Solved
Jump to Solution
178 3
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a multi-select field that has a large number of choices. I have decided it would be better to separate them into different categories (fields). Is there a way to do this?

I have a master list of choices in a separate table (if that helps). These choices have a name and a description and a category.

Is it possible to do something like this:

Category 1 Field

  • Look at the main select fields and find all choices.
  • Look at the master choice list and find the choice name that matches the category.
  • Place the choice that matches the field category into the field.

I am on a free plan and have no access to paid tools.

Example base for explanation purposes:

1 Solution

Accepted Solutions

I managed to achieve this on my own, but in a very round-about way. For all of those reading in the future, this is what I did. Please note, that I am a newbie and know nothing about scripting or formulas. I am also on a free plan.

  1. I had the multiple select fields in my table (Main Table), filled with my selection (there are about 100 to choose from).
    image
    The above image is only showing the options that have been selected.

  2. I created a separate table (Options Table) with each choice option as the “Name”. I made sure that each option was spelled exactly the same, including punctuation and spacing. You can extract the choices manually with your fingers and eyes, or by using code (see below for code). I then added another column as the “category” as a multiple select field.
    image

  3. I created a linked record field in my Main Table that pointed to my Options Table. Make sure the allow multiple records is ON.

  4. I selected my multiple choice field in my Main Table and pressed ctrl+c on my keyboard to copy that entire column.

  5. I selected my linked field (linked to Options Table) column in my Main Table and pressed ctrl+v on my keyboard to paste the selected options. This will make the matching linked record appear in this column. At this point, everything looks like this.
    image

  6. I then created a rollup field next to the linked field and set it up like this:
    image
    image
    image

  7. Lastly, hit save.

If there were any selections made that fall under the category “width” they will appear in the rollup field.
image


Text Version in case images ever break:

Field on this table that links to the records you want to summarize
(Select the linked field from the drop-down menu. The one that is linked to Options Table.)

Options Table table field that you’d like to roll up
(Select the “Name” option)

TOGGLE ON Only include linked records from the Features table that meet certain conditions

When the filter section pops up, select

Where “Category” is “Width”

Aggregation formula which rolls up the values in each linked record

Type this into the text box:

ARRAYUNIQUE(values)

You will have to repeat steps 6 and onwards if you have more than one category. I ended up with 14 separate rollup fields to separate my 100 multiple selection options. I found it easier to duplicate the rollup field and change the field name and filter instead of creating a brand new field each time.

At this point, you can consider deleting that multiple select field (or maybe copy it somewhere else for safe keeping and THEN delete it).


You can grab the multiple select choices by using scripting. After you run the script, copy the list of choices and paste it into your Options Table. There will be a blank row between each choice, but I don’t know how to fix that. :frowning:

This code is not mine and was provided by someone else.

let table = base.getTable("TABLE NAME")
let multipleSelectField = table.getField('MULTIPLE SELECT FIELD NAME');
for (let choice of multipleSelectField.options.choices){
output.markdown(`${choice.name},`)
}

Example Base, which may or may not have been deleted:

See Solution in Thread

3 Replies 3

Is this not possible?

You should be able to do this with scripting. The scripting app is free to all users.

You can also quickly create dropdown lists, by pasting all options as rows in a short text field, and then converting that field to a select type.

I managed to achieve this on my own, but in a very round-about way. For all of those reading in the future, this is what I did. Please note, that I am a newbie and know nothing about scripting or formulas. I am also on a free plan.

  1. I had the multiple select fields in my table (Main Table), filled with my selection (there are about 100 to choose from).
    image
    The above image is only showing the options that have been selected.

  2. I created a separate table (Options Table) with each choice option as the “Name”. I made sure that each option was spelled exactly the same, including punctuation and spacing. You can extract the choices manually with your fingers and eyes, or by using code (see below for code). I then added another column as the “category” as a multiple select field.
    image

  3. I created a linked record field in my Main Table that pointed to my Options Table. Make sure the allow multiple records is ON.

  4. I selected my multiple choice field in my Main Table and pressed ctrl+c on my keyboard to copy that entire column.

  5. I selected my linked field (linked to Options Table) column in my Main Table and pressed ctrl+v on my keyboard to paste the selected options. This will make the matching linked record appear in this column. At this point, everything looks like this.
    image

  6. I then created a rollup field next to the linked field and set it up like this:
    image
    image
    image

  7. Lastly, hit save.

If there were any selections made that fall under the category “width” they will appear in the rollup field.
image


Text Version in case images ever break:

Field on this table that links to the records you want to summarize
(Select the linked field from the drop-down menu. The one that is linked to Options Table.)

Options Table table field that you’d like to roll up
(Select the “Name” option)

TOGGLE ON Only include linked records from the Features table that meet certain conditions

When the filter section pops up, select

Where “Category” is “Width”

Aggregation formula which rolls up the values in each linked record

Type this into the text box:

ARRAYUNIQUE(values)

You will have to repeat steps 6 and onwards if you have more than one category. I ended up with 14 separate rollup fields to separate my 100 multiple selection options. I found it easier to duplicate the rollup field and change the field name and filter instead of creating a brand new field each time.

At this point, you can consider deleting that multiple select field (or maybe copy it somewhere else for safe keeping and THEN delete it).


You can grab the multiple select choices by using scripting. After you run the script, copy the list of choices and paste it into your Options Table. There will be a blank row between each choice, but I don’t know how to fix that. :frowning:

This code is not mine and was provided by someone else.

let table = base.getTable("TABLE NAME")
let multipleSelectField = table.getField('MULTIPLE SELECT FIELD NAME');
for (let choice of multipleSelectField.options.choices){
output.markdown(`${choice.name},`)
}

Example Base, which may or may not have been deleted: