Allow expressions for the "Filter out all existing rows?" Table property

[WillowMobileSys]

The Table property "Filter out all existing rows?" is meant for "Form only access"  apps where the user simply needs to submit data and never has access to view the entries.  In a large corporate system where we have data entry divided among user roles, there are times when we need the "Form only access" feature in apps that have full functionality. 

For example,  I might have a back office function to enter missing data on a users behalf and, it being a complex function, want to leverage the feature built in another app.  I can currently do this but the problem is that it requires a transition to the other app that understandably fires a Sync subject to Security Filters.

However, in this use case many of the main app tables may not need to be loaded at all.  But I need to support Security Filters for when main app users access the app.  So the best I can do to filter out not-needed tables, is to force the Security Filter to return FALSE for the back office users.  This is not efficient! 

To provide faster Sync times in this special but important use case,  it would be much better if we could conditionally apply the "Filter out all existing rows?" property.  If conditions are met, table is not loaded....done!   Otherwise the table goes through normal load process including Security Filter.

(EDITED:  The assumption I'm making above that the Security Filter expression is still checked on each row.  However, in the comments below it is being suggested that an expression that returns FALSE without referencing row columns is treated the same as the "Filter out all existing rows?" setting set to TRUE - yet to be confirmed.   If true, this means that the Security Filter is pre-processing the expression before attempting to apply the filter expression to rows.  Not great implementation and certainly would not be clear to an inexperienced App Creator.  Basically, the "Filter out all existing rows?" setting is a Table Filter,  The Security filter is a row filter for the table.  Keeping these two separate and allowing expressions in both makes much more sense and is more readily understood by new users - regardless of if performance is impacted or not....BUT especially if it is impacted!!!)

[Fabian_Weller]

Hi @WillowMobileSys why is a security filter like this not efficient?
IF(USERROLE()=User,FALSE,TRUE)

[WillowMobileSys]

@Fabian_Weller 

---

@Fabian_Weller wrote:

IF(USERROLE()=User,FALSE,TRUE)

---

To clarify, I am saying it is not as efficient as using the "Filter out all existing rows?" setting.  

Currently, when using ANY expression to filter a table, the Security Filter process MUST check each and every row to perform the comparison and determine if the row is included or not.  If the table has 10,000 rows then 10,000 rows will be inspected.

Using "Filter out all existing rows?", allows the Security Filter process to skip the table entirely - i.e. ZERO rows need to be inspected!!   

We can think of the "Filter out all existing rows?" as the Table filter - whether the table itself should be loaded or not. 

Security Filters are Row filters - whether a row in that table should be included or not.

[Fabian_Weller]

@WillowMobileSys I understand what you mean. But I'm pretty sure that when you use an expression like IF(USERROLE()=User,FALSE,TRUE) in the security filter, then no row will be inspected. 
If you use an expression like [Column_In_This_Table]=TRUE then every row needs to be checked by the security filter.

[WillowMobileSys]

Hmmm, it would be a matter of implementation on AppSheet side.  The Security Filter process would have to recognize, up front, that the given expression doesn't reference any current table row columns for the process to determine it can skip the table entirely or load the entire table.

My assumption is that the Security Filter doesn't do this kind of pre-processing.

Do you happen to definitively know if the Security filter does treat your example expression as if the "Filter out all existing rows?" is TRUE?

Regardless, even if the Security Filter pre-processed the expression to determine course of action, it is counter-intuitive and potentially has gaps with more complex expressions.  It would be cleaner and more easily understood if we could just conditionally set the "Filter out all existing rows?" setting.  When TRUE, skip table.  When FALSE, then apply Security Filter expression.

[SkrOYC]

---

@WillowMobileSys wrote:

So the best I can do to filter out not-needed tables, is to force the Security Filter to return FALSE for the back office users

---

Afaik, @Steve mentioned that the "Filter out all existing rows" is basically a quick way to use "false" in the security filter of a table.
Steve, can you clarify if you mean it to say it was practically the same or if it's the same in all fronts?

[dbaum]

---

@WillowMobileSys wrote:

the Security Filter process MUST check each and every row to perform the comparison and determine if the row is included or not

---

FWIW I don't assume this is the case (although I definitely don't know anything with certainty). My inference from previously diving into various community and help article discussions about security filters is that if the result of a security filter expression like IF(USERROLE()=User,FALSE,TRUE) is false then the constant false is delegated (perhaps depending on the capabilities of the data source) to the data source's internal query, which then efficiently returns no rows.

[SkrOYC]

---

@dbaum wrote:

is delegated (perhaps depending on the capabilities of the data source) to the data source's internal query, which then efficiently returns no rows

---

I also think so, which makes sense also in the case of Security filters translated into queries when using a SQL Datasource. It has to be some pre-procesing to optimize the query to the datasource. In the same line, I'd consider any AppSheet expression just the "user friendly" way of a more optimized-but-coded way that's used in the backend

[Suvrutt_Gurjar]

---

@SkrOYC wrote:

Afaik, @Steve mentioned that the "Filter out all existing rows" is basically a quick way to use "false" in the security filter of a table.

---

Yes, this seems to be a correct guideline. I ran the following tests to see the sync time of a 37.8 K rows table with two options

1) no security filter which means all the rows were read and

2) with security filter as FALSE and saw the performance profile.

With no security filter, the sync time for the app was 4.05 seconds and the large 37.8 K rows table (SWC Players) took 3.23 seconds to read. Images below

With security filter set to FALSE the app sync time was reduced from  4.05 seconds to 1.12 seconds and the table with 37.8 K rows with security filter as FALSE simply did not appear in performance log details.

[Fabian_Weller]

That's also my experience @Suvrutt_Gurjar 
I use FALSE in security filter to not load a 2 GB Google Drive folder (Folder as a table).
https://www.googlecloudcommunity.com/gc/Tips-Tricks/Delete-app-images-or-files-in-an-app-record-from...

Even though I remember that Praveen told us: A security filter is working perfect in SQL, because it will work on the database itself. For Google Sheet and Excel it is not working effective, because AppSheet 1. loads the whole file 2. imports it and 3. executes the security filter.
But that does not seem the case when we use FALSE in the security filter. Then it seems to skip the whole table.

[Suvrutt_Gurjar]

Thank you @Fabian_Weller for sharing your observation and good to know our observations align in light of the guidance by @Steve as mentioned by @SkrOYC 

---

@Fabian_Weller wrote:

A security filter is working perfect in SQL, because it will work on the database itself. For Google Sheet and Excel it is not working effective, because AppSheet 1. loads the whole file 2. imports it and 3. executes the security filter.

---

Yes, you are correct. The security filters on spreadsheets can be less efficient than those on DBs. This guideline has been also captured in the following help article.

Scale using security filters - AppSheet Help

[WillowMobileSys]

---

@Suvrutt_Gurjar wrote:

With security filter set to FALSE the app sync time was reduced from  4.05 seconds to 1.12 seconds and the table with 37.8 K rows with security filter as FALSE simply did not appear in performance log details.

---

While I appreciate that you took the time to run these tests, I don't think the test you have described captures the true test case.  Simply setting the Security Filter to FALSE allows AppSheet to easily deduce to treat the filter like the "Filter out all existing rows" setting is TRUE.

But, what if there is a REAL filter expression in the Security Filter?  The only way AppSheet could treat it like "Filter out all existing rows" setting is TRUE is if they pre-processed the expression to determine that ALL rows will return a FALSE.  I simply don't believe AppSheet does that.

Again, the use case here is that some users need to see rows for their activity but OTHER users don't need to see any of the rows.  Currently, combining these two, filter out all rows + legit filter of rows,  can only be handled by submitting them in the Security Filter field.  By nature, I DON'T think AppSheet can treat the later, users who don't need to see any rows, similar to the "Filter out all existing rows".  But this is just based on gut instinct.

@Suvrutt_Gurjar  If you are still setup for tests, I wonder if you could run a test where the expression has part that would always return FALSE, like "USEREMAIL() = ..."  BUT also includes a legit filter expression that filters out rows when the NOT that user email.  This will give us a true gauge of the performance for the user case I am outlining in this Feature Idea.

[Suvrutt_Gurjar]

Thank you @WillowMobileSys  for your revert.

---

@WillowMobileSys wrote:

@Suvrutt_Gurjar  If you are still setup for tests, I wonder if you could run a test where the expression has part that would always return FALSE, like "USEREMAIL() = ..." 

---

I tested with such a filter expression that needs to be evaluated for each row of the table (when each row will return FALSE in the expression "USEREMAIL() = "abcd@gmail.com", it naturally takes longer time. In my test it took  3.2 seconds for the single table ( 37.8 K rows) to load.

I very much appreciate your requirement and proposal of legit filter that scans each row based on filter expression.

I participated in the discussion to test @Steve 's approach of using FALSE and reported the findings.  That approach could be possibly helpful for some people as it practically eliminates all the existing rows and even when one adds a new row to the table, as it treats the newly added row as an existing row after save. Of course it may not be a pure one as proposed by you.

Below is the performance log with a row level security filter that was set up in such a way to return FALSE for each row.