I’ve been going through some old unresolved posts. This one in particular. The original request is asking for global access to the signed in user for filtering. I do see a new filter option in staging that would definitely solve this problem, so I’m excited about that.
The reason for this post is another idea I had after rereading through the old thread. What I’m thinking is a modification to the relation column that would let us Invert the results of the relation and return everything that does Not match. Probably not something that would be used often, but could help in certain situations.
In my situation I have a list of users. In that sheet I set up a relation to a Task form response sheet using email address. I then created a Lookup column to get an array of completed Task Names. With this array of Task Names, I was thinking that I could create a relation to a Task sheet, but invert the results so the relation only returns tasks that are not contained in the Task array.
Yeah, I guess it would have helped to actually paste the link to the original post, huh. I guess I forgot to do that. The original problem involves a non-user specific list of tasks, so only one list is maintained. A user can open a task and then using a form button, they can submit that they completed the task. What I would like to do is to then provide a list of only the tasks that the user has Not completed yet. I’ve been trying to solve this in my concepts app if you need a better visual. On a user record, I am able to get an array of completed tasks using relation and lookup columns. I would like to then use that array list for an inverted relation against the generic task list sheet to only get the records that do not match the list.
Like I said before, I think the new filter options you have in staging would solve this particular problem beautifully.
I don’t have any other great examples, but maybe this would also be useful in situations where you want to return a list of available items for sale, or available timeslots for appointments. By using an inverted relation and an array of purchased items, or occupied timeslots, you can easily only return the unpurchased items/open timeslots.
