I need assistance with a validation formula for a list with 5 content types and 5 different outcome columns and I want to force the user to choose and outcome when the outcome date is chosen and vise versa. the Outcomes are: ETOutcome, STOutcome, TTOutcome, MTOutcome, TCOutcome. I also want to not allow an outcome to be chosen when the status of the item is pending. Any help would be appreciated.
-
So, you have three columns participating in the validation: [OutcomeDate], [OutComes], and [Status], right? Is [OutComes] a Choice column? Can you clarify your logic in a simple pseudo code?– Matiur RahmanCommented Nov 9, 2020 at 20:54
-
No I have 5 Outcome columns as I have different outcomes for each content type and a Status column.=IF(AND(NOT(ISBLANK(ETOutcome)(STOutcome)(TTOutcome)(TCOutcome)(MTOutcome)),ISBLANK([Outcome Date]),Outcome<>",FALSE,IF(AND(NOT(ISBLANK([Outcome Date])),Outcome="<Select>"),FALSE,TRUE))– Marnee WadeCommented Nov 9, 2020 at 21:30
-
If I revise your logic: values for [Outcome Date] and [Outcome] ( a value other than "<Select>"), are required when all of these outcome columns [ETOutcome].. [STOutcome].. [MTOutcome] are not BLANK, right?– Matiur RahmanCommented Nov 9, 2020 at 22:16
Add a comment
|
2 Answers
Based on my understanding of the requirements, I'm posting the below. Here is what it validates:
- if [Status]=Pending, will not allow to select [OutCome] or enter [OutComeDate]
- if all 5 content type outcome columns are entered (not blank), force to select [OutComes] and [OutComeDate]
=IF(AND(Status="Pending",OR(OutComes<>"<Select>"),NOT(ISBLANK(OutComeDate))),FALSE,IF(AND(AND(AND(AND(ETOutcome<>"",MTOutcome<>""),STOutcome<>""),TTOutcome<>""),TCOutcome<>""),IF(AND(NOT(ISBLANK(OutComeDate)),OutComes<>"<Select>"),TRUE,FALSE),IF(OR(NOT(ISBLANK(OutComeDate)),OutComes<>"<Select>"),FALSE,TRUE)))
answered Nov 10, 2020 at 5:04
-
I tried the above formula- my status's are In Progress, Deferred and Completed- If Completed is chosen we would want to force the user to choose an outcome and an outcome date but in turn not be able to choose an outcome and outcome day for "In Progress" or "Deferred", when I tried the above formula SharePoint did not accept it I tried it in the following manner- Commented Nov 12, 2020 at 15:41
-
=IF(AND(Status="In Progress",OR(OutComes<>"<Select>"),NOT(ISBLANK(OutComeDate))),FALSE,IF(AND(AND(AND(AND(ETOutcome<>"",MTOutcome<>""),STOutcome<>""),TTOutcome<>""),TCOutcome<>""),IF(AND(NOT(ISBLANK(OutComeDate)),OutComes<>"<Select>"),TRUE,FALSE),IF(OR(NOT(ISBLANK(OutComeDate)),OutComes<>"<Select>"),FALSE,TRUE))) Commented Nov 12, 2020 at 15:43
-
Not sure why you got syntax error as I tested the formula before posting it. Maybe column names not the same. Anyway, if possible, please post values of all columns for valid and invalid scenarios in an excel format so that the requirement is clear to me. Commented Nov 13, 2020 at 3:36
Assuming the Status column is a choice column with choices “Pending”, ”Approved”, ”(leave empty)”, here is a sample formula for validation:
IF([STATUS]="Pending",AND(ISBLANK(ETOutcome),ISBLANK(STOutcome),ISBLANK(TTOutcome),ISBLANK(TCOutcome),ISBLANK(MTOutcome),ISBLANK([Outcome Date])),
IF(OR([STATUS]="Approved",[STATUS]=""),AND(NOT(ISBLANK(ETOutcome)),NOT(ISBLANK(STOutcome)),NOT(ISBLANK(TTOutcome)),NOT(ISBLANK(TCOutcome)),NOT(ISBLANK(MTOutcome)),NOT(ISBLANK([Outcome Date]))))
)
You can change the second condition based on your actual setting for the Status column.
answered Nov 10, 2020 at 6:40
