Challenge: Filtering Dropdown Based on Multiple Selected Values in a Text Column

[PapaBear9420]

I'm building an app where I need to filter a dropdown (PropertyID) based on multiple values selected in another field ("Authorized Properties").

Here's the setup:

• Employees Table: Contains employee information, including a Text column named "Authorized Properties" to store a comma-separated list of property IDs they can access.
• Properties Table: Contains a list of properties with PropertyID as the key.
• Work Orders Form: Users create work orders and select a PropertyID from a dropdown. This dropdown should only show properties the user is authorized to access.

The Challenge:

I want to allow users to select multiple properties in the "Authorized Properties" field (ideally using a checklist or multi-select dropdown). However, I'm facing limitations with the standard AppSheet editor:

• No "Checklist" input mode: The "Checklist" input mode for Text columns doesn't seem to be available in my version of AppSheet.
• Filtering with Valid If: While I can filter the PropertyID dropdown using a Valid If expression with LOOKUP(), SPLIT(), and INTERSECT(), this approach works best when "Authorized Properties" is a Text column with comma-separated values. This prevents me from using a true multiple-selection input.

What I've Tried:

• I've tried using virtual columns and REF_ROWS to simulate multiple selections, but I haven't found a reliable solution yet.

My Question:

Is there a way to achieve both multiple selections for "Authorized Properties" and accurate filtering of the PropertyIDdropdown within the limitations of the standard AppSheet editor?

Any suggestions, workarounds, or alternative approaches would be greatly appreciated! I am happy to include any screenshots if that is helpful!

Thanks in advance for your help!

[Steve · Accepted solution]

---

---

No "Checklist" input mode: The "Checklist" input mode for Text columns doesn't seem to be available in my version of AppSheet.

---

This would be a column of type EnumList.

---

Is there a way to achieve both multiple selections for "Authorized Properties" and accurate filtering of the PropertyIDdropdown within the limitations of the standard AppSheet editor?

---

Absolutely!

The security filter expression for the Properties table might be something like this:

IN(
  [Property ID],
  SPLIT(
    CONCATENATE(
      SELECT(
        Employees[Authorized Properties],
        (USEREMAIL() = [Email])
      )
    ),
    " , "
  )
)