Valid If help (Contains formula excluding current rows)

[Mic_L_Angelo]

Hey folks, could use some help here.

Got a table called “Managers”, listing managers in my group.

Manager Table has a “Coverage” drop down column.

The drop down selections are
Places,
People,
Travels,
Travels, People
Places, Travels
Places, People
Places, Travels, People

I need a Valid If formula that states "A manager cannot select an “Coverage” entry if another manager has already selected a “Coverage” entry that contains the first.

In other words, Manager 2 cannot select “Places” if Manager 1 already selected “Places and People”

The best I came up with is below

NOT(IN([Coverage], SELECT(Managers[Coverage],
[MANAGERS KEY ID] <> [_THISROW].[MANAGERS KEY ID])))

This only prevents the exact entry from being re-selected
(Manger 2 can’t select “Places and People” if Manager 1 already selected “Places and People”)

I basically need to add a CONTAINS formula for this to work as I need it to.

Anyone willing to be a jolly old chap and help me out?

[Makcharlie]

am also intrested in this answer i want to exclude a value in a row from visible options for selction i.e
i have a table with options:

1. Shaded
2. No shade
3. Partly shaded
4. Not recorded
   i want the user not be able to choose ‘Not recorded’ option

please someone help

[Steve]

@Makcharlie, why would you want the explicit Not recorded value rather than just leaving the column value blank? How do you envision the Not recorded value being set?

[1minManager]

@Mic_L_Angelo

I think the reason is it sounds like your using a ENUM type column. Where even if you choose “Places, Travels, People”, this isn’t a list. So In() won’t then work. Try changing it to an ENUMLIST.

[1minManager]

@Makcharlie

Set this as the suggested values, or valid_if

IF(

• • some rule - - ,
    LIST(“Shaded”,“No shade”,“Partly shaded”,“Not recorded”),
    LIST(“Shaded”,“No shade”,“Partly shaded”)
    )

[Mic_L_Angelo]

@1minManager iI changed it to EnumList, but the same problem persists. It only limits exact entries, not one that contains the entries.

How should i modify the formula then?

[Steve]

@Mic_L_Angelo:

That your drop-down includes both individual entries and the possible combinations is unusual and somewhat confounds the issue. Better that the column be of type EnumList with only thee possible options: Places, People, and Travels. By merit of being an EnumList, the user will have the option to choose one or multiple options; it’ll also make it easier to validate and query the list. The Valid If for the Coverage column could then be:

(
  LIST("Places", "People", "Travels")
  - SELECT(
    Manager Table[Coverage],
    ([_THISROW].[MANAGERS KEY ID] <> [MANAGERS KEY ID]),
    TRUE
  )
)

[Mic_L_Angelo]

@Steve the combination entries was a short-sighted hack on my end. I was unaware ENUM lists allows multiple entries in the same fields. I removed the combination entries, so I’m not down to 3, People, Places & Travel.

That’s the good news.

The bad news is that the formula still doesn’t work, it is, in fact, acting more strangely than the first formula.

Manager 2 can select “People”, even though Manager 1 already selected it. But navigating to Manager 1 entry shows the “Invalid Error Message” with the “People” option hidden from view. I cant de-select “People” unless i select one of the other choices.

I feel like my original formula functioned a bit closer to what I was expecting. All 3 options are visible, but clicking one of the options someone else selected will provide the error. I just need a formula to include entries located with a combination.

Any other ideas?

[Billy_Sheng]

Hi Mic,

I might have some ideas for you if I can understand what you are trying to do a bit better. Can you book a time with me here: https://app.hubspot.com/meetings/billy42

[Mic_L_Angelo]

@Billy_Sheng set up the meeting.

I would recommend reading my original post in this thread as I think it explains the situation well enough

[Billy_Sheng]

I’ll connect with you offline!