Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 12, 2022 02:39 PM
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
I am on a free plan and have no access to paid tools.
Example base for explanation purposes:
Solved! Go to Solution.
Jun 13, 2022 12:26 PM
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.
I had the multiple select fields in my table (Main Table), filled with my selection (there are about 100 to choose from).
The above image is only showing the options that have been selected.
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.
I created a linked record field in my Main Table that pointed to my Options Table. Make sure the allow multiple records is ON.
I selected my multiple choice field in my Main Table and pressed ctrl+c on my keyboard to copy that entire column.
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.
I then created a rollup field next to the linked field and set it up like this:
Lastly, hit save.
If there were any selections made that fall under the category “width” they will appear in the rollup field.
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:
Jun 13, 2022 06:43 AM
Is this not possible?
Jun 13, 2022 10:43 AM
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.
Jun 13, 2022 12:26 PM
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.
I had the multiple select fields in my table (Main Table), filled with my selection (there are about 100 to choose from).
The above image is only showing the options that have been selected.
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.
I created a linked record field in my Main Table that pointed to my Options Table. Make sure the allow multiple records is ON.
I selected my multiple choice field in my Main Table and pressed ctrl+c on my keyboard to copy that entire column.
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.
I then created a rollup field next to the linked field and set it up like this:
Lastly, hit save.
If there were any selections made that fall under the category “width” they will appear in the rollup field.
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: