Automatically Check That Enumlists Match

AppSheet AppSheet Q&A
,
1

I have an app where people are applying for certain events and one of the options the event organizer has is to enter required skills and I want the app to compare any applicants’ lists of skills to the required skills and automatically deny the application if they do not match. I made a VC that is “required skill check” that returns a value of “met” or “not met” so that the bot can simply check if it is “met” or “not met” and change the “status” of the application accordingly. However, the expression I’ve written, based on previous posts here, doesn’t work. It is denying all of my applications:

IF(AND([Opportunity].[Restrict Skills]=“Yes”,COUNT([Opportunity].[Required Skills])=COUNT(INTERSECT([Skills],[Opportunity].[Required Skills]))),“Met”,“Not Met”)

Opportunity is the table that holds the details related to opportunities. Restrict skills is the field where the opportunity organizer chooses whether or not they will restrict applicants based on the required skills. Skills are from the application, and required skills are what the organizer listed in opportunity. Both of those fields are enumlists.

What do I need to change to make the expression work?

2

Hello @colodev

The problem with your expression is If the organizer sets [Opportunity].[Restrict Skills] to anything other than "Yes" (e.g., they set it to "No", or leave it blank), the AND condition immediately fails, and the expression returns "Not Met".

This means every application is marked “Not Met” unless the skills restriction is actively turned on and the skills match.

The Corrected Expression:

IF(
// 1. PRIMARY CHECK: Is skill restriction active?
[Opportunity].[Restrict Skills] = “Yes”,

// 2. IF YES: Proceed to skill check
IF(
    // CHECK A (NEW CLARITY): Is the list of required skills empty? If it is blank (count=0), the requirement is automatically met.
    COUNT([Opportunity].[Required Skills]) = 0,
    "Met",

    // CHECK B: If the list is NOT empty, compare applicant's skills against required skills.
    // This checks if the count of required skills matches the count of the intersection, 
    // confirming all required skills are present in the applicant's list.
    IF(
        COUNT([Opportunity].[Required Skills]) = COUNT(INTERSECT([Skills], [Opportunity].[Required Skills])),
        "Met",
        "Not Met"
    )
),

// 3. IF NO (ELSE): The restriction is not active, so the check is automatically passed.
"Met"

)

I hope it helps. Thank you so much!

1 Like
3

As a Yes/No value:

OR(
  NOT([Opportunity].[Restrict Skills]),
  ISBLANK([Opportunity].[Required Skills] - [Skills])
)

As a Text value:

IF(
  OR(
    NOT([Opportunity].[Restrict Skills]),
    ISBLANK([Opportunity].[Required Skills] - [Skills])
  ),
  "Met",
  "Unmet"
)

  1. NOT([Opportunity].[Restrict Skills]) is TRUE if [Opportunity].[Restrict Skills] is FALSE. A candidate meets the skill requirements if there are no skill requirements.

  2. ISBLANK([Opportunity].[Required Skills] - [Skills]) uses list subtraction to see if [Skills] includes all of the skills in [Opportunity].[Required Skills]. If the result is blank, it does.

  3. OR(..., ...) is TRUE if either (1) (no skill restriction) or (2) (has all skills) is TRUE.

If [Opportunity].[Required Skills] will only ever have a value if [Opportunity].[Restrict Skills] is TRUE, you could replace this:

OR(
  NOT([Opportunity].[Restrict Skills]),
  ISBLANK([Opportunity].[Required Skills] - [Skills])
)

with just this:

ISBLANK([Opportunity].[Required Skills] - [Skills])

See also:

2 Likes