Usage of complex filters with AND in a Valid_If column constraint?
I am trying to understand how I can use this.
I want a Valid_If column constraint with several filters
1 - Auditor must not audit the same area that he belongs to. This is already working through a select, as there is another fixed table where there is auditorโs names and areas where they work
So if I have 10 auditors, 2 of them are from HR, and I am auditing Human Resources, I can only choose between 8 auditors.
2 - Audit has two Auditors (Main and Secondary). Cannot choose same Auditor for both roles. So in the case above, when selecting second auditor, I would need to list to show only 7 names (exclude 2 from HR area plus the one that was already selected before)
3 - If Auditor was already selected for another Audit in the same period of the same day, he also should be excluded from the list.
Nowโฆ as a column constraint for a Valid_If field, a formula must return a LIST.
My first logic is that I should use an AND formula where I would use all the list retraints. But AND returns only YES or NO, not a list, so itโs not accepted as a Valid If Column Constraint.
So, how to do it?
LinkedIn
Twitter
