Too many entries in multi-select text field. Need solutions, please?

Question

I have an app for project managers. When they create an activity, they can assign that activity to a project team member or other resource. The Assigned To field in the Activities table is a multi-select text field. The Assigned To field is pulling from the Resource Name field in the Resources table. As project managers identify new resources, they can add those resources to the Resources table. The problem now is the Resources table has 118 resources. The Assigned To field, being a multi-select text field, can only handle 100 maximum entries (I didn't know that when I set up the field. Argh!). So, now I can't edit that field, because when I do, I get this error: Choices too long. 118 choices specified. Field may not have more than 100 choices. And over time, we're going to have more than that.

My research shows I should have created a one-to-many relationship between the Activities and Resources tables and use the reference field as the source for the dropdown. So, I did that. The problem is I can only select one resource. We need to be able to select multiple resources that will work on that activity, hence why we originally set it up as a multi-select text field.

Ideas anyone?? Thank you, in advance, for your help and consideration!

------------------------------
Tom
------------------------------

michaeltamoush · Answer

TL/DR: Link to a formula field with less choices, then change the formula field to all the choices.

You can trick the system. In your resources table, make another field 'Resource Name for MultiSelect'. This should be a formula text.

Link your multiselect to this field. AFTER you have it linked and saved, make your formula in the new field you created [Resource Name].

Now you will have all the choices.

The trick is, any time you want to edit and save the original multi select field, it will give you that error. So the process in the future is, comment out the formula field so there are 0 choices (or you can make the formula whatever you want, so long as its under 100 choices), edit and save your multi select field, un-comment your formula field.

------------------------------
Michael Tamoush
------------------------------

markshnier__you · Answer

In the long run, a better approach is to have a many to many relationship. One Resource has many Resource Activity Assignments. And one Activity has many Resource Activity Assignments. There is a technique that I could help you implement which would allow you to quickly add resources to a particular activity.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

pauleaston · Answer

Sorry for the thread necro, but I'm running into a similar issue. I have a Requests table and the end-users want to be able to associate a request with prior requests. So I created a multi-select text field that looks up from the Request ID# field.

I understand that the kludge recommended here is not considered best practice, but when I'm only dealing with one table, how would I set this up properly? Do I have to create Request Numbers table that uses pipelines to populate all existing Request ID#s? Then another table to allow for many-to-many? It feels more complicated and result in duplicate data that is only created to create a lookup field?

------------------------------
Paul Easton
------------------------------

Forum Discussion

Too many entries in multi-select text field. Need solutions, please?