Usage of complex filters with AND in a Valid_...

[Rogerio_Penna]

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?

[John_Gardner]

It gets messy, but you can put an AND() inside your SELECT()

formula for your Valid_If. It looks something like:

SELECT(Auditors[Auditor ID], AND( [Auditor ID].[Area] <> [_THISROW].[Area],

[Auditor ID] <> [_THISROW].[Auditor2], [Not sure how your’re tracking dates, but put that formula here]))

You don’t need the carriage returns, but sometimes it’s nice for quickly looking at your conditions.

[Rogerio_Penna]

@John_Gardner Thanks, it worked. Trying to add the date checking now.

There is a column for ParentAudit ID (it’s a Unique ID code, so each registered Audit can have multiple Area Audits registered under it), another column for date and another for morning or afternoon (M or T)

So, you can´t have an auditor doing two audits at the same day, same morning, or same day, same afternoon.