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?
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:
please someone help
@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?
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.
Set this as the suggested values, or valid_if
IF(
@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?
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
)
)
@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?
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
@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
I’ll connect with you offline!
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |