AirTable Security Filter

AppSheet AppSheet Q&A
1

I am failing to get security filters to work with AirTable (these are filters that have worked with Google Sheets).

I have two examples:

Example 1

Security Filter:
[date] >= Today()-7

The error I get while trying to load the app is:

Error: Data table ‘breaksheet_airtable’ is not accessible due to: Error executing AirTable operation. Error type: INVALID_FILTER_BY_FORMULA. Error message: The formula for filtering records is invalid: Invalid formula. Please check your formula text…

Example 2:

Security Filter:
AND( IN( [store], SELECT(AppUsers[store], [email]=USEREMAIL() )), [date] >= Today()-7 )

The error I get while trying to load the app is:

Error: Data table ‘breaksheet_airtable’ is not accessible due to: Failed to translate AppSheet boolean operator to AirTable boolean operator. Unknown AppSheet operator: InList.

It seems that AppSheet is attempting to translate the AppSheet formula to an AirTable formula so that it can peform the filter on AirTable’s side. I am slightly familiar with the AirTable language, I wonder if there is a way to type the AirTable formula into the AppSheet security filter…?

2

I figured out how to filter by a value being in a list. You need to convert the list to a string and then look for the value in the string, as a substring. For example:

FIND( [value], CONCATENATE(123, 654, 895) ) > 0

I still can’t figure out how to filter on a date. i.e:

[date] > TODAY() - 7

AirTable uses a function called IS_AFTER(date1, date2) to check if date1 > date2

3

I figured out how to filter on date, you can create a second column that is a date epoch column. Then you can filter based on that:

[date_epoch] > TOTALSECONDS((TODAY()-7) - DateTime(“1970-01-01”))

Also (including for the completeness of the solution) I figured out how to filter by a value being in a list. You need to convert the list to a string and then look for the value in the string, as a substring. For example:

FIND( [value], CONCATENATE(123, 654, 895) ) > 0

2 Likes