Formula wizards! Can you help me with this one?
I have a column for Primary Team (single select) and a column for Secondary Teams (multi-select). I want to drive a report widget to include rows where a certain team, let's say "Team X", is either primary or secondary. Report parameters won't let me do this (because I also need to filter the report to those with Status "active" and the reportbuilder will let me use OR or AND but not both). Technically I need ([Primary Team] OR [Secondary Team]) AND "Active") in the report for it to work there.
Thus the workaround...I think the best way would be to have a "Team X" column (checkbox) and a formula that looks for the name in the team columns and checks the box if it is found.
If you think there is a better way I'm all ears.
What would the formula be? I get lost with IFs and ORs and combos.
Logic is:
If [Primary Team] is "Team X"
OR
If [Secondary Team] HAS "Team X"
then "1" (for the checkbox)
Thank you!