foreign so so do [Music] now do do [Music] now [Music] good morning good afternoon good evening wherever you are or in some cases good night good late night for some of you wherever you are and whenever you are welcome to the community live stream my name is chuck tomasi from servicenow senior developer advocate been with the company for about 10 and a half years and uh customer for a couple years before that our topic today because this is november 23rd 2020. if i check my calendar right yes it's the 23rd 2020. our topic today is data lookup tables this is something that one of my previous bosses was just fanatical about it was his favorite feature in the platform it is great to see everybody on the youtube channel of course if you're on youtube or you're watching later go over to youtube.com user servicenow community subscribe like click that notification button you know what to do leave a comment if you've got a comment of course we always encourage deeper comments on off topic things that are not related to data lookup tables please put those in the service now community wow they got the uh the comments are just flying by today thank you to everybody who's joining live this is a lot more fun when it's done live and a lot more fun when all the technology is working so i am uh one computer short of a full setup in here that's not a metaphor for something it is we are working with what we've got today hope you enjoyed the new countdown screen i finally finished that widget and can put configurable backgrounds in the placement of that just for fun to give you a little lead in of what's happening and also the the intro music kind of like when you go to a conference and you're filing into the uh the main keynote stage and they've got that music playing so it allows you to get the notification a little earlier that hey this is live and you go into kind of a waiting room and then it also allows me to validate that the feed is working so if there's any technical issues we can work them out ahead of time so we look forward to that in the future thanks for bearing with me as we as we learn and we grow and we change that's youtube we also do this on twitch hopefully that's simulcasting over there i can't check that live but i am watching youtube live so thanks for joining me there you can find that on twitch if you've got any questions as i mentioned that go beyond data lookup tables please go into the community at community.servicenow that is well aside from an update that just decided to come up behind me that's what's behind me is the community at community.servicenow.com select mess up in my notes then i missed the step that said put the community on the screen right well you know that's what happens when you do it live and there's only one person in the production that's doing all this so again thanks for pairing with me and thanks for being here it's uh it certainly is a lot of good time community.servicenow.com also i want to invite you to go over to developer.servicenow.com you can find all kinds of good information if you are just getting started on the platform or you're an old pro like me there is something for you over there api references are there free personal developer instance free learning plans grow learn become a better developer blogs and videos and we are just making it better and better we have lots of ideas to continue to grow and improve in 2021 so thank you again for joining us wow smiley faces thank you chat it's uh good to be here i did a lot of live streaming yesterday with t morris my co-author on podcasting for dummies so i'm i'm picking up tips from the man as i go forward so hopefully you'll you'll see we're all learning and growing and having a good time so developer portal is where you want to go one of the things that you will find there as well is if i can get this up in time is the meetups that we are doing virtual meetups right now because of the current global situation and you can meet with other developers around the globe really without leaving your the comfort of your own easy chair or living room or bedroom or wherever you want to watch these meetups attend these virtually learn what other people are doing from sydney to vancouver to uh pune they're just they're all over the place it's a wonderful experience to be able to share this with other people hello from halifax nova scotia top gunner thanks for checking in one year newly minted csa congratulations a lot of people have been proudly showing their csa and their implementation specialists on linkedin i try to thank as many of them as i can congratulate that is wonderful thank you for being part of that communication part of that community thank you for uh all the the accolades that you got on there a lot of great people over there developer meetups more great people there quick reminder we do have breakpoint our developer podcast if you haven't listened to that we are releasing another episode this wednesday the 25th of november a discussion a community a conversation with tim woodruff one of our developers in the community on the slack channel does a wonderful job we also are following that up with a discussion with josh nerius former developer advocate and now in charge of a lot of things integration hub on the product management side so a lot of things in the pipeline for break point audio podcast only so no demos no power points no update sets and makes it easy to consume whether you're driving to work you still do that kind of thing or you're cutting the grass or you're trying to rock the baby to sleep at night whatever you wherever you listen to your podcast you can find it at wherever you find your podcast amazon google apple spotify you know where it is just look for break point it's going to be the green one without the tennis ball because breakpoint is also a tennis term unfortunately you kind of get the idea we're making our way through that oh what else do we have to tell you about upcoming tech now webinar bitly slash t on 82 reg had a message this morning somebody said hey i'm having problems with the registration page if you have problems with the registration page it is 99.9 likely that it is an ad blocker that happens to me that happens to a lot of people so check your browser settings temporarily disable your ad blocker for that page because it will come up with mandatory fields but nothing in the drop down or maybe somebody else somebody else's settings it'll just behave strangely like you would not expect turn off your ad blocker for that page guarantee you there's nothing nefarious going to happen to you there bitly slash 82 reg this is bitly slash tn 82 reg december 15th i want you there sign up now learn all about instance data replication the easiest way i know to move and synchronize massive amounts of data between two instances without cloning craig's step will tell you what it's good for what's not good for how to set it up all that kind of stuff even some of the down and dirty little tricks so thank you drew for the endorsement on the podcast i'm gonna move on there's no code today so we won't be talking about our github repo well actually quickly there have been scripts that have been written in previous episodes so if you watched community live stream in the past you can find all of our code snippets there but we will not be doing any of that today if you want to grow a little bit more and learn a little bit more about scripting javascript on the now platform you can go watch the bit the uh the web series put together a little while ago thank everybody for your recommendations on that i'm really eager to get the next series started published really soon really soon it's coming it's coming it's it's close i'll have to put up a new slide for that i don't even have anything ready for that bitly slash sn-learn js is where you can find that okay let's get on with our topic du jour that's french for topic of the day i think [Laughter] martin also says yeah the registration page is also weird the formatting goes away and i have a bit of a green screen thing going on that was weird okay i think my black clipping is just a little off so you may see some artifacts around me has to do with the darker shirt and the camera settings and we won't get into that right now okay topic today is data lookup tables what are they what are they good for well there are times when you want to do an automatic lookup for something like an assignment or you may have seen this on the priority where you set the impact in the urgency and magically the priority is set even though it's a read-only field how is that done it's not done via script that's the way the priority lookup was originally done actually the original one when i started with this customer back in 2008 was hard-coded it was literally a script that said you know if impact is this an urgency is that then we'll set the priority to that that is the brute force way of doing a lot of things and i don't encourage you to do that because somebody is going to say you know what we need to change it not impact one and priority two or impact two and priority three uh urgency three the rules change people change their mind you need a quick and easy way to modify this as data data driven design i've said this plenty of times before it's going to your best way to increase the velocity of your development and decrease the maintenance you do on any given implementation configuration customization application in the future so data driven design always keep that in mind you will be farther ahead two years from now trust me on that says the man of nearly 40 years of development experience and making plenty of mistakes along the way so if you just want something like incident priority based on impact emergency or assign a group or assign a person based on a category or subcategory this is a great way to do it data lookup tables make this extremely easy to build and maintain okay that's what we're looking for is developers fast fast fast easy easy easy less work later right so let me give you a quick example of what that looks like you may have seen this before but if you haven't if you're new to the platform this is how it's done i'm going to go to the incident table and i will take a look at some open incidents see if my cursor pointer is still working yes it is okay now i can point around the screen and i think my little double tap zoom is going to work as well right now i've got over here click click there we go i've got impact one urgency one priority one and if i change this to medium it's automatically flipping that priority even though it's a read-only field it's doing something in the background this is not scripted i mean there may be script somewhere that the system is running that i don't care about but all of this is configurable if a and b then set c okay or even just a single field if a then b okay easy to do let's go see how that's done well step one is over on if you do data lookup we have data lookup definitions yes i changed something i want to leave let's look at this priority lookup right here in the center and i did click it did i not let's try it again waiting waiting waiting take a sip of tea here we are okay this is the source table let's take a closer look at this i've got a name just a quick way to reference this from a list i tell it what the source table is where is the data coming from that's going to tell me where my fields are being set the matcher table i'm going to define this this is critical because this isn't just something that is on the system already you need to create this if you're creating a new lookup table so we'll get to that in a second over on this side we have some various settings yes it's active do you want it to run or not that's basically what that says do you want it to run dynamically when the form changes is a client-side component involved with this to watch for changes and update dynamically or are you just going to if the fields aren't on the form then you don't need this right if priority didn't display and it was just something in the background you could leave this off and save yourself a little bit of processing run on insert do you want to run this for new records yes we do for impact urgency priority situation but you may not necessarily need to you may not necessarily want to maybe fields aren't applicable or are set at this point so just like a business rule determine when you want this to run new records and or existing records let's look down here this is where the the real fun begins we have our matcher set definitions i am going to define what my source table field or fields are in this case impacted urgency and the matcher tables now they look like they're exactly the same they are two different fields on two different tables they don't have to have the same names in this case they do i often do as well it makes it easier to debug these things but your matcher table could apply to something else then we have exact match lookup i'll get to this in just a second i'm going to flip over to the other this is what needs to match this is what says when impact is won and urgency is won aha i've got a match what do you want to do with that we go to the setter field definitions and we say we're going to set the priority okay watch the impact and urgency set the priority matcher and center that's what they're called this is the definition of our data lookup data lookup definition makes sense when we have that we are going to then use this is all under system policy the ones that are out of the box are here by default assignment lookup rules and priority lookup rules they're not all there by default when you make a new one this is where we define the rules that's why it's called priority lookup rules somebody had to name this table this contains an order in case you need priority of which way you're looking up something if there's a conflict or a possible uh duplication of rules which one comes first we have our setters getters excuse me our matchers sorry i'm trying to try to do this on live is always fun we have our matcher if impact is one and urgency is one then priority is one it kind of goes down the list this way if it doesn't match nothing happens simple as that that's our example we are going to replicate this or create a new example for one of our favorite examples that is not i.t related because we may have people that are doing hr or legal or something else on the system so i don't want to tie everything to it makes a nice example to discuss but for the demo i'm going to use my course table which is over here on my menu using the wrong pointer device again always happens and first thing i need to do the the rules of my engagement are going to be this i'm going to assign an instructor based on the track okay so i've got history management i've got liberal arts i've got math i've got science so different tracks different instructors okay you come up with your own rules this is my example i want to go in here and apparently i made the category dependent yes okay that's a different video for another time we're going we're going to get that one on the next video how to set this thing dynamically but uh history management has some different things going on we're going to set it the instructor based on management so right now it's not doing anything you can see i changed from history to nothing's happening here let's go make a data lookup table so step one i'm going to do this in studio you can choose to go to system definition tables create a new table that way but since this is a scoped app i'm going to do it in studio to keep everything together that allows me to develop in one one browser tab and test in another browser tab so let's go get our favorite cls 323 app which has a whole bunch of battle scars from previous videos and here's my course table don't need to pull this up for sake of exercise i'm just doing it to show you that it's there everybody's happy i'm going to start by creating a new table so create application up here in the upper left table click click and i will call this course instructor let's just call it yeah course instructor that's always the hard part isn't it naming your tables and fields definition it's not the lookup rules it's the definition i'm going to call it lookup definition lookup definition i'm probably going to run out of characters real soon here 40 is the limit and that's going to create a horribly long table name so i'm just going to call this instructor lookup instructor lookup name your tables once you can only name them once you can label them anytime you want if you have two or more lookup values which evaluate as true with different orders which one will be applied the one that matches first that's what the order is for does it stop once the first lookup is found yes or will apply the last no we'll do the first maybe we'll come up with a uh an example for that don't need a mobile module i'm going to create a new module under my cls menu and here's the important part i'm going to flip to the docs page for this here's the important part your table must extend dl matcher let me say that again dl matcher data lookup matcher we're extending some functionality that already exists in platform so dl matcher there it is data lookup matcher rules that's what's going to give us the ability to do the related list full of setters and matchers and all that fun stuff and save it goes and creates those extended fields for me with the source table all the good stuff interesting i've got three societies in here i'm curious to see what kind of tables those come from let's just take a peek because this is experimental let's go to table put that on the personalized list layout and find out why we have three cis ieds like there was some kind of nuclear accident that caused that did i hit cancel that was odd or am i just being a little slow this morning okay there we go table table table sys id application ones from sys meta ones from dl matcher and one's from my course table interesting okay fun that's step one step two is go and create a lookup table now over here we need to create our rules so i am going to create create create the lookup table create table same kind of situation this is going to be my instructor course instructor rules name or whatever cls instructor rules i don't like really long table names but i do need them to be somewhat descriptive this one does not have to extend anything this is a standalone table and in here i'm going to have my what did i say track which is going to be a choice list and my instructor is going to be a reference to you got it good old sis user let's scroll that up so we can actually see what's going on cis user user hey that wasn't very nice let's do it the other way type user hit the down arrow twice go uh if you miss extending the table can you do it later the answer is no you do need to do that when you create a table and if you do extend and you want to later not extend same kind of thing you got to stop and recreate the table because there's all kinds of things that happen behind the scenes at the database level excellent question though you know i am i'm getting ever closer to creating a new sub series of community live stream called back to basics that will get some of these really early things i know some of this is covered in system fundamentals course and other places so i've got to navigate those waters so i'm not giving away intellectual property but occasionally we need reminding of of the way the system behaves and how we can do the easy stuff first okay let's go with our lookup table defined let's go and refresh this reload reload i'm going to go over to um look up and of course instructor lookup definition did i not create a module i probably did and i don't like where it went darn it you probably created a new one instructor course instructor rules yes studio likes to it gets menu happy on me so let's go in here i'm going to put this under the other one which was system policy i think [Laughter] let's find out application menu name system policy that way all of my rules are together system that was a bit verbose policy is my application menu i don't really care what order it goes in right now it also created a new role why you would need a new role for every table i have yet to figure out and save that then we can update this come on i want to finish saving we don't need a menu that has no modules especially one that shouldn't have been created in the first place i'm just doing a little cleanup at this point and theoretically if i go back under look up oh it didn't put it in there fine let's do it the other way if you can't find your table you can always go here and say show list okay here's my instructors here's my tracks i don't have an order on this interesting let's find out what happens something in the back of my head says i did this a little backwards okay what i want is the table name wow i'm all over the map on this one today tables of course look up definition course instructor rules give me the table name i'm going to do it over here paste dot list new i don't like creating new records in studio it just feels wrong now my track has no choices what i can do and i think i showed this to you another time is directly connect it to use the choices on the course table so configure dictionary all right here right click on that label configure dictionary and rather than duplicating the choices and saying here's three choices or five choices it gets really monotonous and difficult to maintain if you have the same choice list in multiple places what i can do is click advanced view down here and if you haven't seen this before it's a really great way of borrowing those choices from one table and field to the next that way if i change them on the course track choice list it will change here as well set the table to my course table course and it says here's the choice fields that are on there which one would you like i would like to borrow the track field track and field and now ta-da i have those so if we have history we will get fred luddy as an instructor seems like he's been around a while if we're using if we're doing servicenow history save if we're doing liberal arts well let's get don goodliff say insert and stay don't hit save management that could be beau riggieri insert and stay i'm just creating some lookup data here mathematics would be david liu dr david liu insert and stay and i think we have one more science i'll take science for 100 alex poor alex trebek passed away recently insert and we've got our lookup rules let's go to that definition so lookup brings us to data lookup definitions i don't know why i don't see my wonderful lookup definition here but let's create a new one we are going to do a data lookup rule you can also do catalog data lookup rules this was not originally there i don't know what released but it's been fairly recent in the last four years that's recent to me that catalog data lookups have shown up thank goodness so our name is going to be course instructor lookup the source table is our course table the source is of course of course of course the matcher table is going to be our lookup definition save match your field definitions let's create a new one that says match the track to the track i don't have a track on here you know why because i did this a little funny i knew something in the back of my head was off okay let's save that i know it says active but we're going to save that and setter field definitions is probably not going to work as well set the instructor to the instructor you know why because in my table definition that one that was the extended from the data matcher lookup thing which one was it extends table data manager look overall i need the instructor and the track fields here not on that other table uh i did that wrong i knew that felt funny track is a choice we learned by our mistakes you're learning by my mistakes it's half it's twice as fast this way okay instructor it's the really the docs page is correct i just haven't read it today instructor is reference this is on the table that extended dl matcher and that is going to be as i mentioned user down down go yep create fields let's do that now what we need is that track field again let's link that to the course track field do that very easily in table definition because the field is right there we don't need to go to the forum and look it up so oop went right by it course and track that way i get the right choices go back to wait for it wait for it there we are go back to our definition record i'm going to discard that okay on our data lookup definition i'm going to set active run on form change run on insert run on update save that as well make sure they're all checked for my example anyway okay track is going to match rack and when i set instructor is going to match instructor there they are didn't see him it's because he didn't put him on the look up definition always replace i didn't get to mention what always replace is it says if true a matching lookup will update the source field when even if the matching field is blank okay so if we get a blank it will try and update i'm not sure what it updates with maybe we'll go to an experiment with that as well that's how we learn we experiment now that i've got that i got my source table i've got my matcher table where does it tie to the other table let's go to the course instructor data look up those rules how did this get tied to that so priority data lookup rules let me look at the other one i already did a lookup rules i'm using this as an example this is priority data lookup which is the dl lookup and it says i want to match that to that but somehow it's got to tie this in back to the documentation okay because where does it define the priority data lookup rules list the combinations ah each data lookup is a query that searches for row containing values that matches the matcher fields the data lookup then returns the value list of the center fields for example this priority data lookup table lists the combinations of impact and urgency that's the dlu priority you know what i may have missed something let's go back and look at i don't know where i'm going now not in the community let's go back and look at this table definition i'll bet i defined my lookup table incorrectly figure table on this one yeah this extends data lookup matcher rules i had a standalone table that did not so my second table that i created was wrong it needs to extend data look up matcher so i've got can we do that the first time dl matcher let's look at that that's dl matcher you have three sis ids here i do that's my first table okay so my first table is like this first table where ah i got it i got it i got it i got it i got it i got it i got it okay i didn't maybe i don't need the second table that's probably where i'm getting confused course table we're just a little off course get it okay let's take this table because it doesn't have any records in it right now does it i did all of my fred luddy and other stuff for no reason i don't need that second table is what i'm thinking right now sure enough look it's got the order it's got the active these are the fields that i didn't create so just for fun i know i might regret this at some point the course instructor rules delete elite delete tables very carefully children that is not the ins extended table you can do this one table is what we're thinking go create the dl match your extended table put data in it define the rules on the data lookup definition let's do this again just for fun instructor fred he was the guy in charge of history sorry for that sidetrack we didn't need the second table apparently let's keep going instructor david david was in charge of mathematics i know i'm going out of order i'm going in whatever name comes to mind we had don goodliff john was liberal arts i don't remember what he was but that's what he is now we have bo bo was in charge of er management make sure we have one of each yep and i think i got science so chuck is in charge of science i'm going to go through this again just to because it was really kind of insanely sideways apologize for that okay what we did is we went into studio created a new table extended it from dl matcher that's the critical part put two fields on there for our lookup and setter values we want to look up on track we want to set instructor create that table then back under data lookup rules the definitions create a new definition this is not the table i just created this is a system table that says how do you want me to work define my course instructor lookup which says here is my source table i'm going to use the matcher table that i just created set the matcher and setter definitions from the source table to the mattress the to the matcher table create the center definition source table matcher table then go back and look in the table i created to set those rules if a then b if a and b then c that kind of thing same way that it works okay one table i only needed to create one table so now that we're all caught up mentally you think it'll work let's go try it i need my mouse to be a little more responsive here it's getting stuck go to the course table as if another enough things weren't stuck today and i go to advanced blork in the history track let's see if wow this mouse is being really there liberal arts don goodlef that's pretty cool management it did not update okay you know what something is off why doesn't it update if i say science and save it should say categories mandatory boogers okay save it should have saved with chuck something is not right on these definitions uh they should change because we saw priority changed let's look at the that table's gone we can delete that module we have a left over here it didn't find it when i deleted the table it deleted the module but it didn't refresh the menu okay so not everything works for chuck on the first time of course let's go to look up would it be something in my definition course lookup definition how does this compare with let's see false false update insert run on form change i want to compare it against the definitions for say the assignment lookup assignment uses false false on two groups on the setter a whole bunch of things on the exact match lookup is all false run run run looks the same not running the same what have we missed because it's not running dynamically course instructor lookup look up the track match to track exact lookup false we have our rules always replace false oh change that to true that's what it was so back to the course table i'm going to use my recency factor my time machine go to course table always replace is the e to making this work dynamically like that see how that goes says i don't care what's in there if you have a value i'm changing it if you have a value and it's set and you don't want to mess with it anymore because we started with a blank value and it said okay i'm only going to replace it if it's blank so always replace says you got a value there if you set me to false i'm not going to replace it if you've already got a value i'm going to trust that i could have come in with existing records i said this course is already assigned to don goodliff just even if i change the track don't reassign it now priority because it's read only you you kind of have to fix that one you can't change it manually so that one is going to be the same so watch that always replace it caught me there it will catch you too i guarantee it so let's go to asian history save that fred is in charge of asian history that's working now the key to all of this is oh look there's my step-by-step instructions that would have saved me a whole lot of things in my notes you can use data lookup tables to create these ups and sets very quickly probably much quicker than i did because you don't need that second table that i accidentally created and the the catch is excuse me the catch is the setters and the matchers must be on the same table you can't do any dot walking to go down and look up other fields if you look at these that are already in the system data lookup definitions assignment look right the matchers subcategory cmdbci location category those are all part of the incident table of course and the matchers or the setters assignment group assigned to also part of the incident table so be aware that if you need to do dot walking i've got another video for that we will look at the a different way to do that and it will involve some script so this is a great example an easy way to set up in record same record for your setters and your matchers on the same record on the same table how to do that without any script did you see me writing code today did not write any code today so if you get confused use the out of the box examples and refer to the documentation pages like i should have right away so that is going to take care of that join me monday the 3rd let's go back to that join me monday december 3rd for glide ajax there's a lot there and i know a lot of people get stuck on this it's why do you want to use this versus g form get reference versus g scratch pad there's a whole bunch of different ways glide record various ways you can get data from the client script to say hey server i need some data okay we're going to show that to you explain all that information glide ajax probably one of the most common asked questions on the community is around glide ajax so we're going to share that with you so anytime somebody comes up to that you can point them to the video that i'm going we're going to work on on monday so can we still override the instructor even after it is auto set yes that's what um the always replace does for you always replace says always replace i don't care what's there right now always replace good question pretty thank you for that and with that i'm going to sign off and i will see you again monday hopefully my computer will be back there's no show thursday i should have mentioned that it is a holiday here in the u.s thursday is thanksgiving holiday which is why we have to wait a whole week to get the next episode so i'll see you again for glide ajax real soon take care everybody be safe learn something share something be on the community i'll talk to you later [Music] bye [Music] you you