Stuck on showing list of approved individuals

[Michael_Venner]

Hi, I have this problem that I have been trying to resolve with different syntax and functions but I am not getting anywhere.

Basically I have this expression within Valid If:

SELECT(
Auditor Technical Codes[Auditor ID],

CONTAINS([Client Technical Codes], [Technical Code Approval]),

)

It half works.  It shows me a list of Approved Auditors (Auditor ID) where they have a technical code approval that matches the client technical code for the record.  However, it is showing me auditors who have at least one of the codes, not all of them.

A bit more context.  I am within a client record, the client record has a number of codes that are applicable to that client; I will use Q01, Q02 as an example.  These are applied to the client record using an Enumlist.

I have a ref column (which is the one I have this valid if statement within), this reference goes to my list of auditors.  The list of auditors is relatively long and it has a column for the technical code they are approved for like below:

Auditor 1-Q01

Auditor 2-Q02

Auditor 3-Q01

Auditor 1-Q02

I want Auditor 1 to be the only auditor shown on the reference as they have both codes (Q01 and Q02), at the moment it will show all three auditors as they all have at least one of those codes.

I hope that is clear,  tried to get it right but I'm always getting errors no matter what I am trying

[Suvrutt_Gurjar]

You may want to mention the type of column [Technical Code Approval]. If an auditor has multiple codes approved, does she/ he appear in multiple records in the column  [Technical Code Approval] the way you have shown? In your shared example, the auditor Q1 appears in two records -1st and 4th.

[Michael_Venner]

Hi, the column Technical Code Approval is in list format

Auditor appearing in record 1 and 4 is because they have been added twice into my list; once for Q01 and another time for Q04

[Vincent_P]

Hello

Just use the third parameter "distinct ?" setting to true  in your select, and maybe use another label for showing the auditor instead of auditor-technical code

[Neerajkiran]

It seems like you need to modify your expression to ensure that only auditors who have both codes (Q01 and Q02) are displayed. You can achieve this by using an AND condition within your CONTAINS function. Here's how you can do it:

```

SELECT(

    Auditor Technical Codes[Auditor ID],

    AND(

        CONTAINS([Client Technical Codes], "Q01"),

        CONTAINS([Client Technical Codes], "Q02")

    )

)

```

This expression checks if the client technical codes contain both "Q01" and "Q02" for each auditor, and only includes those auditors who meet this condition.

[Michael_Venner]

Hi, There are Q codes in total and they vary depending on the client record you are looking at so using AND is not possible as there are too many variables

[Markus_Malessa]

Is there a reason why auditors are listed as a separate line item for each technical code? Like Auditor 1 that is listed once for Q01 and then another time for Q02. I guess what I'm asking is why are the technical codes for each Auditor not an EnumList also?