Is there a better way to filter Slice by user input? Issue with 'Filter' table + Detail view method

[loolookachoo]

Edit: Solved!
- Watch the video from @MultiTech's response to get a video demo of the solution.
- @WillowMobileSys describes the same general concept, but provides a way to have the user be responsible for updating the Filter table, or in the video's case - the User table.
---------------------------------------

Pardon the title. I was really pushing the character limit there 😅. Anyway:

Is there a better way to dynamically filter a slice using user input - a way that's better than the solution found in this Demo?

I have multiple potential users (not a lot - max 5?) and that method doesn't work with multiple users since the filter criteria only pulls from a single row, so any change in the filter criteria will affect the views of ALL users.

There IS the built in filter function, but that's only on desktop, the UI isn't obvious, and is kind of cumbersome to use.

Would using a bot to somehow create and assign a new filter criteria row per USEREMAIL() work? Probably. But is that even a good idea? I honestly don't know. Hoping someone here could give their input. Any help would be appreciated.

Thank you!

[MultiTech · Accepted solution]

The following video shows how to do something similar to what the demo app you were using does, but it's built so it supports many users (how it's supposed to be 😉).

It uses the Current User system to differentiate users, making it easy to have multiple people using the same app - while still allowing you (the app dev) to conform things around the specific user using the app.

Hope it helps!

[WillowMobileSys]

If you are trying to limit the rows of data that a user sees, then you want to use Security Filters.  These are designed to download only user valid data to each users device/browser.  It also helps to keep the app performant for that user on that device - less data downloaded.

If you are trying to achieve something else, please describe what you want to accomplish so we can help you better.

---

@loolookachoo wrote:

that method doesn't work with multiple users since the filter criteria only pulls from a single row, so any change in the filter criteria will affect the views of ALL users.

---

If you were to decide this is the best approach,  you would adjust the "filter criteria" table so entries are PER USER - each user has their own filter criteria row.

I hope this helps!

[loolookachoo]

Thanks for the reply!

Hm. I don't think Security Filters will work since users can (and should) have access to the same set of data and views. It's just that they might be interested in filtering the data in different ways by choosing different criteria.

Issue is, any change from one user will affect the selected filters of other users. This is because the criteria are saved as columns in a single row in a table called "Filter_Transactions".

This, in turn, affects the "Filtered_Transactions" slice because its "Row filter condition" formula reads from that single row in the "Filter_Transactions" table.

AND(
  IF(
    ISBLANK(
      ANY(
        Filter_Transactions[Merchant Id]
      )
    ),
    TRUE,
    [Merchant Id] = ANY(
      Filter_Transactions[Merchant Id]
    )
  ),
  IF(
    ISBLANK(
      ANY(
        Filter_Transactions[Branch Id]
      )
    ),
    TRUE,
    [Branch Id] = ANY(
      Filter_Transactions[Branch Id]
    )
  )
... etc etc repeat for other columns
)

So now we can have a situation where 2 users are using the app at the same time, and if User A changes the selected filters it will affect what User B is looking at. And this happens in real time, or whatever time it takes for their devices to sync anyway. And uh, that's bad.

---

@WillowMobileSys wrote:
If you were to decide this is the best approach,  you would adjust the "filter criteria" table so entries are PER USER - each user has their own filter criteria row.

It's so far the only solution I can think of. Yes, I could add more rows to the Filter_Transactions table and use Security Filters to only show the row for that particular user. And that should work. For this particular instance.

BUT it doesn't scale and I'm just curious about any better approaches to this issue for future projects.

TLDR:
How to:
1. Provide multiple concurrent users with a user-defined way of filtering a set of data (that isn't appsheet's desktop-only filter functionality).
2. Present this filtered data in a view that's unique to each user.
3. Have this method not require manual configuration each time a new user joins in.

Again, thanks for your time! Really appreciate it. And Security Filters are a better approach to the solution I was thinking of. I'll remember mark your post as the solution if nobody else chimes in after a couple of days.

[MultiTech · Accepted solution]

The following video shows how to do something similar to what the demo app you were using does, but it's built so it supports many users (how it's supposed to be 😉).

It uses the Current User system to differentiate users, making it easy to have multiple people using the same app - while still allowing you (the app dev) to conform things around the specific user using the app.

Hope it helps!

[loolookachoo]

Edit: Oh hey look, I can accept multiple solutions. Don't know why I assumed I couldn't. Thanks for the help!

Thank you . Your videos have been such a great help (thanks for teaching me how to do loops - it's already come in handy on this first appsheet project of mine).

Anyway-

I'm sorry if you mentioned it and I just missed it but, while your video is great and also answers my original question (and I highly recommend watching it to anyone that needs this functionality), it's kind of missing an important piece of information when it comes to actually implementing this in an app.

And that is, "How do i add more users to the table that saves my users' selected filter criteria".

In your video's case this table is called the User table, and while you did explain well on how it functions within the scope of filtering slice data, I don't think there was a proposed approach on how to manage adding rows into this User table to begin with.

And I feel it's important to answer this question because, for me at least, the natural conclusion was: "Oh I guess we just go into the spreadsheet and make a new row every time we have a new user". And in many use cases that's perfectly fine.

In fact, that's what I was going to do until @WillowMobileSys gave me a better answer. It's a method that delegates the row creation to the user and that's just better overall.

[WillowMobileSys · Accepted solution]

---

@loolookachoo wrote:

BUT it doesn't scale and I'm just curious about any better approaches to this issue for future projects.

---

Maybe we have a mis-understanding? It scales perfectly such that each user gets their very own filter row.  Each time they go into the Form to set filter criteria their personal row is retrieved or a new one added if they don't have one yet. 

I presently use this format for 2 different Dashboards across a set of about 30 different users.  It works very well!!  There is one panel the user uses to set Filter criteria.  Once set, a dozen other panels update to show the specific details.  I only ever have about 30 rows in each of the Filter tables.

---

@loolookachoo wrote:

1. Provide multiple concurrent users with a user-defined way of filtering a set of data (that isn't appsheet's desktop-only filter functionality).

---

You already have a Filter table.  Simply add a column for the user who sets the filter criteria.  When it's a new user, simply add a new row for them.  Existing users retrieve the row with their ID.

---

@loolookachoo wrote:

2. Present this filtered data in a view that's unique to each user.

---

I am not sure what you mean by "unique".  But the most common way is to use a Dashboard view.  One panel is the Filter criteria panel represented by a Detail View with QuickEdit columns.  The rest of the Dashboard is one or more panels showing the filtered data.  In my case I have a dozen panels showing data across many tables.

---

@loolookachoo wrote:

3. Have this method not require manual configuration each time a new user joins in.

---

I mentioned above you can simply add a new row for a user who has not yet filtered.  How you go about this will depend on the design.  As I said, I have a Detail View to capture the Filter criteria.  But this only works if there is a pre-existing row.  To handle the case when its a user filtering for the first time, I have to adjust and present a Form View for them to enter a new row FIRST.  AppSheet provides some built-in navigational control so, once they Save, I can then send them to the actual Dashboard view with Filter Criteria already in place based on their newly added row.  From then on, they always go the Dashboard view, where they are presented their existing row, they modify the criteria and a new filter is applied.

I believe you can build a very similar system and it'll work quite well for you!!

[loolookachoo]

---

---

First off, thank you! I get it now! And sorry for not understanding your initial response.

(Everything that follows is just me explaining my misunderstanding)

I mentioned above you can simply add a new row for a user who has not yet filtered.  How you go about this will depend on the design.  As I said, I have a Detail View to capture the Filter criteria.  But this only works if there is a pre-existing row.  To handle the case when its a user filtering for the first time, I have to adjust and present a Form View for them to enter a new row FIRST.  AppSheet provides some built-in navigational control so, once they Save, I can then send them to the actual Dashboard view with Filter Criteria already in place based on their newly added row.  From then on, they always go the Dashboard view, where they are presented their existing row, they modify the criteria and a new filter is applied.

---

When I said "it doesn't scale" it's because I thought that in order to support more users, I would have to go into the spreadsheet file itself and manually add a new row to the filter table for each new user of the app.

I thought this was the case because, when i was reading your original response, I thought there was no way to do this task via a form in the app. At least not without having the Filter table form just kinda sit there and be accessible forever (as opposed to hide itself once the user already has an associated row in the Filter table).

This concept of using navigation controls solves my issues and it's something that I didn't even know existed until I read your response just now. Everything kind of clicked into place after that.

So yeah, I can see the way forward now. I'll give this a shot! Thank you!