Filters in AppSheet applications - in Preview Program

[Arthur_Rallu]

The following feature has been added to the Preview Program. Feel free to test the feature and report issues or possible improvements in this thread.

App users can now filter items from a collection of records based on column values.
The feature is available both on mobile devices and desktop computers.

Here is a very simple example. Your application has pastries that have a ‘price’ and a ‘type’ (Sweet vs Savory). In a view that shows all pastries, app users can now filter to see only the ones that are Savory and that cost less than $3.99.

How do app creators enable this?

• Go to your Data Schema and make sure the ‘Search’ toggle is turned on for each column that app users should be able to consider
• Save your application

App users will see the feature after their next sync.

How do app users work with it?

• After it’s been enabled by the app creator and after app users have synced their app, app users can click on the Search icon
• In the search bar, app users click on the filter icon (icon screenshot here)
• This opens the filtering widget
• App users can then specify filtering values for each column
• App users click ‘Done’ to get back to the view with the results

See screenshots below for visual details.

What are the supported views?
Only collection views are supported, namely:

• Card, Deck, Gallery, Table, Map*, Calendar, Chart

The filtering mechanism currently does not support multiple tables at the same time. As a result, filtering on the Dashboard View is currently not available. Similarly, in the Map View it is only possible to filter records of the primary table, but not of the secondary table.

What are the supported data types?

• Text
• Enum
• REF
• LongText
• Name
• ScanText
• ChangeCounter
• Number
• PostalCode
• Address
• LatLong
• ChangeLocation
• DateTime
• ChangeTimestamp
• Percent
• Decimal
• Price
• Url
• File
• Image
• Drawing
• Signature
• Thumbnail
• Video
• Date
• Time
• Email
• Phone
• Color
• Progress
• Yes/No

Is the filter persistent?
If an app user sets up a filter on a view,

• After looking at individual records of that filtered list, going back shows the filtered list
• When the app user goes to another view of the same data set, the filter persists

Does it work offline?
Filtering works offline as it applies to the data that was downloaded to the device.

How does that relate to Security filters?
This is independent of security filters. Security filters allow only certain data to be downloaded to the device. This new feature filters data that’s already been downloaded to the device.

[Kirk_Masden]

This is really great. I can confirm that it works well with Japanese. Of course, I’m happy with this as it is but if, at some point in the future, it became possible to display a hit count, that would add to the value of the filter function. With larger sets of data, a display of the hit count would help users see what effect the addition of the filtering terms had. Thanks!

[Kirk_Masden]

I have one more thought about how to make this great enhancement of the AppSheet platform even greater. Let me use an image from my own app as an example:

The list of spots into which one can type a filtering criterion is determined by what we have set to be searchable and the user is presented with a list of those fields. What if we were allowed to select the field into which a keyword would be typed via a dropdown menu and more fields could be added as needed with a plus button? That way, it might become possible, for example, to type in a word that I want to look for in my “Definition and example” field, then hit +, choose “Definition and example” again and type in another word. This would function as an “AND” boolean search. Currently, the logic of the filter function is already “AND” but only between fields, not within them. This would allow the implementation of an “AND” search within a field.

I’m not sure if what I’m proposing would merely involve an adjustment in the interface or if it would require a fundamental change in the guts of the searching and filtering program. If it were only a matter of adjusting the interface and could otherwise be implemented easily, it might be worth it.

[Marc_Dillon]

@Arthur_Rallu
I was just wondering if there was any ETA for a full release of this feature?

[Arthur_Rallu]

Hoping to roll what you can see today out to everyone very soon.
We still have more changes in mind, but we’ll tackle that later.

[Marc_Dillon]

Good to hear!

[MultiTech]

@Arthur_Rallu

Any plans on this feature working with dashboards?

• I have very few views that are stand-alone table/deck/card - most are interactive/enhanced dashboards.

Unfortunately, when I just went to try this filter feature I found the filter button not appearing for dashboard views.

---

Interactive dashboards share a common table; perhaps if you have a dashboard, and you mark it as interactive, the search filter works? (Taking it’s filtering criteria from the base table?)

Side Note: I’ve always wondered if it wouldn’t be helpful to be able to pick the table an interactive dashboard is for; this way the view has the table info encoded inside it. For a normal dashboard it makes sense that you wouldn’t pick what table it’s for, but an interactive is based on the idea that they all share a common root-table; why not let us pick that root?

[Steve]

MultiTech_Visions:

an interactive is based on the idea that they all share a common root-table

Is that true?

[MultiTech]

Basically yeah, if you want the interactive part to work.

I’ve got a list of records, maybe lists of child-records (that share a ref connection to the first list of records), and a detail view or two to see the details.

• All these views share a root table, or a ref-table… I guess that needs to be included in the thought.

[Amit3]

How can you disable this feature? What if I don’t want the filter option AT ALL?

Many thanks

[yastaaa]

I would like to state the difference between filtering and navigation (with Filters). The filter is a great improvement. But most of times you design the apps so the users can navigate quickly from one general data view to specific data views that are important for them, or important for the app owner to show. For that, filters are needed, but inside the views.

As and example idea. Consider a Dashboard View with two sections. One top section with a form that has the filters for the columns added. So when you choose from (dropdowns, checkboxes, buttons, etc) the data view in the other split shows and update filtered view of the data of the table.

[Steve]

yastaaa:

As and example idea. Consider a Dashboard View with two sections.

Filter Table based on & After form entry values Questions

Since you’re using a dashboard (which is a good idea): Create a table to contain the filter parameters. Each user should get their own row. Create a slice of the table in (1) with a Row filter condition that excludes all but the one row for the current user. Create a view on the slice in (2) of type detail. Add all columns that the user may modify to adjust the filter as Quick edit columns. Create a slice on the table containing the data to be filtered (the table used by the Care Giv…

[yastaaa]

I have tried to add a form to a Dashboard and It is not allowed.

[adufresne]

Agree with this! It would be super helpful to have the filter be its own view that when submitted goes to a filtered view.

[Fof_Webmaster]

I bring in most drop downs into my apps using a valid if statement. Even with Searchable on my drop downs don’t show up as filter choices. Is this that state of affairs with this preview program, or is there a way to make this work? By the way this feature has enormous potential.

[Erik_Ferm]

Simple question: Is there a way to control which columns are shown in the filter? It seems to me that all columns are shown at the moment, even if they are not shown in the view that is being displayed.

[Grant_Stead]

It’s the check block, is searchable, in the column definition…

[Erik_Ferm]

Thanks, that makes 100% sense!

[Marc_Dillon]

I’d like to see the ability to search for records that have no value (are blank) in some column. Currently we can do that for Enum type columns, because we get the automatic “(empty)” selection in the filter.

But there doesn’t seem to be a way to do the same for other column types.

[Ferret]

yastaaa:

But most of times you design the apps so the users can navigate quickly from one general data view to specific data views

Agreed. There are very few times when I DONT create a list with one or more specific ‘pre-filtered’ lists accessible as overlay actions (go to another view, with either a LINKTOFILTEREDVIEW() formula or LINKTOVIEW() to a view based on a slice). I started doing this before user-defined filters were a thing, but even still, providing a one-tap ability to apply a common filter(s) is very helpful if not outright critical.

Now that we have user-defined filters, particularly ones that are displayed to the user in a better fashion than I can acheive by ‘pre-filtering’, I think it would make sense to be able to apply these types of filters within the actions I create that go to list views.

This could be the “Saved FIlters” mentioned by @Arthur_Rallu, or “Initial Value” mentioned by @tsuji_koichi

In the past without this feature, the only way to give a user a filtered list from the context of an ‘all-list’ was by hard-coded actions (using slices or not). But now that the users are understanding the new filter area as the place to filter for further specifics, it makes less sense to provide hard-coded filters that they cannot similarly see the indication of or edit once applied (but I still do it for now because quick sets of filters that I suggest to them is still vital). When needed, we could still use slices or LINKTOFILTEREDVIEW() to hard code a filter we dont want the user to remove.

[Darmund]

I have also heard that users like the bubble icon that appears when looking at a view that has a in-app filter applied. I hope AppSheet creates a deep link function that allows us to send users to a view with a pre-filled filter condition. Until then, I’m thinking about creating an action to send browser users to the URL with the in-app filter condition.

[Pointer]

When I filter with an enum column, the filtered column is not displayed, there is no problem with filtering on text type columns. I hope this gets fixed.

[Arthur_Rallu]

Yes, we are working on fixing this.
It happens when you use only one value to filter that Enum column. If you select two values, the column does not disappear.

[david_nganga]

Is there away one can control what appears under filter option. to get rid of some items from reference table under a different column

[bonameblisto]

Too many posts and documentation articles about that “trouble”

[Wiktor_Jurek]

Is there any opportunity to add a 'default' filter that we could link a user to?

[Darmund]

I think it's pretty limited for now. For browser users, you'll notice that the url contains the information of a user's filter conditions. So you probably could create an action with a deeplink expression that took a user to that specific page. Unfortunately, I don't know how that might affect mobile users. I think there is a feature request for a new deeplink function that can pass filter criteria.

Also, a lot of functionality of having a "default" filter might be covered in creating a secondary slice of the table and sort by or group by conditions in the view.

[Wiktor_Jurek]

Hey Darmund, I've found this to be the case, too. I decoded the URL and there is a filter=[{property:condition}] expression you could use - but as you said - not sure how this would work on mobile. May have to do some testing.

Regarding a secondary slice - this is OK until you want to see *more* data than the predefined slice. I'd like to give the users the option to deviate from the defaults if that makes sense.

[Darmund]

Oh yeah, I totally agree! I feel like the filter feature is a huge game changer and I can't wait for it to become more developed. Having a default filter will also draw users to the filter location. My users currently have a hard time finding the little triangle in the search bar and what it can mean for their filtering needs.

[VGC_Innovation]

Hi, I have been utilizing the filter feature for awhile now and it is a feature I highly appreciate.

But I would like to know if there are future plans to implement the following:

1. Filter functions can be directly edited in the column headers like filters in google spreadsheets/excel. 
2. Filter functions are available in dashboard views with multiple tables

These suggestions would really be a big help to end users who use a lot of data records for data analysis. 

Enlighten me if I missed out some features or other things. Thank you to anyone who will respond!

[NCD]

Feature Request:

Would love to have an option to place the Filters in the header!

Question:

How do I put my filters in Alphabetical order? 

It gives me by the order it is in, in my Spreadsheet.

[Fabian_Weller]

Hi @Arthur_Rallu I can echo the responses here. This is a very cool feature thank you.

I have 3 requests

• Actually the filter order is the same as the column order in the Spreadsheet. It should be or: The column order in the view. Or: the column order of the slice.
• When I have a dependent dropdown (like [Category] + [Items]) the filter should narrow down the values to choose. When I first select the [Category], it should show only the [Items] of this Category.
• Actually the filter is not working on dashboard view. I can understand that when we use different tables. But the filter should work when I have views based on the same table. Like map + table view.

[Fabian_Weller]

I have a question / suggestion about Filter EnumList.
Actually it is an "AND-Filter".
Think about a Table with people. In [Languages] you can select the languages they speak. This is an EnumList with let's say {"English" , "German" , "Italian"}. Let's say we have 3 people in that table. These are the languages they speak:

1. English , German
2. English
3. German

Now when I need to filter people that speak English AND German I can select both languages in the Filter and get the first person.
But when I want to know who speaks English OR German, I first have to select "English" and get 1. and 2. Then I have to select "German" to get 1. and 3.

This is not very intuitive. I think it should be a "OR-Filter".

Adding @Arthur_Rallu 

[Kirk_Masden]

I think I came across a bug in the filter feature today (which, otherwise, I love!).  In my app, I normally use it to search in a table view.  It works fine there. A problem occurs, however, if I try to edit the filter after having clicked on a record.  The problem is that when I'm looking at a record in detail view, the search interface continues to be displayed but it no longer works.  The biggest problem is that the filter categories continue to display but I'm no longer able to type anything in to them.  It feels like my app is broken or something.
This issue occurs when the user makes the mistake of trying to use the filter interface in detail view after beginning a search in table view.  So, if the user uses the app properly, no problem.  Still, I think it would be good to either grey out or remove the search interface in detail view if it cannot be adjusted there. 
Actually, come to think of it, why is the search magnifying glass visible in detail view anyway? It doesn't serve any purpose there, does it?

P.S. I noticed the issue while using my app in a computer browser but confirmed that the same issue occurs when I use my app on my iPhone.

[dbaum]

I've always observed that in certain situations Filter selections seem to be available but are inoperable. I haven't figured out the pattern, but I see now that maybe it's Detail vs. Table views.

This is indeed disconcerting and confusing to users. Ideally, Search and Filter would fully function for Detail views. I do see them as applicable--at least if the slideshow property is enabled (i.e., to search/filter within all the records that can be navigated to via the slideshow feature within the view).

[Kirk_Masden]

Thanks @dbaum !  @Arthur_Rallu , can you confirm that you have read this?  Or, would you like me to submit a report to support?  It's not a huge issue for me personally but I think it would be good for the platform to get it resolved.

[Arthur_Rallu]

Thank you @Kirk_Masden 

Yes, it is on our radar. No need to file a report. 

[Markus_Malessa]

Date filters only appear to allow selection from a calendar type selector and you can only move back/forward at the month level. If I need to select a date range in 2017 I would have to click the back button 60 times for the start date and then again for the end date.

Could this be changed to allow a manual date input or at least be able to select a month and year in the calendar input? Most apps with similar functionality allow for both manual entry and provide a dropdown for month and year in the calendar input.

[dbaum]

Something to try: Check whether selecting the year in the header bar maybe takes you to a screen to select among multiple years. Some date pickers work like that.

[Markus_Malessa]

Thanks for the suggestion, that actually worked. But it was not very apparent.

[Fabian_Weller]

I think this depends on the System (Windows / Android / iOS) and cannot be changed by AppSheet.