Dynamic / Advanced Search Filter

[Manish_Jain11]

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=c...

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 ?

[Koichi_Tsuji]

Thats s possile

[Manish_Jain11]

@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 ?

[Koichi_Tsuji]

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.

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.)

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

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. ?

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

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 ?

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”

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 ?

[Manish_Jain11]

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.

[Koichi_Tsuji]

This requires deep knowledge over the several funcitons and features of Appsheet.
Yes, not easy, but tough.

But to see is to believe.

This is sample app where I demonstrate the custom feature for searching and filtering table view. The filter conditions are retained per app users.
As you asked, i placed the reset function as well.

It should take time to understand how it structured and works , but clues are inside here.

Good luck.

https://www.appsheet.com/samples/Autocreated-from-Google-Sheets?appGuidString=2143467e-3888-488f-a65...

[Manish_Jain11]

Thanks a lot @tsuji_koichi . Will review and revert.

[Bob_Haizmann]

I voted for this also. I implemented a search filter within the dashboard as explained in the link. But there are issues, explained on the bottom of that thread, that need to be solved, like syncs building even though they aren’t needed and conflicts if 2 people are using it.

Dynamic search is essential for the dashboard.

Bob

Change row filter condition for a slice based on user input Questions

I’m filtering a slice based on user input within a dashboard as follows: AND(ANY(Filter[Observation Type]) = [Observation Type], ANY(Filter[Description]) = [Description], ANY(Filter[Work Status]) = [Work Status], ANY(Filter[Start Date]) <= [Date and time], ANY(Filter[End Date]) >= [Date]) This expression works fine. But I want the user to have a choice whether or not to filter on “Description”. He would choose Yes or No to “Do you want to filter on Description?”. If he chooses NO the expre…

[Manish_Jain11]

Hi Tsuji,

I tried this solution . It seems working but with some problems.
I have a City Column in my customer_filter ( the table which has my filter values selected using the filter form) . The City column is an enum list and contains multiple values comma separated like " Chennai, Mumbai" . This I am using to filter my customers view using the following expression.

if(
(ANY(select(Customer_Filter[City],[ID]=Useremail()))=[City]),true,false
)

It filters fine but only one city (one value) and filters only rows with that one city. Where do you think I have gone wrong.

[Steve]

Try this instead of your current expression:

ISNOTBLANK(
  SELECT(
    Customer_Filter[City],
    AND(
      ([ID] = USEREMAIL()),
      IN([_THISROW].[City], [City])
    )
  )
)

[Manish_Jain11]

Thanks Steve. It works. Appreciate the help.

[wael]

I want to search in a data table by typing the search in a text box in a form

and showing the data in a table and searching anything with a part of the word Advanced Search