Match blank value IN list

[dbaum]

In an effort to optimize security filter expressions to be fully delegable to the database source, I'm trying to eliminate an OR function that references a column from the table being fetched since that's noted as not delegable to the database. This portion of the expression is essentially:

OR([Column] = "constant value", ISBLANK([Column]))

I tried transforming to an IN expression including a blank value in the list, but that returns false--AppSheet doesn't match the blank value being checked to the blank value in the list:

IN([Column], LIST("constant value", ""))

Does anyone know of a way to accomplish this--either using the IN technique I attempted or some other way to transform the OR expression into a delegable expression?

[TeeSee1]

One idea  is to create a column with an App Formula [column] & "@zzz" ("@zzz" can be any string) and filter against this column by

IN([new col], LIST("constant value@zzz", "@zzz").

The cost (read as well as create) of additional column with an App Formula may offset (or worsen it) any performance gain of delegation and obviously it needs to be tested.

My two cents..

[Marc_Dillon]

I think you've maybe misread the advice in the article. It doesn't say to never use OR(), it just says to avoid complex OR() expression. Your expression looks very simple to me and I think should be fine. If there is any doubt, you can enable full query logging on your database, and see what the actual SELECT query is that Appsheet is running, in order to determine if it was properly/efficiently translated.

[dbaum]

---

@Marc_Dillon wrote:

I think you've maybe misread the advice in the article.

---

Certainly possible. My interpretation is based on the article section's introductory guidance that "a security filter is efficient is if it reduces to a simple comparison of a column to a value, or if it involves an AND() with two or more such comparisons", which seems to exclude OR, and the following paragraph's explicit "list of simple conditions that AppSheet does recognize and push into the SQL query", which doesn't include OR.

---

@Marc_Dillon wrote:

you can enable full query logging on your database, and see what the actual SELECT query is that Appsheet is running, in order to determine if it was properly/efficiently translated.

---

Thanks a lot for this suggestion!

[Suvrutt_Gurjar]

If i may join the thread. To start with, I agree with @Marc_Dillon ' s suggestion that simple OR() expressions should be okay.

In case of IN() expression, a version of the IN() expression format that you desire could be

IN(TRUE,  LIST([Column] = "constant value", ISBLANK([Column])))

[Marc_Dillon]

---

@Suvrutt_Gurjar wrote:

In case of IN() expression, a version of the IN() expression format that you desire could be

IN(TRUE,  LIST([Column] = "constant value", ISBLANK([Column]))

---

That's a smart & logically equivalent way to write an OR expression, but unfortunately it also is not translatable (by Appsheet) into a SQL query.

[Suvrutt_Gurjar]

---

@Marc_Dillon wrote:

but unfortunately it also is not translatable (by Appsheet) into a SQL query.

---

Oh okay, thank you for all the testing and updates on pushing security filters to DBs. I think if possible you may want to share your findings in Tips and Tricks section till the help article incorporates your findings. 

@lizlynch : You may want to append the article with insights by @Marc_Dillon 

[Marc_Dillon]

I had a reason today to turn the logging on for a MySQL database to examine the queries that Appsheet was sending in. So I decided to test a few other things at the same time, like this question of OR() being converted to an efficient database query.

I'm shocked.

Appsheet does NOT convert an OR() in a Security Filter to an appropriate "WHERE" clause in a database query.

Absolutely not at all.

Even something as simple as OR( [column]="X" , TRUE ). Which according to the linked help document should be sent to the database the same as just [column]="X". It does not.

I'm blown away right now.

I just submitted a support ticket for this.

EDIT: Big whoopsies here on my part. I'm much less shocked now that I've realized I made logical mistake. Of course it wouldn't pass the "X" from OR( "X" , TRUE ) on to the database query, because it wouldn't need to , because the OR statement would always be TRUE. I just tested again with OR( "X" , FALSE) and it does indeed pass the "X" through to the database as described.

But still, it can't handle a simple OR( "X" , "Y" ), which is the more important issue, and is still quite shocking.

[dbaum]

Thanks a lot for testing and reporting. Per your advice, I had gone to do the same and ran into issues I haven't yet resolved with the db provider that prevent enabling full query logging.

I guess at least it's nice to know that the literal details at Efficient security filter definitions for database data seem comprehensive.

[SkrOYC]

---

@pravse wrote:

In #2, If you have security filters, and they are simple (ANDs, equality or IN conditions) and you use a SQL database, then you count the rows that pass the security filters only … otherwise you count all rows

---

---

@pravse wrote:

You can do AND([Col1] = {something that evauates to a constant}, IN([Col2], {something that evaluates to a list}), etc).

Also, a very common pattern is to use a simple filter [Email] = UserEmail on TableA and then to use TableA itself as a filter on TableB like this in the security filter of TableB IN([ARef], TableA[Key])

---

[Suvrutt_Gurjar]

@dbaum ,

Just to explore more possibilities, may we know in your use case, if the [Column] is blank, will it be ever populated and if yes how- meaning by user updating the form or through action or otherwise? 

Also is the table likely to be very big in terms of number of rows or columns?

[dbaum]

Thanks, @Suvrutt_Gurjar, for your interest and offer to help more.

In the particular example I'm referencing here, the column either holds a User ID or is blank. The column is (de)populated either via actions (typically via automation) or via the AppSheet API (always via automation).

The use case is essentially a table whose rows may each be assigned to a single user. When a row is assigned, that user's User ID is populated into the row. If the user processes the row, the User ID is never removed. If the user does not process the row before finishing their shift, their User ID is removed and the row is eligible for reassignment to another user. The scenario where I need rows with a blank User ID to be included along with the user's already assigned rows is when the table is being loaded on the user's behalf via the AppSheet API for assignment of additional rows to the user.

[Suvrutt_Gurjar]

---

---

Thank you very much @dbaum  for more details.

---

@dbaum wrote:

If the user does not process the row before finishing their shift, their User ID is removed and the row is eligible for reassignment to another user.

---

Is this understanding correct that you are referring to the same column when you say "their User ID" is removed and are you making the user ID blank for those rows. Also are these the rows with the blank column value that you would wish to be available through the security filter?

[dbaum]

---

@Suvrutt_Gurjar wrote:

Is this understanding correct that you are referring to the same column when you say "their User ID" is removed and are you making the user ID blank for those rows.

---

Yes.

---

@Suvrutt_Gurjar wrote:

Also are these the rows with the blank column value that you would wish to be available through the security filter?

---

Yes.

[Suvrutt_Gurjar]

Thank you. I am sorry if I am oversimplifying without understanding  the core issue with the following suggestion. 

Is it possible that instead of making the rows blank, you assign some specific value such as "No User ID" or "nouserid@novalue.com" depending on the column type and include that value in IN() expression' s list of the two possible values of the security filter.

[dbaum]

Thanks for your creative thinking. It indeed had occurred to me to maintain a row in the User table with a User ID of something like "unassigned" and use that instead of blank in the table whose security filter I'm trying to optimize and which references the Users table via its User ID column. I haven't taken that step since it creates other complications. Besides having to maintain a dummy row in the Users table, I'd also have to comb through the app and adjust other expressions that evaluate this User ID column using functions like ISBLANK.

[Suvrutt_Gurjar]

Oh okay, got it. Thank you for the update. I thought since a blank option does not seem to be working at least for now with security filters and AppSheet team at times could take substantial time to remedy/ respond to  the issue , a workaround approach could be acceptable. 

[dbaum]

You're right--a workaround is required one way or the other. For now, I'm leaving the existing workaround in place--namely, retaining the OR expression that returns the necessary rows but is suboptimal with regard to database query efficiency:

---

@dbaum wrote:

OR([Column] = "constant value", ISBLANK([Column]))

---

[Suvrutt_Gurjar]

Got it. Thank you very much for the update. I am sure this thread will be useful for anyone working on security filters with DBs.