hey everyone welcome back to my channel my name is robert fedoric it is so good to have you here in this episode of servicenow toolbox we are going to be describing the concept of many to many tables and how to build them two different ways now if you already understand why m2m tables exist you can use the chapters in the description below to skip ahead to the actual build part if you're new to servicenow or new to how data structures work in general it's really important to know why these kinds of things exist you may already be familiar with the concept of reference field wherein one table references another table like which location is this user a part of where the user has one reference to the location but there are many other cases where you have table a and table b and they can have many relationships on either side a good example of this is users and groups a user can be a member of multiple groups but a group can also have many users in it the way databases handle this relationship is by having a table between the two tables you care about that stores the relationship between them like in the case of users and groups there's a table between those two tables that says here's a record representing the relationship between a user and a group we're going to look at how to build these things two ways and each way will provide unique advantages and consequences okay the first type of many-to-many table we're going to look at is one that i call the middle doesn't matter and you see this all the time when you're dealing with users and groups you care about groups that have members you care about users to have groups but you don't spend nearly any time looking at the records in that in-between table here we are on a user record let's go down to the groups that this user is a member of and we see they're already a member of the servicenow admin group and let's see what the edit button does on the related list as you can see it gives me a slush bucket of other group records i'm going to pick architects move that over hit save now we see that this related list is showing both servicenow and architects i know that there's an in-between record that is joining the user record to the group right to the group record i just don't care about it right now now another thing that's special about this type of m2m table is how the new button behaves on a related list so i'm looking at groups i hit new and it's actually asking me to create a whole new group not a relationship to a group a whole new group so if i say robert is a member of the servicenow mega admins and when i hit submit it creates the group and it creates a membership to that group the in-between table between users and groups to build one of these we got to go to sys underscore mtm.list and this is where we create the special middle doesn't matter type of m2m table so we're going to click new and we pick the from table and the two table my from table is going to be incident my to table is going to be sysuser it's already created a database name for the table you'll also see on the right hand side it's got some names for the references both database names and labels it will do a good job of automatically creating these for you but i want to add some nuance so the use case here is maybe we have users that need very special communication given to them maybe they speak different languages maybe they speak in sign language and we need to have special contacts made for them if they are associated with an incident so really we want to many to many on the incident where we just have the users so uh here we're going to say what's called a major com incident and in the label we're going to say major com users and that's just going to help us identify the related list in the list of other related lists so let's create many to many now we see our uh table in here so we can go to the incident form and see that table in action so i'm gonna go to incident now i've pulled up an incident form so that we can see this many to many in action so let's right click configure related lists and let's grab the major com users that's now on a related list and if we go there and we hit edit we can see a list of users that we can add to that so we'll put able tutor abraham lincoln and adam ringle and they are now related to that incident in a many-to-many fashion likewise we could go to adam ringel's user record and we could build a related list of the incidents and we can add incidents from there the next type of many-to-many table that we're going to talk about is one that i call the magic in the middle and in this case you're not only joining tables such as user and group with a relationship record but the relationship record itself has properties of its own that we care about so we might populate extra information about the relationship or report on that table for various reasons so for this example i imagined a world where i might want to have validated action on some type of task type it's not enough that i put something in the work notes or the or the additional comments maybe i want to say i literally did this amount of research i literally did this amount of assisting a colleague i took this that or the other definitive validated defined action so i really quickly created a table called validated actions it's not a sexy table it's just got a name and whether or not the thing is active and then i populated it with a few records assisted teammate emailed client external visited the client walk through escalated or researched and those are the validated actions that i can take against an incident then i created a conventional table i called it incident validated actions and let's take a look at the columns that i've created so we created a reference to incident and a reference to the validated action table then because there's other information i want to catch i created a special notes field and because i wanted to capture how long did i work on this validated action i created a time logged field so it's not capturing just a reference to each table it's capturing other information too let's see it in action so here i am on an incident so i scroll down to my related lists here i see that i have an incident validated actions i see that i've also got an action i've already taken and you'll notice that the related list does not have an edit button this time uh because i didn't create it via that cis m2m table i just created a regular old table and if i click new on this unlike the sys m2m method it's not taking me to the other side saying hey create another validated action this is asking me to create one of the relationship records so it already knows the incident this is connected to it's got a number for the action and all i have to do is pick a validated action and in this case i'm going to click walk through and i'm going to say walked through procedure one two three and that took me one hour and i'm going to submit that so there's extra information about the relationship that i captured there and there i have another validated action on my incident because an incident can have as many validated actions as you want and we're going to just take a look at one of those validated actions and we can see that the assisted teammate validate action can be linked to many incidents as you see here so there you have it folks two ways to build many-to-many tables and which one you choose depends on the kind of experience you want remember my model if you want to have two relationships where the middle doesn't matter you just want to be able to add a bunch of them to a record at once then you'll use the cis underscore m2m table in order to add those one thing that i should have mentioned during the build is that those suckers are actually really hard to delete so when you go to build something in the cis m2m table uh be really super sure it's the way you want to build it if you want the other experience whereby the table in the middle has properties of its own that you want to track and anytime you click new from the related list you want to create one of those relationship records then you want to just take a regular old table and make sure it's got a reference to each of the two tables you want to relate in a many-to-many fashion thanks again for watching if you think this was useful give it a share through your network i'd really appreciate it and we will see you on the next one if you're a servicenow expert looking for better opportunities but maybe your resume or linkedin profile isn't doing you justice reach out to me via linkedin or the email pictured here as i offer both career coaching and recruitment services and if you're a servicenow customer or partner you heard that right robert fedoric now does servicenow recruiting with a 1 500 subscriber youtube channel and mailing list and thousands of linkedin followers let's make sure your open positions get first go with the prodigious pool of servicenow resources reach out via the emailed picture here you