0

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.

3
  • 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? 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)) 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? Nov 9, 2020 at 22:16

2 Answers 2

0

Based on my understanding of the requirements, I'm posting the below. Here is what it validates:

  1. if [Status]=Pending, will not allow to select [OutCome] or enter [OutComeDate]
  2. 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)))

3
  • 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- 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))) 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. Nov 13, 2020 at 3:36
0

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.