Security Filter Model Failing - Franchise Groups[Row ID] Returns Empty in USERSETTINGS

AppSheet AppSheet Q&A
,
1

Hello AppSheet Community,

I’m working on a security model for a multi-franchise app and have run into a persistent issue that seems like a platform bug, and I’m hoping someone has seen this before.

The Goal: Filter all data (Locations, Associates, etc.) based on the franchise groups a user belongs to. The model needs to support a “Super User” role that can see all data, while regular users see a filtered view. The app is on a Core plan and has been deployed.

The Data Structure:

  • Associates (Key: [Row ID], has [Email], [Franchisor] (Yes/No), and [Super User] (Yes/No) columns)

  • Franchise Groups (Key: [Row ID])

  • Associate Franchise Groups (Join table with Refs to Associates and Franchise Groups)

  • All tables are in the native AppSheet Database.

The Logic: The entire model is based on a single USERSETTINGS formula that calculates a user’s allowed list of franchise groups.

  • User Setting Name: UserFranchiseGroups

  • App Formula:

    IF(
  OR(
    USEREMAIL() = "my_super_user_email@example.com",
    AND(
      LOOKUP(USEREMAIL(), "Associates", "Email", "Franchisor"),
      LOOKUP(USEREMAIL(), "Associates", "Email", "Super User")
    )
  ),
  Franchise Groups[Row ID],
  SELECT(
    Associate Franchise Groups[Franchise Group Id],
    [Associate Id].[Email] = USEREMAIL()
  )
)

The Security Filter on the Franchise Groups table is then a simple check against this setting:

IN([Row ID], USERSETTINGS("UserFranchiseGroups"))

The Problem: When this model is active, all filtered views show no data for all users, including the hardcoded super user. This indicates that the USERSETTINGS("UserFranchiseGroups") formula is returning an empty list.

Troubleshooting Steps Taken: We have verified the following:

  1. All table structures, keys, Ref columns, and column names are correct.

  2. Data exists for the test users and in the Franchise Groups table.

  3. We used a debug view (and a virtual column) to isolate the USERSETTINGS formula’s output.

This is the key finding: When we simplify the USERSETTINGS formula to only Franchise Groups[Row ID], it still returns an empty list. This happens even though the Franchise Groups table has data and [Row ID] is correctly set as its key.

We have tried regenerating the structure for all relevant tables multiple times, which has not solved the issue.

My Question for the Community: Why would a simple expression like Franchise Groups[Row ID] fail to return a list of keys when used inside a USERSETTINGS formula? Has anyone encountered a bug where a USERSETTINGS formula seems unable to read data from a specific AppSheet Database table?

Thanks in advance for any insights!

2

Oh, you’re using AI!

That’s likely to be a problem in the future. I strongly recommend Google Sheets instead.

A more efficient expression would be:

ISNOTBLANK(
  FILTER(
    "Associates",
    AND(
      (USEREMAIL() = [Email]),
      [Franchisor],
      [Super User]
    )
  )
)

Try this instead:

IN([Row ID], SPLIT(("" & USERSETTINGS("UserFranchiseGroups")), " , "))

The App formula values of User Settings columns are only ever updated when the user opens and subsequently saves the User settings form. They do not update during a sync or under any other circumstances.

1 Like
3

Also, using User Settings for security is a very bad idea.

2 Likes
4

Should I put the entire formula in the security filters?

5

You certainly could.

Here is my recommendation:

  1. Create a Current Associate slice on the Associates table with a Row filter expression like:

    (USEREMAIL() = [Email])

    This slice will always only contain the Associates row for the current app user allowing for efficient access.

  2. Create a Current Associate Franchise Groups slice on the Associate Franchise Groups table with a Row filter expression like:

    IN([Associate Id], Current Associate[Row ID])

    This slice will always only contain the Associate Franchise Groups rows for the current app user allowing for efficient access.

  3. Set the security filter for Franchise Groups table to an expression like:

    OR(
  (USEREMAIL() = "my_super_user_email@example.com"),
  AND(
    IN(TRUE, Current Associate[Super User]),
    IN(TRUE, Current Associate[Franchisor])
  ),
  IN([Row ID], Current Associate Franchise Groups[Franchise Group Id])
)
1 Like
6

Elegant solution @steve

1 Like
7

Just out of interest why do you say that as that’s a reccomendation from AppSheet for scaling?

8

User Settings can be manipulated by savvy users even if your app isn’t designed to allow them to.

1 Like
9

From a security perspective I see what you mean, but for just using the User Settings as a means to keep the app performant (i.e. not needing to try and load milliions of rows and using the Security filter as just a filter) it’s not an issue. For actual security I always use my own table, not the User Settings.

3 Likes
10

I apologize for the delay, but thank you!

1 Like