In any scenario, the user selected would need to have its own column in the Admin tab…especially if there are more than one admin. One thing I didn’t mention in all of this, is that the admin screen in the app is filtered by signed-in user. This way every admin has their own instance. Likewise, Admins have the ability to select different users at different times.
So any UI patterns that we could come up with have to write the user information to that column. I imagine the easiest way would be the way that I did with the choice component. I guess you could type the name in manually, but then you run the risk that the relationships break because the name doesn’t exist in the names sheet.
This is a brilliant and very helpful solution to a common trouble-spot.
I’ve experimented and adapted a variation on the same idea that allows for a separate sheet for each instance of this pattern within an app. In my use-case, I have several instances of this pattern and so using the primary “admin” or “user” sheet as the “home base” is not practical. But the principal is still the same as what you’ve created.
In summary, I’ve created a sheet for each instance in my app where this pattern is needed (creating many-to-many joins that should be unique). In each of these sheets, I’ve created a user-specific column that records the selected “user” or “blog post” or “whatever” that should receive only one assignment to whatever “team” or “tag” or “whatever” is on the other side of the join scenario.
So, for the scenario we’ve been working with in this thread, I’ve created a sheet called “Add Memberships”. It has a user-specific column that captures the “selected user” that will be added to a soon-selected team. This sheet has a column called “Current User ID” which is a template that captures the “Row ID” of the current user. It also has a column called “Selected User ID” which captures the User ID of the user that will be added to a soon-selected team, through a row in the Memberships sheet.
From there, the process is what you’ve created here: the Teams sheet has a template column that captures the current user ID, then a relation column that uses the “Current User ID” column to relate to the Add Membership sheet’s current user id column. Then, we can use the Lookup column to return the “current user’s” selected user in a column in the Teams sheet. And we use that column’s value to create the concatenated matcher that you’ve presented here.
That’s hard to describe without show-and-tell, but the bottom line is that this approach provides a way to achieve the same end result by using a separate sheet as home base for each instance of this pattern required by the app… as opposed to using the main user (or admin) sheet as home base for each instance of this pattern.
I just don’t understand at 3:32, how do you grab the PersonEmail with just a template (in a sheet 2). From where does come from the PersonEmail in you sheet 2?
:
