Best way to tie two tables together besides relational column?

I’m building an agenda app. I’ve got my users table with contact info, and an events table with all of the information about that event. Then I built a third table with username and event name, which allowed me to build a relational column on users (to show all the events they’re going to) and events (to show all the users attending).

Problem is, I can’t figure out how to pull in the rest of the information in those tables. If someone views a list of users and taps on one, they can see that user’s events, but they can’t see stuff like what time that event is. If someone views a list of events and taps one, they can see a list of attending users, but not stuff like contact info for any of them. And on that list of events, I can’t filter for “my events.”

Do I just need to build a ton of lookup columns? Is there a better way to tie this data together than a third column with the user-event pairings? Feels a little silly to end up just replicating the same data across multiple columns via lookups.

I usually prefer either putting a list of events in the user table or putting a list of users in the event table. Not a big fan of a table in between, but that usually means a little more setup to get it working.

That aside, with your setup, when you get to the detail screen for that middle table, I would maybe add a custom collection to show the event details, and another collection to show the users. In your middle table you should only need a user ID, an event ID, a relation to the event table, a relation linking the event ID to itself in the same table, a lookup of user IDs from the relation, and a final relation linking the lookup column to the user table. The first and last relations will be used for your collections.

1 Like

Thanks! When you say “a list of events in the user table or a list of users in the event table” do you mean a separate column for each event (in users) or user (in events)? I’ll give both of your solutions a shot.

No, ultimately it would be a comma delimited list that is then converted to an array using a Split Text column. That resulting array can then be used with a relation. The tricky part is being able to add or remove items from the comma delimited list and usually involves a few other columns and some trickery.

I see what you’re asking. Yes…you’d need to create a bunch of look up columns.

Instead of a Joined List, I like to create a non-computed array directly with a Multiple Files column. Adding and removing items is really easy (Make Array & Remove Item from Array), and it works quite well for these sorts of use cases.

This wound up working! I still have the intermediary table, which I used to pull a list of attendees into a relational table, then made that a joined list, then made that a split part column, which gave me everything I was looking for. There’s probably a cleaner way to do it than I’m doing but as long as it works. Thanks!

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.