Automatically populating limited data from a multiple select field

[Y_S]

Hi, I’d like to create a field that automatically fills in only some of the data from a multiple select field.

For example, I have a field that records whether a therapy is long term/short term; and sometimes includes how many weeks long the therapy is.

I’d like another field to automatically pull in the long/short term info but to not also include how many weeks long the therapy is.

Thanks in advance!

[Adam_C]

Hi again Y_S!

Try this out in a formula field:

IF(
  AND(
    FIND("Long", {Length of Therapy}),
    FIND("Short", {Length of Therapy})
  ),
  "Long and Short",
  IF(
    FIND("Long", {Length of Therapy}),
    "Long",
    IF(
      FIND("Short", {Length of Therapy}),
      "Short",
      ""
    )
  )
)

Result:

[Y_S]

Thanks - is there a way to do it so that they are separate snippets of data (ideally as a label similar to the multiple select) rather than as a string?

[Adam_C]

Hmm, like with the colors and everything? Unfortunately, I don’t think so.

We could do an automation that pastes the string into a multi select field where you’ve got the colors set up maybe?

[Y_S]

Hmm

This is what I’m trying so far - how would you suggest I set up this automation?

[Adam_C]

We’d insert the formula field’s value like so:

[Y_S]

Hmm I’m getting an error message when I do this

Any suggestions?

[Adam_C]

Hm, your record ID input’s set up wrong in that picture. Here’s how it should be set up:

And here’s my base setup

You can also view the automation details in this base I’ve set up for you

[David_Solimini]

The ideas from @Adam_C certainly work, but I want to suggest a different approach. It seems that the question of “short” vs “long” – what I’ll call Term Length Category – and the number of weeks/months are different types of information and should therefore be in different fields. One of the tenets of DB design is that every different type of information should be separate.

This is a similar situation to something I have at work – we produce multiple types of written products, each of which has attributes which need to be in the product’s record. Based on that experience, I suggest:

• Create a new table: Therapy Terms.
  • Give it fields:
    • Term Category. Single Select, values: Long or Short.
    • Therapy Length. Single Select, values: 5-6 weeks, 1-2 weeks, 12 weeks, etc. for each of the potential situations people can have
    • Name: Formula that combines the two fields concatenate({Term Category},if({Therapy Length}," - " & ({Therapy Length}))
  • Create an entry in this table for each of the combinations you may have, ie, each of the products you offer. So Short Term-1-3 weeks, Short term-12 weeks, etc.

Link Your Existing table to Therapy Terms and allow multiple selection of Terms.

In your existing table, create two lookup fiends - one for Term Category and One for Term Length.

The result:

• You have a menu of Therapy Terms to choose from for each patient, and the record will show a Term Category of “Short” or “Long” or “Short, Long” and in a separate field you have the Therapy Length of “1-4 weeks” or whatever.
• It’s easy to add new options to the menu without messing with the main table field settings.
• You get the fields you want in the main record.
• It reduces the chance of data input error by enforcing a set of options. It’s less possible to input impossible/incomplete combinations in your day to day work – you don’t create “Long Term, 1 week” option or “Short term, 2 years” options.

This also has the additional benefit of giving you the ability to have other ‘attributes’ that a type of therapy can have and show in the main record.