0

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.

1 Answer 1

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)))
)
2
  • Thanks very much, I think I can unpack the structure from that, but is there a reference somewhere that gives the full range of condition functions? I'm used to AND being an operator, not a function, that's why I was confused to see it at the start of so many statements. Who thought that was a good idea ;) ? Also is this a derivative of some language, or did MicroSoft make it up, and throw out every convention they have used up until now?
    – MofEnticer
    Apr 16 at 13:25
  • This might be a good place to start: learn.microsoft.com/en-us/previous-versions/office/developer/… "Microsoft SharePoint Foundation formulas for calculated fields are based on Microsoft Excel functions and syntax. However, Microsoft supports only those functions mentioned on this page for use in SharePoint Foundation calculated fields. For example, the Excel function MID is not supported."
    – user79408
    Apr 17 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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