Can someone please help me figure out how to implement a many-to-many relationship? I’ve been trying for quite awhile now without any success. I’ve been reading different things–some say you need to use a “hack”, while others are suggesting you can just use the build in Relation field. My understanding is that the Relation field only works for one-to-one & one-to-many relationships, but not many-to-many relationships. Is that correct?
In any case, I want to have an Event that can have multiple Drills assigned to it. And each drill can be assigned to multiple Events. Can someone PLEASE walk me through how to set this up properly if it’s possible?
The key is to have a common value between sheets. You just need to set up a relation column in the Events sheet that links a common value from the Events sheet to the Drills sheet. Then set up a relation in the Drills sheet that links a common value from the Drills sheet to the Events sheet. In some cases you may need to use an array of relations, but that all depends on your setup. If you have a sheet, it’s easier to visualize. I think the Employee template is a good jumping off point where you can see how employees are linked to other employees above and below them. Also the template shows links between employees and offices.
I guess the main question I have with this whether or not it requires you to add the columns with the special syntax manually in the sheet. For example, do I need to add a column called “Events=Drills:Event:Multiple” & “Drills 1”, “Drills 2”, “Drills 3”, etc.?
The other question I have once I get this set up correctly is is it possible to allow the app user the ability to add or remove relationship from an item (i.e. add/remove drills from an event)?
Thanks so much for your help. These are the only 2 questions keeping me from doing some really cool things with this platform. Overall I absolutely love it, but this many-to-many relationship setup is a bit confusing to say the least.
@jonlutz79 The best way to do a many-to-many style of app is to have 3 sheets. Here is an example app that I put together for you and others to help understand the structure. It’s a very simple Events app, where you can sign up people to attend an event. Sign up can be done on the Events page/tab or the Participants page/tab. There is a list of Events and a list of possible Participants that are attending the Event. So 3 sheets. Events, Participants, Attendees. The Attendee sheet is the key to many to many relationships. I made it a template so you can copy the app and play with it. If you have any questions ask away.
All relations and lookups use Glides inside the builder relationship and lookup column creation in the Data section. https://many2many.glideapp.io
Very observant of you. I was going to mention that as a caveat when I posted it.
Anyway, no I don’t know of one within Glide’s interface. If I was implementing it in a live app, I would run a timed script in the background that would automatically remove double sign ups.
@George_B Thanks so much. This is perfect. I was able to set it up correctly, & in fact this makes complete sense as I’m very familiar with how to model many to many relationships from a database perspective. For whatever reason I wasn’t able to get that information from the docs.
So, it looks like I can add a relationship based on the button you have in your example to sign up an attendee. However, I have 2 more questions for you.
Is the Choice selector the only option when it comes to adding an attendee? Or is there a way to include a search for attendees to add?
Is it possible to allow a user to REMOVE a relationship (i.e. remove an attendee from an event)?
Another option, depending on why you care about people signing up twice would be to use a combination of the UNIQUE and COUNT functions to count the number of unique attendees a particular event has. I added the formula below to the Events sheet to count the attendees for an event. Note it isn’t an arrayformula, and I’m having a brain freeze on how to implement it that way so it doesn’t have to be added manually to each new event row
I think the answer to both questions is making the App login style Public with Email. You would then introduce the Attendee page into the app and set it up to filter by logged in user. They could then see the events that they signed up for and delete them. You would have to add the email columns to both the Participant sheet and the Attendee page. This way you can create Tab views of the Participants where they only see their names. Then on the details they could only add their names to events and you would be able to let them delete their names from the Attendees sheet view but not others.
George_B, love your example app for the many2many. I am totally fine with the modelling and have apps working totally fine with this but it is first now I am having to create an app where the user creates a record that belongs to a parent record. Is the form submit the only way to pass through the ID of the parent record to be stored on the subrecord?
Despite setting up the relations both ways I am struggling to get the sub record to store the parent record ID.
@dkjorgi Yes, the form is the only way to do it. Remember you are creating a link between the two lists (sheets) and the result is stored in a third sheet (Attendees). So you have to be in a details view of one of those two sheets in order to know which single record (row) to link the one side to. Then you use the choose component to select which record (row) of the other sheet to link it to.
It would certainly be nice if there was a Search component, as a Choose component gets unwieldy quickly when the list of records is large from which to choose.
@Mark and @david BTW I originally was going to use the unique ID feature to make the link relationship on the Attendees spreadsheet but realized that it wouldn’t work for this many to many style of relationship because of the choice component. When adding an attendee to an event either from the participate detail side or the event details side of it, both need to use a choice component to select the other side of the relationship, and since the choice component only allows you to select on column, using the Unique ID column would be useless.
See my post about an enhancement to the choice component:
If you are willing to have events and participates all listed on one sheet then you could have a text entry and a choice component pointing to the same column and a user can select an existing event the same could be done for participants. All on one page. Your choice components would then need to built off of a unique list of events and a unique list of participants in a separate sheet.
