I have an app I am building which is for operational staff only with a Personnel table with columns [Status] (Active/Inactive enum) and [Operational User] (Yes/No), using Google Sheets for data.
I want to apply a security filter with an AND([Status] = “Active”, [Operational User] = “TRUE”). Testing the formula in the formula dialogue returns all the values correctly. However when applying the security filter, it is filtering out all values (or some other reason is causing it to do that).
I’m doing this to avoid having a filter Validif expression on ref columns to filter in the same way, but I don’t know if security filters can actually be used this way?
Make sure you did NOT turn on the “Filter out all rows” setting on the table.
Other than that, there is not anything wrong with the expression. The quotes around TRUE should not be a problem. Double check that the spelling matches between your expression and the column data. Confirm that there are indeed rows that should NOT get filtered out.
Thank you. Yes the setting is not turned on. Putting in each half of the AND statement, it’s the [Operational User]=”TRUE” component that is causing the issue.
I have removed that part just to check and the dropdown is functioning as it should. I’m at a bit of a loss! I just really want to eliminate as many formula from the app as possible to improve performance, and this is one that features a few times in the app.
I’m getting the correct result in the column’s validif with no security filter using FILTER(“PersonnelList1”,AND([Operational User]=“TRUE”,[Status]=“Active”)) which is the exact same function isn’t it?
Awesome thank you so much, and to @Suvrutt_Gurjar, I really appreciate it. I guess AppSheet considers Yes/No values in binary and the quotations are throwing it off.
That’s going to save a whole lot of unnecessary computation!
