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…?
Solved! Go to Solution.
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
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
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
User | Count |
---|---|
43 | |
26 | |
23 | |
14 | |
11 |