Applying Usersettings() in security filter

[RedVox]

I am trying to make security filters like [club] = UserSettings("Club").  I have a table named users that stores email, club and access-level. In the UserSettings table, I have set a field named Club to have an automatic formula of Lookup(UserEmail(), users, email, club).  However, it seems that the lookup formula in my usersettings table does not get executed at start up.  When I replace the Usersettings() function in my security filter with a lookup against the users table, it works.  But this seems less efficient since the same lookup will need to be executed each time the user goes to a new view.  What do I have to do to populate the usersettings field at runtime?  

[dbaum]

---

@RedVox wrote:

I have a table named users that stores email, club and access-level. In the UserSettings table, I have set a field named Club to have an automatic formula of Lookup(UserEmail(), users, email, club).

---

That looks circular, which won't work. The User Settings' columns app formula can't reference the Users table until the Users table is loaded, and it can't load until the security filter's expression is evaluated.

I would think that just the following would suffice for the security filter:

[email] = USEREMAIL()

[RedVox]

I don't think it's circular.  Unless, perhaps, the security filters are applied before the usersettings formulas are processed?? 

Either way,  your suggestion won't help me.  Each user is from a given club and should see all the records from their club, not just ones with their email address.  So, I need a way of providing the security filters with the id of the club associated with the user.  I've set all the filters to use a lookup against my users table.  But that's about 10 identical lookups.  I'd much rather write the value once to usersettings then have it re-used in each security filter.

[dbaum]

---

@RedVox wrote:

the security filters are applied before the usersettings formulas are processed??

---

In your current design, this has to be the case. The issue is that the User Settings expression references the Users table, which can't load until the security filter is processed, which can't occur until the security filter references the User Settings column, whose app formula expression references the Users table, which can't load until...and round, and round, and round.

---

@RedVox wrote:

the same lookup will need to be executed each time the user goes to a new view

---

I don't understand this. ICYMI: Security filter expressions are applied on the server for all tables when a device syncs--not at the time that a user navigates to each app view on their device.

---

@RedVox wrote:

Each user is from a given club and should see all the records from their club, not just ones with their email address.  So, I need a way of providing the security filters with the id of the club associated with the user.

---

I think I understand now. Assuming the Club column in the Users table references a Clubs table and that Clubs table's security filter doesn't reference the Users table, try:

IN(USEREMAIL(), [Club][Related Users])

[RedVox]

Thanks this is very helpful. Understanding that the filter is applied at
sync helps. I still find it inelegant to use a query for each security
filter when it’s the same query for each table, but it’s less of a
performance hit that I was expecting.

I’m my security filters, I’m using a lookup function to retrieve the club
id from the user table. I’m not sure I see the benefit of using an in()
function instead. If you think it’s significantly better to use in(),
please let me know. Thanks!

[dbaum]

On reflection, my suggestion probably wouldn't work. The Club's table's [Related Users] column's REF_ROWS function references the Users table, so it would also be circular to include that column in the Users table's security filter expression.

[Joseph_Seddik]

That's an awkward way of using USERSETTINGS that are intended to store the user's manually-entered preferences, not hold formulas. 

Instead, please use the method described in this tip:

Current User (Slice) | How to conform your app a... - Page 3 - Google Cloud Community

[RedVox]

Thanks.  That's basically the same as what I'm doing now, I think.  I have a user table that holds my user attributes such as access level, etc.  What I was hoping to do was to save a value from that table into a system variable or constant so that instead of querying the user table  for each security filter, there'd be a system value that could be applied.  Looks like one lookup per security table will be as efficient as it gets. 

It seems weird to me that only the user can set usersettings() values - particularly when the  usersettings tables accept formulas for values.  Whatever the usersettings are intended for, it's very different than I would have guessed.  I would have thought they'd be treated more like session settings are in php.  

[Joseph_Seddik]

It is somehow implicitly indicated by or can be inferred from the docs; since while all the app tables are stored in the server side, User Settings is the only table that is stored locally on the users device (per login) probably in a cookie or similar mechanism. Therefore, to hold a formula that accesses other tables, these other tables would have been downloaded first to the device; that's after the security filter is applied.

[RedVox]

Thanks, that's helpful for understanding the sequence of things in the system design.  Some ability to store attributes related to users and have them applied before loading data would be a helpful addition to AppSheet.