Dynamic / Advanced Search Filter

Hi,
I have been trying to build an app for my business. While Appsheet addresses most of the requirements that are expected in todays, I dont seem to be finding a way to add Advanced / Dynamic filter that would filter the current view to show the records based on user inputs and would stay in that state until the user clears the filter so that user could further filter the records from that state on. And this I feel is the most basic feature that any app would need irrespective of the category. I found a few samples that did address my requirements of this dynamic filter but with limitations -

https://www.appsheet.com/samples/This-app-shows-how-to-filter-your-data-using-a-form?appGuidString=c6859960-6635-4cdd-b0ac-92a42fbb3a71

https://www.appsheet.com/Template/AppDef?appName=Untitledspreadsheet-71626-3#Manage.Author

While the apps aforementioned do the filtering, there are a lot of limitations I found

  1. The filter is temporary . I would be expected to do the filter all over again if I want to further filter the records from that state.

  2. The filter form should be dynamic and should be the part of navigation menu like a burger menu besides the search icon and can slide from right or an action sheet and should load the form dynamically based on the current view. For instance I might have 2 views - Customers and Items. When clicked the advanced filter button when in a specific view, say Customer, it should load the form with fields relevant to that particular view, fields relating to customer in this view.

This I feel is not anything advanced but a very basic feature I believe. I could see content related to LinktofilteredView() , security filters but nothing clearly seems to address this requirement.

Am I missing out on something ?

I agree. I’ve used all of my votes. Perhaps I can “unvote” something and come back to this. But, at any rate, I too would like to see a better search function, particularly now that AppSheet is part of Google. (It’s ironic that search is a weakness of a platform that is owned by the world’s most famous search engine.) :wink:

P.S. Even if we have used all of our votes, it would be nice if we could at least “like” other feature requests. Maybe I should make that a feature request – but most people would probably not want to use a valuable vote on it. :wink:

3 Likes

This is entirely possible.

A curious opinion, since the vast, vast majority of apps don’t need sophisticated search.

Nope. What you’e describing is very sophisticated.

2 Likes

True, but Google has the world’s best search engine :wink:

4 Likes

Howz it possible, Steve. Can you help me with a brief guide ? And no mate I don’t think it’s all that sophisticated. I cant imagine anyone scrolling thousands of records like I have to search for something or just using a generic search when a lot of your field values could be similar… I am not an analytics person but I still can’t really think of anything less than 50 percent of apps in my mobile atleast with no search functionality… Be it travel, investment, food and entertainment or a business management and more… However I’d appreciate if you can guide me in brief as to how do I go about adding this to my app ?

Yet you’re asking for help to implement it.

There are a lot other things that you’d call unsophisticated or basic that I am seeking the help for to implement, the very reason am trying to build a no-code app. If I unable to implement some feature doesn’t make it sophisticated.

The question however still remains if YOU can help or if anyone else can ?

I can, but will not. There are examples for implementing search functions–including at least one by me–in this forum. I encourage you to look. Perhaps by using the forum’s search feature, even.

2 Likes

I share your wish, @Manish_Jain1 , for better filtering capability that would be easier for app builders to implement and I suspect many people who have not commented on or voted for this thread do too. On the other hand, I don’t understand the technical reasons why if might be difficult or impractical for AppSheet to make such capability standard. @Steve, on the other hand, does understand the inner workings of the AppSheet platform. He has also be extremely generous in helping me with many, many things over the past few years. So, if @Steve says it’s difficult for AppSheet to make this kind of functionality standard, then I’m inclined to think that must be the case, even if I don’t understand why. I’ll just hope that some sort of enhancement of this sort in the AppSheet platform comes eventually – even if we have to wait a few years for it.

2 Likes

Appreciate your views @Kirk_Masden . I do not myself understand why App Sheet has not kept this feature which is a must for people like me which I feel would be in large numbers even if its not the majority of Appsheet subscribers. I was excited when I learnt about Appsheet and wanted to jump on to subscribe for my small team as we have been using google sheet to manage our day to day operations for years now. But I felt I should first shortlist the features before I begin, the most basic ones (atleast for me) to be able to use the app , the minimum that I would need to use the app. And I was actually surprised when I found theres nothing enough that can help me implement this feature or atleast a considerably easier way as what would be expected from a No Code App. I cant expect myself to scroll through thousands of records to reach to what I want or use a generic search which would give me results for say " John" from all my fields customer, vendor, teammate when I want only the customers with the name John. This is just an example. @Steve I dint mean to challenge your views , I was just keeping my views. Looks like you took it wrong. But thanks anyway.

4 Likes

Native features for advanced search and filterings is one of the most wanted feature for long time and also one of the top items on my wish list.
I understand the Appsheet dev /UI team should be working on the restructuring the UI of appsheet globallly and widely, I jus hope this is under their scope for new development as well. Just wait and see.

For the advanced searching, it is quite common that we need this on the table view so that we gavec the user experience like Excel / spreadsheet filtering feature alike.
For the mement, it is not possbile with Appsheet.
To get the advanced filtering functionality, I always place action on table view . First action to bring the user to new form to set up filtering criteria. Once this form is saved, the filteredview actions is fired. The user will be going back to table view wher they are from, but the rows are filtered as they configulated the form.

@Kirk_Masden

4 Likes

An addition of easy filters was mentioned a livestream around the end of last year I believe when they had the ‘open mic’.

However, if you make your filter right, the filtered view is not temporary. For an easy start, I suggest creating a few filter fields on a table with one row only edited by one user so the filter only affects the user making the changes, like a Users table if you have one. Do this by creating a form or detail view (quickedits) of a slice that only returns one record by comparing USEREMAIL() to an appropriate value in its Filter Row Condition. Create a second slice for the table you want to filter and set its Filter Row Condition to an expression that compares the appropriate fields from table to be filtered and the table holding the filter values.

For example, filtering table according to ONE acceptable value for jobs:

[Job]=any(Me[filter_job])

Filtering table according to MULTIPLE acceptable values for jobs:

in([Job],Me[filter_job]))

Combine multiple column filters with AND function for even more advanced filtering. Takes a little work but its all the filtering I can ask for.

4 Likes

Thanks @tsuji_koichi . This method could work but it does not end there , a user might further want refine the search to even more fields from there. In that case he has to go to the filter form again, fill all the fields once again from the beginning and then get to the result. Imagine a user , who is expected to do this tens or hundreds of times each day. The whole purpose of using the app to improve in such a scenario wouldnt make sense. A Plain Google Sheet or any other spreadsheet in that case would do a better job.

No it is posisble. We just simply save the filter form with useremail() value to make it trable. Once the user wishes to refine the filter condition, then prompt them to the save row, meaning open the exisitng form then let them refine. Using thsoe filter condition value for slice then make a table or any other list type view you want

It is workng well for me.

2 Likes

@tsuji_koichi - This sounds interesting… Let me try that. Can we also clear the search filters and return to the original results.

Thats s possile

@tsuji_koichi

So here we go step by step . Please correct me if I am wrong

  1. Create a view - the Main view of a particular table

  2. Create a Form - Advanced Filter Form and give all the fields as required for search.

  3. Give a Button in the Main View and assign an Action to go to the Advanced Filter Form. ( I dont want this in menu. Because there would be different advanced filter forms for different data views - say customer, order etc. So I would want this Filter Form button only on the specific view.)

  4. On Save , the button will take us back to the Main View. - But how does it take along the filter values selected in the form. How are those values passed to the main view so that it renders the view with filter applied. ?

  5. Now that I want to refine the results even more , I would click the advanced filter button again and it will take me back to that form , so that I can select more values . But will that form already have the values that were selected in the first search ?

  6. Assuming that I have refined the filter and am done with the work and now I want to clear all the filters, So I shall give the Clear Fliters Button and it will clear all the values that were selected and give me the view with complete results. How do I implement this ?

I have tried to keep it simple but apologise if you find it confusing. Please ask for clarifications if any ?

  1. Create a view - the Main view of a particular table

    okey

  2. Create a Form - Advanced Filter Form and give all the fields as required for search.

Make sure to set the fields type Enumlist or Enum and dropdown option value should come from main view table. I would also suggest that ID for this table is set to initial value of useremail() Each app user can dominate single row for this table and we rely on that row.

  1. Give a Button in the Main View and assign an Action to go to the Advanced Filter Form. ( I dont want this in menu. Because there would be different advanced filter forms for different data views - say customer, order etc. So I would want this Filter Form button only on the specific view.)

I recommend to place action to jumpt to new form for filter via deeplink action set to display overlay.

  1. On Save , the button will take us back to the Main View. - But how does it take along the filter values selected in the form. How are those values passed to the main view so that it renders the view with filter applied. ?

Create the slice fist. The slice conditions is coming from each users filter table value.

For instance if you have [Col1] for filter table as enumlist then slice condition should be something like

IN([Col1] , ANY(select(Filter[Col1],[ID]=Useremail()))

you have multipe finter condition then you wrap those by AND() and set to slice conditions

Then you create view (table). Once the form for filter is saved, set the action to bring the user back to this filtere table made out of slice

  1. Now that I want to refine the results even more , I would click the advanced filter button again and it will take me back to that form , so that I can select more values . But will that form already have the values that were selected in the first search ?

Implement logical check first. If the row which belows to loing user does not exist, show and display the action for deeplink to guide them to the new form. IF not, then hide the action. Then prepare another action to guide them to open the detal view for the existing row. This detail view is set to quickeditable for all the coluns. Then for this fltter table, place action to change the value for all the coumn to initolal value, which could be null. THis action is prepared for “reset filter”

  1. Assuming that I have refined the filter and am done with the work and now I want to clear all the filters, So I shall give the Clear Fliters Button and it will clear all the values that were selected and give me the view with complete results. How do I implement this ?
4 Likes

Thanks @tsuji_koichi . This seems to help me in a big way but I would need to implement and see . Feel a little tough for now but I think I need to spend some time on it to get that easy. Thanks a lot mate.