# Many-to-Many Relationships **URL:** \ **Category:** Ask for Help\ **Created:** [November 20, 2019, 11:37pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225 "2019-11-20T23:37:17Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![jonlutz79](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jonlutz79/32/55213_2.png) [@jonlutz79](https://community.glideapps.com/u/jonlutz79)\ **Post date:** [November 20, 2019, 11:37pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/1 "2019-11-20T23:37:17Z")
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? ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [November 21, 2019, 12:35am UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/2 "2019-11-21T00:35:51Z")
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. ---
**Author:** ![jonlutz79](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jonlutz79/32/55213_2.png) [@jonlutz79](https://community.glideapps.com/u/jonlutz79)\ **Post date:** [November 21, 2019, 10:53am UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/3 "2019-11-21T10:53:53Z")
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. ![](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/e/e8ac224ba9ee97e87670ed6f3a2f0128b7af9fe4.gif) ---
**Author:** ![George\_B](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/george_b/32/534_2.png) [@George\_B](https://community.glideapps.com/u/George_B)\ **Post date:** [November 21, 2019, 1:54pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/4 "2019-11-21T13:54:17Z")
@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](https://many2many.glideapp.io) ---
**Author:** ![NataliaGo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nataliago/32/1317_2.png) [@NataliaGo](https://community.glideapps.com/u/NataliaGo)\ **Post date:** [November 21, 2019, 2:22pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/5 "2019-11-21T14:22:19Z")
@George_B is it possible to add a data validation so that one person could not subscribe multiple times to one event? ---
**Author:** ![George\_B](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/george_b/32/534_2.png) [@George\_B](https://community.glideapps.com/u/George_B)\ **Post date:** [November 21, 2019, 2:31pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/6 "2019-11-21T14:31:38Z")
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. ---
**Author:** ![jonlutz79](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jonlutz79/32/55213_2.png) [@jonlutz79](https://community.glideapps.com/u/jonlutz79)\ **Post date:** [November 21, 2019, 3:10pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/7 "2019-11-21T15:10:13Z")
@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. 1. 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? 2. Is it possible to allow a user to REMOVE a relationship (i.e. remove an attendee from an event)? Thanks again for all your help! ---
**Author:** ![George\_B](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/george_b/32/534_2.png) [@George\_B](https://community.glideapps.com/u/George_B)\ **Post date:** [November 21, 2019, 3:11pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/8 "2019-11-21T15:11:28Z")
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 ```auto =COUNTA(UNIQUE(FILTER(Attendees!$C$2:$C, REGEXMATCH(Attendees!$C$2:$C, A2)))) ``` ---
**Author:** ![George\_B](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/george_b/32/534_2.png) [@George\_B](https://community.glideapps.com/u/George_B)\ **Post date:** [November 21, 2019, 3:39pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/9 "2019-11-21T15:39:22Z")
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. ---
**Author:** ![jonlutz79](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jonlutz79/32/55213_2.png) [@jonlutz79](https://community.glideapps.com/u/jonlutz79)\ **Post date:** [November 22, 2019, 3:40pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/10 "2019-11-22T15:40:17Z")
I finally got the hang of it now that I got over the hurdle of modeling many to many relationships. Many thanks! ---
**Author:** ![dkjorgi](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dkjorgi/32/1227_2.png) [@dkjorgi](https://community.glideapps.com/u/dkjorgi)\ **Post date:** [November 23, 2019, 8:16am UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/11 "2019-11-23T08:16:50Z")
[George\_B](https://community.glideapps.com/u/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. ---
**Author:** ![George\_B](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/george_b/32/534_2.png) [@George\_B](https://community.glideapps.com/u/George_B)\ **Post date:** [November 23, 2019, 12:10pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/12 "2019-11-23T12:10:01Z")
@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. ---
**Author:** ![jonlutz79](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jonlutz79/32/55213_2.png) [@jonlutz79](https://community.glideapps.com/u/jonlutz79)\ **Post date:** [November 23, 2019, 12:50pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/13 "2019-11-23T12:50:34Z")
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. ---
**Author:** ![George\_B](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/george_b/32/534_2.png) [@George\_B](https://community.glideapps.com/u/George_B)\ **Post date:** [November 23, 2019, 1:28pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/14 "2019-11-23T13:28:54Z")
@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: > [@Choice Component - allow key display pairs](https://community.glideapps.com/t/choice-component-allow-key-display-pairs/581): > > In the interest of some kind of data normalization and using key links in your tables/sheets it would be nice to display pretty text for the choice dropdown but save a key value to the database/sheet. This is more in line with how it is implemented in HTML which is what is ultimately driving things under the covers. This is the idea I’m talking about. The user would see the long text but the save would be the shorter. All that you would have to do is allow the user to optionally select a sec… ---
**Author:** ![dkjorgi](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dkjorgi/32/1227_2.png) [@dkjorgi](https://community.glideapps.com/u/dkjorgi)\ **Post date:** [November 23, 2019, 2:56pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/15 "2019-11-23T14:56:15Z")
> [@George\_B](#): > > So you have to be in a details view of one of those two sheets in order to know which single record That was the key, thank you for that. All working fine now. ---
**Author:** ![Ramo](https://avatars.discourse-cdn.com/v4/letter/r/f17d59/32.png) [@Ramo](https://community.glideapps.com/u/Ramo)\ **Post date:** [December 28, 2019, 12:13am UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/16 "2019-12-28T00:13:54Z")
@George_B Thanks for the m2m app, it’s been really helpful to play with! One of the drawbacks with it is that it’s not the best UX. Current process 1. Add event 2. Navigate to participant 3. Add participant 4. Associate participant to event via attendees In above, ideally, steps 2 - 4 can be eliminated if user can **add** or **associate** new participant to event in event details page. Then when looking in Relation List, see all participants. I’ve been trying to figure it out, but have been unable to In my app, I’m not using the m2m which is causing duplicate participant records. So either way, there are drawbacks ---
**Author:** ![George\_B](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/george_b/32/534_2.png) [@George\_B](https://community.glideapps.com/u/George_B)\ **Post date:** [December 29, 2019, 6:01am UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/17 "2019-12-29T06:01:42Z")
It was just a simple app to demonstrate a somewhat more than basic concept. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [January 5, 2020, 6:11pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/18 "2020-01-05T18:11:22Z")
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. > [@Dynamic List Creation Workaround](https://community.glideapps.com/t/dynamic-list-creation-workaround/2761): > > I have a relative who uses an app I made to keep track of mileage on trips. (Not GPS tracking, she just enters in the destinations and beginning and ending odometer reading.) She has some destinations for beginning and ending, but sometimes wants to add a new one. Just playing around with the choice component. I want my users to choose from a list but also be able to add to the list when they do not see what they want on the list. I have created this as my basis: [https://ojek4.glideapp.io](https://ojek4.glideapp.io) Ad… ---
**Author:** ![Ramo](https://avatars.discourse-cdn.com/v4/letter/r/f17d59/32.png) [@Ramo](https://community.glideapps.com/u/Ramo)\ **Post date:** [January 14, 2020, 3:29pm UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/19 "2020-01-14T15:29:41Z")
That’s a really good idea. Going to try that out. Thank you! ---
**Author:** ![Alfonso\_Aguilar](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfonso_aguilar/32/3833_2.png) [@Alfonso\_Aguilar](https://community.glideapps.com/u/Alfonso_Aguilar)\ **Post date:** [February 26, 2020, 5:04am UTC](https://community.glideapps.com/t/many-to-many-relationships/2225/20 "2020-02-26T05:04:17Z")
Not sure if it is what you were looking for, but you could do [this](https://many2many-norep.glideapp.io) if you filter the available choices using the signed-in user email. [Next page](https://community.glideapps.com/t/many-to-many-relationships/2225.md?page=2)