AppSheet: Efficiently Filtering a Table Column Based on a List Selection

AppSheet AppSheet Q&A
1

Hello everyone, I have an urgent situation that I need to solve, and I’d really appreciate it if you could help me.

I created a dashboard with a detail view and a table view. The purpose is to filter the table according to the detail view selection via a slicer, and it works fine. My current slicer rule looks like this:

AND(
IF(
ISNOTBLANK(Current User 2[eCommerce Director]),
IN([Sales Account Manager], Current User 2[eCommerce Director]),
TRUE
),

IF(
ISNOTBLANK(Current User 2[Start Date From]),
DATE([Start Date From]) >= DATE(Current User 2[Start Date From]),
TRUE
),

IF(
ISNOTBLANK(Current User 2[Account Type]),
IN([Account Status], Current User 2[Account Type]),
TRUE
),

IF(
ISNOTBLANK(Current User 2[Subscription Notes]),
IN([Subscription Notes], Current User 2[Subscription Notes]),
TRUE
)
)

Now, I want to add one more condition, but this one is a bit different from the others: it needs to filter based on a list column.

For example, if I select values a-b in the Active Subscription Type column, I need to filter rows in the Subscription Type column where the values are like a-b — b-a — or a-c-b, etc.

IF(
ISNOTBLANK(Current User 2[Active Subscription Type]),
CONTAINS([Subscription Type], Current User 2[Active Subscription Type]),
TRUE
)

But this only filters rows where a-b appear consecutively.

The only condition that worked as I wanted is the following, but it runs very slowly and there are a lot of values for the column:

IF(
ISNOTBLANK(Current User 2[Active Subscription Type]),
NOT(
OR(
AND(
CONTAINS(Current User 2[Active Subscription Type], “3PL”),
NOT(CONTAINS([Subscription Type], “3PL”))
),
AND(
CONTAINS(Current User 2[Active Subscription Type], “Reimbursement”),
NOT(CONTAINS([Subscription Type], “Reimbursement”))
),
AND(
CONTAINS(Current User 2[Active Subscription Type], “Managed Service”),
NOT(CONTAINS([Subscription Type], “Managed Service”))
),
AND(
CONTAINS(Current User 2[Active Subscription Type], “Platform”),
NOT(CONTAINS([Subscription Type], “Platform”))
),
AND(
CONTAINS(Current User 2[Active Subscription Type], “Vendor Recovery”),
NOT(CONTAINS([Subscription Type], “Vendor Recovery”))
)
)
),
TRUE
)

Is there a more efficient way to write this condition so it works as intended but without slowing things down?

2

When you say “a-b” is that like a multiple selection from an EnumList? And what are the values in the records themselves, also multiple selections, or text, or what?

3

it’s enumlist and base type is text

4

INTERSECT( SPLIT(Current User 2[Active Subscription Type], “,”), [Subscription Type]) = Current User 2[Active Subscription Type]

The formula above works, but I have some calculated fields that should count the number of rows based on the current user’s selection. However, the results disappear after the app is synced, It was working properly before I added INTERSECT( SPLIT([_THISROW].[Active Subscription Type], “,”), [Subscription Type]) = [_THISROW].[Active Subscription Type] part

COUNT(
SELECT(Account[Account ID],
AND(
IF(
ISNOTBLANK([_THISROW].[eCommerce Director]),
IN([Sales Account Manager], [_THISROW].[eCommerce Director]),
TRUE
),

  IF(
    ISNOTBLANK([_THISROW].[Start Date From]),
    DATE([Start Date From]) >= DATE([_THISROW].[Start Date From]),
    TRUE
  ),

  IF(
    ISNOTBLANK([_THISROW].[Start Date To]),
    DATE([Start Date To]) <= DATE([_THISROW].[Start Date To]),
    TRUE
  ),

  IF(
    ISNOTBLANK([_THISROW].[End Date From]),
    DATE([End Date From]) >= DATE([_THISROW].[End Date From]),
    TRUE
  ),

  IF(
    ISNOTBLANK([_THISROW].[End Date To]),
    DATE([End Date To]) <= DATE([_THISROW].[End Date To]),
    TRUE
  ),

  IF(
    ISNOTBLANK([_THISROW].[Kickoff Date From]),
    DATE([Kickoff Date From]) >= DATE([_THISROW].[Kickoff Date From]),
    TRUE
  ),

  IF(
    ISNOTBLANK([_THISROW].[Kickoff Date To]),
    DATE([Kickoff Date To]) <= DATE([_THISROW].[Kickoff Date To]),
    TRUE
  ),

  IF(
    ISNOTBLANK([_THISROW].[Account Type]),
    IN([Account Status], [_THISROW].[Account Type]),
    TRUE
  ),

  INTERSECT( SPLIT([_THISROW].[Active Subscription Type], ","), [Subscription Type]) = [_THISROW].[Active Subscription Type])))