There are 10 columns, and I want to filter the rows by 3 values - Provisions, District & Tehsil.
What I want to do is let the user enter 3 things - choose a provision you require, and select your district and tehsil. So, now I have 3 values with me, provision, district, & tehsil (all the values I need to filter the database from). Then app proceeds to the next screen, where I show the filtered data.
Here is the filtration structure I need to build :
First check if there are any Donors available who provide the user’s selected provision. If any, proceed to the next step. If none, then display NA.
Then check within the list of donors (who have the selected provision) if there are any of them available in the user’s district. If any, proceed to the next step. If none, then display NA.
Then check within the list of donors (who are available in the user’s district) if there are any available in the user’s tehsil. If there aren’t any, then display the list of all the Donors available in the district.
So, is it possible to filter a google sheets data source? I thought about one method, which is to load the database and iterate through each row of it, and then if the row satisfies the filters, then add the row to a local/temporary data source - and then display that local data source. Is this method useful? I haven’t tried it yet
If you have any ideas/methods on how should I filter, it would be a great help to me
Thanks a lot for reading!
this is the best kept secret of google sheets! (after microsoft provided the new powerful dynamic array functions in excel 365 but by subscription only, google is providing it to us, free!)
assuming your main data resides in the sheet called donors.
put your search arguments in the sheet named sel.
for testing just populate it by hand, but in thunkable, simply update this one row with data from an input form.
here comes the best part:
create a new sheet called result and in cell a2, type the formula shown below. it means " filter from donors to here and apply the following conditions which are delimited by commas". (note that you don’t have to specify the ending rows!).
Voila!
@manyone’s solution on the Google Sheets end looks awesome, but since you mentioned trying out creating a local view in Thunkable and displaying that in your project, I thought I’d share a project that demonstrates this here.
As you can see, when a genre is selected from the List Viewer, I populate a local Data Source with items from my main Data Source that match this genre. Then I display the matching data in a Data Viewer.
I filter by one criterion, but you can easily modify this to filter by 2 or more criteria! Just add extra filter list and filter criteria fields as inputs to the show filtered data function, and link a few conditions together using and blocks in the if statement inside the function.
I’ll write a more detailed tutorial about this soon, since multiple Thunkers have asked about it, but I hope this demo project is helpful to you (or to someone reading)!
I hope you are doing well!
