[Music] [Music] good morning good afternoon good evening wherever you are and whenever you are welcome to the community live stream my name is chuck tomasi senior developer evangelist at servicenow and this is august 10 2020. i am here to talk to you about many to many tables because we never stop learning this show is the uh the show that gives you the answers behind the answers the questions that you've asked the problems things you've posed in the servicenow community and i take those and turn those into a topical deep dive like today we're going into those relationships between two tables and how we can create those ourselves and manage and examples all that good stuff and you know what i started off by saying that the continual learning i learned something just 10 minutes before the show it's amazing what you can learn by reading the documentation good morning to everybody who's checking in on youtube thank you very much for joining me if you are doing so either in real time this is brought to you each monday and thursday at 2 p.m utc so be sure you check that out mark your calendar i suspect i'm catching people who no longer have to commute it's it's 7 a.m here in phoenix and the west coast so hey enjoy your morning coffee with me i've got the uh the brew of tea at the ready i also have some water in case that runs out so we're we're set to go and i hope you are too go to the youtube channel subscribe and like and click that notification and as i showed last week you will get yourself a very special present every morning that says hey the community live stream is live well that's a good thing so checking make sure we have audio i messed that up last thursday i apologize for that thank you for everybody in the community letting me know it is good to have this interaction with you and again thank you for joining me if you're watching this later don't forget you know click the like and share and all that good stuff we really do appreciate that very much we do have oh i've got a little bit of a green screen thing artifact showing up in that lower left corner i'm going to keep an eye on that and let me just back off the there we go the black clip was just a little off i do things over the weekend and sometimes the camera settings just aren't exactly like i like them on monday morning so we'll continue on with that thank you again for joining us uh we do this we do we also do this on twitch so if there's any reason youtube isn't available for you or for me we should have a backup there if both of them fail i've got a local mp4 that i'll upload after this is done so we've got we've got the the base is covered it's like anything else in it have a backup and then have a backup for your backup well twitch is backup one and a lo the uh mp4 file is backup two so you should always have a backup plan speaking of backups if you want to back up your no go forward in your career not back up but go forward to your career i invite you to go check out developer.servicenow.com where you can get a free personal developer instance all of our apis are there free learning plans lots of great stuff on the new release paris which is still in early access as of the time of this recording in august of 2020 it will become general availability in september so look forward to seeing that but if you want to get your hands on this you want to practice operating from an orlando instance to paris because if you're new to servicenow our releases follow the letters of the alphabet and they are named after cities of the world so if you want to sing along with me in doing the servicenow alphabet you could do it aspen berlin calgary dublin eureka fuji geneva helsinki istanbul jakarta kingston london madrid new york orlando paris quebec is next spring rome is after that san diego and we don't know what's after that so we have to finish this alphabet at some point and then maybe i can retire [Laughter] write that down sing it to your kids at night do whatever you want that makes it fun go over to the developer portal you can get that uh we're all having fun today also available from the developer portal if you go under that uh connect menu up on the top and click events you will see not only upcoming webinars we've got tech now coming up on the 18th that i'll talk about a little bit we've got podcasts that are coming out we're still working on the graphics the podcasts have a label that says webinar so excuse that for a little while we are working on that because the podcast is new and i will get to that in a moment as well but we also have meetups and if you look i still need to work on the screen controls for this over at meetup.com there are chapters all over the world from look at this corner down in sydney and new zealand to singapore up to finland and sweden and canada and brazil all over the place so you should have something close to you that you can connect on a zoom call and meet with other developers hopefully in your language they're not always in my language so local meetups are a good thing but extend out and reach to some of those other chapters i've attended a couple last week learn a couple new things that's what it's all about the continuing education theme that we've got here so i'm not only sharing with you but you're sharing with me and we all we all learn grow and gain from that it's a wonderful thing so meetups are great did i get that url down there meetup.com pro slash servicenow dev program you can also find that through the developer site the community which is over here i showed that originally is where you want to post those questions that go beyond today's topic so if you've got a question about many to many tables i'll be happy to try and answer it in the live chat or in the comments later the number of you do put comments on the youtube videos and in the uh the reply to the video that ends up on the community so we're doing a community video on youtube that gets back into the community that's how it works we'll get you covered ask there if you've got other questions maybe you've got a grc question or an acl question or something else that has three letters in it feel free to ask that in the community at community.servicenow.com there are thousands tens of thousands i think it's into the hundreds of thousands of people that are eager and willing and able to help and learn from your questions too because it is a persistent database and they can search later i just had a response this morning from someone from a question i asked 11 years ago about email notifications and somebody said hey i'm having the same thing did you ever have that answered i went back into the prb database in high all of our problems yeah this stuff is still around so it's nice to have a system of record you can trust for more than a decade later oh people still checking in thank you very much for joining uh another quick reminder if you've built some apps built some utilities on the on the platform please share them with us so that we can donate fifty dollars per app to the un covid19 response and recovery fund go to the bitly link you see there short little questionnaire i think it's about five questions just to tell us about the application you built and we'll make a donation on your behalf thank you very much for those who have participated in that already we do have still introducing the new breakpoint podcast for servicenow developers of all skill levels we'll be covering a lot of stuff we had a discussion with brad tilton on how to get started as a servicenow developer last week if you're not subscribed you can find that at the bitly link you see there sn breakpoint or sn breakdashpoint if you want to get technical you can see it you can read why am i reading this to you you go over to to there you can catch this on your favorite podcast directory apple google wherever i know it's on spotify uh founded on radio.com so it is getting out there so you can find this podcast wherever you pick up your podcast or listen directly we do post this into the community next discussion is wednesday the 19th of august so just a few days away with dave slusher about some of the paris platform features and we will then be going and talking to more people in the future i may even slip in some special episodes i'm starting to get more content than i've got time slots otherwise i'm not going to be able to get any new content until after the first of the year and i really don't want to hold that up for you so look for any special content we are about a week away from our next tech now episode tech now is the web series forest service now developers admins builders of all skill levels on a wide variety of servicenow topics i can do that without even looking at my cue cards now go to the bitly link that is on the bottom of the screen and we will talk to wolfgang about how to build now experience components i'm very much looking forward to this because i haven't had a chance to dive deep into this i've observed other people doing it but it's just not the same and he will give you a walk through for this i also noticed he came out with another video on his barely functional developer youtube channel clever name by the way shall we begin let's get my mug back on the screen there we are wow lots of people checking in we've gone through the intro we're 10 minutes in part of the reason why i do a lot of that is to give people a chance to join thank you for joining me let us commence with the topic du jour i've got my paris bow tie i call it that because one of our wonderful partners gave this to me in paris a couple of years ago maybe it was 2018. it was 2018. that was two years ago where's the time going so fast well it was not the paris release back then but i thought i'd wear this occasionally during the paris days it does do a little bit of trickery with the green screen behind me so we'll we'll make the best of what we can okay our topic today is many too many tables and you may have seen these before so let me back up a little bit going back in episode or two about reference fields reference fields as you recall let me get this up here and go to my classic incident table where i will show you a reference field or three reference fields allow you to reference a record on another table for example our classic incident caller is a reference to the cis user table i know this because when i right click on the label and say show caller id you have to be an admin to do that it says it's on the incident table here's the field it's a reference to this table pretty straightforward we give you this nice little info box that tells you all about the table or all about this assignment group very similar show assignment group and i get something very similar this is on the task table it's a reference to the sis user group table so one record pointing to another record and in doing so you create a one-to-many relationship so i can use for example whenever you have a reference field let's collapse that back down i get the free feature called a related list and if i look at i'm going to go to global so i can configure my related list configure down here related lists any records that reference incident have the ability to show up as a related list and you'll notice there are a couple of things any i should say any record that reference incident or any of its parent classes which includes task there's a couple of notations down here if you haven't noticed this before check this out there's some with an arrow which means it is a reference field it is a one two one and there are some that have no arrow that means it's a many-to-many relationship so it's got a little different relationship in there and we're going to create one of those many-to-many relationships you hadn't noticed that that's generally what that means if i put any of these lists look how many of these things reference task or incident and i say let's put um look at this loaner request is on there because it has a reference field to task even though it has nothing to do with this i could put a request on this to put a reminder on there reminder to task and i get a free related list oh how i wish this were the case in mobile that it was that easy but now i have a related list of reminders if i create that one of the fields in here is a task which includes incidents and it's pre-populated with my incident number a reference to that that's reference fields in a nutshell just wanted to remind you what that was and how they differ from many to many tables many tomato tables my classic example of this is if i go to user administration and in users i will see here's my account i see a groups related list and it has this edit button on here and when i click edit and say i want to add chuck to the database group this is called a list collector some people may call it a slush bucket i've tried to avoid that term somebody told me it has negative connotations in certain cultures so list collector is how i'm referring to this i can assign any number of groups to this user and likewise each group can contain many users many groups many users how is this done oh i wish i had this when i wrote my ticketing system that i started in 1994 and signed off in 2008. i really wish i'd come up with this feature it's very very powerful if i create a new record it's going to create a new group record so i don't want that and i don't always need that on my list but in between notice if i click survey creators it will take me to the survey creators group because it's a reference field in the first column how do i see behind the scenes remember that little eye icon that nobody ever seems to click well this is a good time to discover it and i click it and it says i have a user and a group reference in this table now it's currently read only i don't modify this directly it's this intermediary table show xml this is a very handy little feature that i don't often use enough and it tells you what table this is on there is a table between group and user there's a table called sys user group and a table called sysuser and between there is a table called sysusergr member now this is more than just a table with two reference fields there's some other stuff that goes on to make the list collector appear if i just simply created a new table with two reference fields i don't get an edit button it and it doesn't function like i would expect with a many-to-many table i could still do some dot walking and look ups and normal stuff but there's more to it if i say show user it's on the table says user it is a reference but there's more to it than that the system knows how to deal with these because there's a definition that goes on behind the scene and what i learned 10 minutes before rolling the cameras is this other table i went to the reference default many-to-many relationships crazy table name i know because i went to look for many to many definitions and said there are two tables that are involved with these many to many tables the first is syscollection.org didn't know about this because frankly i didn't care it was just sort of behind the scenes but it's nice to see and know what's already there i went to says collection.list and said tell me anything that has cis underscore user in it it says yeah i've got a couple look there's my entry for sysusergr member and it has a field called sysuser that's one direction and here's the other one this user gr member has another reference that goes to cis user group and it tells me all about the labels and the what fields are being used to join notice that when you reference this user group it has a field called group a pretty easy that's some of the extra stuff that goes on beyond just creating two reference fields on a table so this this collection table is important to the many-to-many relationship the other one is the definition cis underscore m2m.list let me see if i can blow that out for you yes no there cis under m2m that's also in the documentation and i found this known about that one for a while if i go to oops sis m2m dot list notice that it doesn't have at least last time i checked it doesn't have anything in here about this user gr member that would be the many-to-many table somewhere in this middle column so the ones that are defined by us don't necessarily show up here the ones that are defined by you do definitely show up let me give you an example of what this looks like in action i'll bring up studio i've got my good old-fashioned community live stream application and in my data model i have a course table thinking of taking a class you may want to have a course associated with that and i have a program table kind of a curriculum a larger overarching class of programs if i want to be a computer science major or a cooking major or an english major what courses do i need to take now some of those courses may be in both so right now all i have on the course table is a reference field to program meaning each course can only be assigned to one program that's not all that helpful because if i'm an english major i may still need to take remedial mathematics or i may need to take history of the world or basic weather or you know some of these other liberal arts courses that can be used across multiple programs let's fix this instead of using a reference field let's employ a many-to-many table this is a fundamental change in my data model i've had to make one of these before it's not the most fun thing to do so think ahead about when you may need a many-to-many entity and it may not always be obvious for example when i wrote an exam management application i had exams i had an exam with multiple questions each question could be on multiple exams i had answers that were a direct relationship to that question so that was a pretty obvious one then i got to how do users take exams well each user could take any number of exams any number of times aha this got a little more interesting that was a many-to-many table between exams and users or students or customers or whatever you want to call them they are people entities some of these are task-based like that scenario some of them are asset-based your cis can be related to many different services one ci could be servicing many different services so it's not all task based it could be asset based as well it could be anything based really it could be you know which fuel do i use with which uh vehicle sorry struggling for words still early here all right so think about that if you can get your data model aligned ahead of time rather than thinking about this later it will save you a lot of work in how you manage that you may have flows you may have scripts you have other things that need to be modified because you change your data model that's that's sort of key to get the data model fixed first so i made a mistake here let's go and fix it let's create that many-to-many table if i were to do this outside of studio i would come to this cis under m2m table and i don't believe many it's not in the menu you have to go to sis underscore mtm.list and you could click new right the other option is of course going to studio which i pretty much live there now and it will bring up a form very much like that create application file up here in the upper left and look right under data model many to many definition there's a definition part and there's the other stuff that gets created the fields the tables that sys collection stuff all of that that happens on the back end this is automatic we fill out one form and a lot of stuff happens here so i'm going to go from the course table to the program table i don't know how many program tables there are but there's mine and it creates this table x underscore company code underscore scope underscore m2m i like that notation because it tells me right away it's a many-to-many and then it uses a plural of the two tables i don't care for plurals in my tables you can do what you like sometimes it even makes more sense to name this thing entirely different for example users and courses excuse me users and exams i renamed that to my score table because i kept what score did they get on this particular instance of taking this exam did they pass did they fail you can add additional fields to this after it's built not to the definition but to the resulting table i'll show you what happens there for the moment i am going to follow my personal best practice you can choose your own if your organization has their own standards bear with me over on that's pretty much all i change is these two tables and this many to many table this part over here really doesn't need much attention it does what it does but think about the labels it looks backwards because my from table is the course and my from field is coarse it feels backwards like the from field should don't worry about it leave it alone for now you can change the labels later don't screw around the fields because you could end up getting your references all whacked out so once i'm done with that i create many to many a case where you might want to modify the labels is where we had users and groups it didn't make sense to have i think we relabeled it members okay group members something like that so the label is always up for that this is now read-only the definition has been created cannot change the definition once the definition has been made the other thing it makes for me is this new table terribly labeled scope m2m program course again we use the table labels in this this is course programs give it a better label i called mine scores on that exam application it's just an m to m table okay so i the definition is created the resulting table is created and i'll bet if i go back to sys collection.list i will now have something that starts with i don't i would have thought i did starts with x underscore 66 contains maybe cls nothing huh okay we're gonna have to read up more on that but not important at the moment that's probably why i never really paid much attention what it did give me in this resulting many-to-many table still has the same name okay with this m2m program course in here good to know terrible to type is a course and a program these look like normal reference fields and they behave pretty much like normal reference fields until i get to the course form course let's go to my courses and oh i'm sorry let's go to programs yeah never mind i can do it here here's the tasks that are available to a course and when i go to configure related lists just like any other regular reference field i now have programs notice there's no arrow so i need a mini let's select that put it at the bottom and i can easily identify free edit button free list collector i could say this course belongs to these programs yay do that and my related list is fully populated but what it really did is created a many-to-many record in here with the two reference fields mine are not read only so i could go in excuse me that was unexpected otherwise it would have reached for the mute button go in and edit those similarly if i take any of those programs this iff program and i have a courses related list because i already had that course to program relationship built notice that i'm going to also have what appears to be a confusing now that arrow kind of helps doesn't it this one is the one to many this one is the many to many and this doesn't help me at all if the labels are just off here but since i'm deprecating that other list i can take it off and say you know what i really don't need this one anymore because i have my great new many to many table migrating the data is going to be another situation you will need to write a fixed script or some other script to go through the existing course records find out what programs they pointed to so i'm not deleting the program field from the course table yet i don't want to go up here and delete this or i would lose my data my legacy data i've already associated courses to programs but now i can also have programs related to courses so i can have many programs with many different courses and mix and match them as i feel fit couldn't do that before this came up in the jeopardy app that i wrote for knowledge 19 that i had originally each player to one game and i said well that's silly what if we have a championship and i want to bring those players back and i want to create new player records because i want to keep their scores their grand total winnings all put together have a many-to-many table of players to games and each instance is what they won or lost in that particular game and then i can take that and roll it down to the player's record so having this three-tier structure or if you want to think of horizontally with the many to many in the table having these three tables allows you some greater flexibility in managing these many-to-many relationships very very powerful stuff just looking over the chat a whole bunch of good mornings wonderful stuff did i miss anything um oh yes i did okay so i mentioned that you can add additional fields at this point the many-to-many table course programs is just like any other table if i want to add another field i can go in and do that and it doesn't interfere with the way that records are created or deleted the other thing to note about these is here's my many to many courses if i said the iff program is made up of those courses it's creating new records this is the important part to remember the many domain table is creating new records on that interstitial table the one that's between course and program the m2m table is getting new records inserted into it in fact if i went and looked and said show me the list it will show me here are the courses clean if oh look gear it if infrit we've got different combinations just like you could have different users on roles or different users in groups there's a many to many table that's in between there that's holding both pieces yeah i really wish i had this when i was coming up with things 25 years ago but didn't know back then uh what happens when you take a record off using the list collector guess what let's look we have seven records there we'll take rafal off of the program and refresh this list and it deleted the record so if you want come on there are now six records if you want to have a business rule or flow or some logic that happens when you add or remove groups from members or members from groups or however you want to deal with that or programs from courses you can put a business rule on there or a flow to be triggered just remember there's that special case of running delete queries after the record's been deleted it gets a little messy so it's like hey go tell me about the record you deleted oh i can't it's deleted all right that gets a bit a bit a bit crazy those records are deleted so if you had scores for example for exams students have taken exams and their scores are recorded on this interstitial table you are going to lose those scores if you remove them from that table so again think of the taking things off of this list as deleting records this is as dangerous as deleting cis or tasks or anything else and you should have that mentality do i really want to delete that it's very easy to just go left to right right to left oh my gosh where'd all my data go you deleted it so that that happens the other thing to note is if you ever need to say oops that was not the right table or oops i really hate the name that i gave that you can take the course programs table let's go back to close that out bring it up as a table not the list of stuff i gotta get rid of that tickle in my throat before it causes another cough this is a table just like any other table and i can delete this the problem comes in and says well okay let's delete that and it will take care of all the related lists and forms and everything else this however is held down here in studio you can see it under many to many definitions and i click that and it's read only i don't have a delete option if i go to sys sysm2m dot list i don't have a delete option well look there's a trivia game i was going to put we're going to build that earlier this year uh let's see if anything starts with x under cis m to m there is my course to program course department and it's red oh it's got red it says hey you know what you did something you deleted this table it doesn't exist i'm going to yell at you about it and as a result it won't work but if i if i messed up and i said i i really wanted the plural on there or maybe i didn't want the plural or i wanted to call it something else you can't use these same parameters because this is in the way it's saying this definition already exists you can't define them any to many twice so chuck how do you delete this i'm glad you asked you have to do it manually it's not pleasant this may be a fixed script if you wish uh i copy the sys id and i go to scripts background my good old friend var m to m equals new glide record sys m2m m2m.get sysid m2m delete record and it needs to be in the scope that you created the scope in now because i did this insist background i can always bring it back but i won't and if i go to studio and refresh it should be it's not gone oh did my thing fail this mdm delete operation against cisa m2m has been refused due to the table's cross go privilege maybe i need to do that in global i always thought that was uh there it is deleted one system to m record okay note to self when you're deleting system to ms sys m2m itself is in global that's what's controlling my delete access and of course my system definition is gone so if you mess things up you have to delete not only the table that it created but also the system to m definition record that's where the definitions are and as usual i don't care about cis underscore collection it's just a fascination thing from what's already been built and check maybe there's already a relationship that you have that you need that's already been built cis underscore collection is a great place uh so is this another case for an active flag could be could be oh you know what else i noticed i forgot to mention um there is let's get to user a this one baffled me at first let's go to the groups for me again and i always like active flags i put them on all my tables that i create because i never know when i'm going to want to deactivate something down underneath the list collector if i say database atlanta it has a name here and sometimes you'll see two or three fields where does that come from some of you may know at least last i heard that came from the list so if i look at my sis user gr member list let's see if we can add there's not really anything to add there is there uh let's go back to my trivia maybe trivia has something better for me i could spell trivia the goddess of typing errors i think she is and i have games i don't have any games do i have any people i have no data in here so let's create some real fast first name chuck phone yeah whatever last name me email just being set bell my name right fingers on the right home home rows you all know how to reach me so that's not very uh do i have a display value i put in a display value very nice now let's go to the game suspect i need have a related listing here for games because i'm not in the right app i won't be able to do this quite right same as before take my many to many lists move it over let's go create a couple of games trivia games um star wars sure why not i don't think there's anything else that needs to be done in here let's do another one classical tunes sure why not and pick another one um folk tales you come up with your own trivia games would you in here we can also put our many to many related list and it's persons but i also have a related list of questions again i can have many different questions on many different tables so the data model for this was pretty well built out and if i say persons down here and i select that all i get is a name let's go to the studio pick my trivia app and underneath my where's my many-to-many attempt what was the name of that table let's pick that it looks like it's persons but i didn't add a person to that table there's only one person to pick i think i'm going to win this trivia game i'm the only one there and it is called trivia m2m game person do so one of these tables which is labeled a little crazy there's the game questions i can obviously figure that out i'm going to guess it's called attempt because i'm making an attempt just like the score table look there it is m2m game person this has a list layout and if i say show list ah it's not from the list layout i thought it was from the list layout here it is not so now sysref list is where john is telling me let's go see let's go see let's configure a view yeah we could do that let's take our attempt record our temp table and say design form the other place this ref list appears is when you choose a reference field so let's go in this record say open it up and i have person and if i select the magnifying glass it has just a name this would corroborate what john is telling me so if i i don't know why that's configure list layout maybe i want more than the name on this list this is a special view for reference fields the view is called sysunder ref under list let's also put in besides name which is the display value let's put in email and phone just in case i want to contact that person the view name is reference list i see down there i'm doing just fine i have a nice new reference list let's go see what the list collector does for me back on my games i have people to select if i highlight one of them there it is thank you john for making an old man's memory work again alternatively i mean similarly i also need to go and do that on the other table's reference list as well so i've got the people displaying three fields but i would also need to go over to the people record select the game get the reference field so if you need to modify something at the bottom of your reference list people are saying this isn't enough details for me you can do that you can dot walk you can get other information on here that's where this comes from how can an a table extend b table not at the time of table creation if you create a table and choose not to extend it you're going to have to recreate the table to make it extended there's there's not a way to go into cisdb object and say surprise you're now extending this can really wreak havoc because like the many to many relationships there's things that happen behind the scenes that tell the system about how that works so we are good to go that took a little longer than i expected but i think we covered all of the details on that let me uh just reiterate that many to many tables are a very powerful way to create and manage and maintain data easy to do with that list collector side by side as i mentioned some of my examples associating players and games or students and exams and anytime something can be related to related in many ways to something else think about that when you're creating your data model is this a one-to-many is this just going to be fixed or do i have the option to mix and match these and say we could have a multiple combination very important discussion to have when you're dealing with the data model which is one of the first things you ever create when you're building an application uh they also of course have that list collector to give a nice easy ui with the edit button pay attention to where that way and think about places where you might consume these that's it for today please i invite you to join me on thursday august 13th when i get started with service portal widget fundamentals we're going to build a custom widget very simple help you understand the server and the client and the html and a little bit about angularjs so very much looking forward to that it's been a wonderful journey over the last few years i've learned from a lot of people and i'm going to put this together this will be part one so there are many more service portal things coming up is it possible to have multiple sub query encoded query token in one query i'm not even sure what that means or an example but it sounds like a great question for the community if you want to reference it over there give some examples screenshots are always helpful for that till thursday put it on your calendar mondays and thursday 2 p.m utc you do the math for your time zone i will see you again real soon where's my correct buttons thanks take care bye [Music] you