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? – Matiur Rahman Nov 9 '20 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 Wade Nov 9 '20 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 Rahman Nov 9 '20 at 22:16
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- – Marnee Wade Nov 12 '20 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))) – Marnee Wade Nov 12 '20 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. – Matiur Rahman Nov 13 '20 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.