SharePoine List Validation Issue

Question

Good afternoon, I have a list where based on what the user selects for a business line they are presented a choice field for activities related to that business line. A flow then populates a generic "Activity" field based on their selection. Because I need the fields to only be visible if the corresponding business line is selected, I can't make them required, but if a user doesn't fill in the activity field for their selected business line, the flow will go into an infinite loop. I know that list validation will help with this so I'm trying to write a formula that will work. I've seen some examples on here, and that's great, but I don't understand the structure, therefore if I have an issue again, I'll be back here asking again...

In pseudo code I want the validation to say: If Business_Line is set to Lands then Activity_Lands cannot be empty(null), or if Business_Line is set to Forestry then Activity_Forestry cannot be empty, or if Business_Line is set to Wildlife then Activity_Wildlife cannot be empty.

Here is an example I'm trying to learn from, as it seems along the lines of what I want: Combine 2 different validation...

What I don't get is the structure of the accepted solution... "=IF(AND([Status]="Disabled",ISBLANK([End Date])),FALSE,IF([End Date]<[Start Date],FALSE,TRUE))"

I am guessing the "AND" operator in front of Status is meaning that both Status=Disabled, and End Date being blank, have to be true for the statement to be evaluated, but then the next thing is "FALSE".... What's false? I thought the next term is the outcome for the argument being evaluated as true, but then it just says false... and then the next term, which I would have thought is the false outcome of the original evaluation is another if statement...

Any help would be really appreciated.

Answer 1

Specify the formula you want to use to validate data when new items are saved to this list. To pass validation, the formula must evaluate to TRUE. For more information, see Formulas in Help. Example: =[Discount]<[Cost] will only pass validation if column Discount is less than column Cost.

So basically, column validation wants a true or false output and it doesn't really care how it gets it. While it can be a bit confusing, if you write validation formula in plain english, it makes it a bit easier to understand:

    =IF(
        AND(
            [Status]="Disabled",
            ISBLANK([End Date])
        ) /* if the status column is set to Disabled AND there is no End Date set */,
        FALSE, /* data is INVALID */ 
        IF( /* If one or both of the original conditions were not true */
            [End Date]<[Start Date] /* check if end date is before the start date */,
            FALSE, /* If the end date is before the start date, the data is INVALID */
            TRUE /* If the end date is NOT before the start date, the data is VALID */
        )
    )

I think the following formula would meet your requirements:

=AND(
IF(AND(Business_Line="Lands",ISBLANK(Activity_Lands)),FALSE,TRUE),
IF(AND(Business_Line="Forestry",ISBLANK(Activity_Forestry)),FALSE,TRUE),
IF(AND(Business_Line="Wildlife",ISBLANK(Activity_Wildlife)),FALSE,TRUE)
)

To me, this is easier to understand how it works, but the next formula is basically an optimised version of the same thing:

=AND(
  NOT(AND(Business_Line="Lands", ISBLANK(Activity_Lands))),
  NOT(AND(Business_Line="Forestry", ISBLANK(Activity_Forestry))),
  NOT(AND(Business_Line="Wildlife", ISBLANK(Activity_Wildlife)))
)