[Music] thank you okay we are a couple minutes over so we're gonna go ahead and get started uh sorry that we started a little bit later on our intro video I apologize for that but we still have people that are coming in so that's awesome so we will go ahead and get started so welcome to the platform analytics Academy for October 4th I'm glad that you're here if this is your first time welcome if you're returning uh welcome as well hopefully you all will get something out of this uh let me make sure that I can move forward here there we go so uh as usual go through some housekeeping things and then we'll get directly to our content but this is for you we try as hard as we can to bring fresh ideas better understanding and practical guidance to uh platform analytics reporting performance analytics the platform in general as much as we possibly can and today we're going to try to do that as well this is being recorded it will be out on the community later on this afternoon or early tomorrow morning uh as well we'll be out on our YouTube page our YouTube channel as well so look for that there also the I I will take this slide deck and convert it to a PDF and I'll attach that to the community again as well so if this is information that that you need and you want to share within your organization you're definitely more than welcome to do that as well please ask questions if you could definitely utilize the Q a um that way we can actually get those questions and um from Zoom after the call and uh we'll have those on record and we we try to take those questions and look through those as much as we can to to think about different things different sessions and future things as well for a servicenow as well and also I have panelists that are here with me that will try to answer those questions uh as they come in if they can via tight if they can't and it needs to be live we'll definitely take those questions so we're covering a pretty broad spectrum of information today so the questions kind of may be all over the place and that's okay but if it is a question that is unrelated to the content that we're covering today then we'll try to get to those um at the end unless like I said one of the panelists that are here with me can take care of that question via type or something that but please feel free to ask questions and it looks like they're already coming in which is awesome uh but if you could uh try not to put those in the chat and put them in the Q a we'll definitely uh appreciate that so Safe Harbor um try to always make sure that we put this here I don't necessarily know that there's any content today that um falls into this but I think that there could be some conversations and uh different information than it that may even be shared by the panelists That Could Fall underneath this so we just want to make sure that we share it and um have you keep it in mind so my name is Thomas Davis and I am the host and presenter today and I know that I have I let me check is everybody here everybody is here so Dan Kane is here Adam Stout is here Tara Fisher is here and we also have RJ uh who is on our inbound side that is uh he's kind of in transit so he may be difficult for him to answer some questions uh but he'll try if he can but he's here anyway but all of these panelists are here to answer questions and make this content uh better for you so with that let's go ahead and get into what we're going to be talking about today so today we wanted to talk about um like I said a few different things but it's really around mastering data relationships and all the possibilities that are available within or some of the possibilities that are are inside of the platform that gives you that ability to do that some of these things you may be familiar with some of them you may not uh some of them you may find elementary and some of them you may not but these are uh in our mind really good things that we wanted to make sure that we get on video and talk about that way um if someone needs that obviously later on down the road or if there's something that you want to pass through to your organization that you would have a source to do that with So today we're going to talk like I said about dot walking related list conditions remote tables and and some database views uh stuff as well so with that let's go ahead and get into it so obviously the agenda.walking related list conditions remote data uh well actually that should be uh remote tables and then database views so the first thing we want to talk about is dot walking so dot walking is something that is probably one of those things I know for me when I first got into the platform as a former customer is one of those things that I earned I learned very very early is the idea of being able to get to uh information that is referenced from somewhere else and how to actually be able to do that so um it's one of those things that gives you the ability to Traverse different tables and all of those things and actually pull that information together to not only view it but to also maybe even set some filters on it uh some sets some some filters in uh different places like an indicator Source or a breakdown Source or inside as you're building a report there's a lot of different places directly on tables and all those different things that you can actually do with with uh dot walking so let's talk about um notation and what notification means and that's basically where you'll actually the idea of a DOT which is just basically in between two different uh points of information that again are referenced somewhere and they can actually talk to one of those but let's talk about the different ways that you can actually do that so the first is table hierarchy so the data is organized obviously into tables and there and these tables often have relationships with one another so for example you might have incident table that is related to a user table through a reference field such as the assign to so that is where that reference comes into so having the ability to talk have tables talk and and use that information inside of reporting and other needs is is very powerful so accessing related data so this is the ability to use that walking notation and it involves chaining together table and field names with periods down like I said to indicate the path of the desired data that you have so for for instance incident.assign2.name is that notation starts with incident table follows the relationship to the user and then through that to the assign to reference field and retrieves the name from a name field from the related list user so again tying that information and being able to pull some information back that you want so dot levels right dot levels can be multiple levels deep you can chain together several dots to Traverse through multiple related tables and an example of that is incident maybe incident to Department to manager to email so here you have an incident and you want to get the department and then the manager email you can actually Traverse all of that to get to the manager record to find out what their email is and then also the user or the department that they're in so again you know the levels can go many levels deep that it necessarily mean that it's always a great idea but you can definitely go multiple levels when you're actually looking for this information so next is filtering query and I talked about this a little bit earlier so you can use dot walking in queries and filters to specify the conditions based on related data so for example if you wanted you know incident.assign2.department and you want it to equal HR then you could look at incidents where the assigned tools department is HR so it gives you ability to filter down that data and and that's a really good thing because maybe you have a thousand records a thousand incidents and you want to actually you know break that down and say I don't want to look at a thousand at one time well maybe doing something like that obviously would trim that information down uh quite a bit so filtering and querying is definitely something that you can take advantage of that walking in that so aggregate functions this is another way that dot walking can also be used to aggregate functions like count and sum and average Etc to perform calculations on related related data so I saw that a question came in and it is how deep can dot levels be now I've not found a number so Adam uh Dan or Tara or even RJ I've not found a number but I think that it's you need to be careful with it that's what I'll say right yeah you need to be careful with how many you do because you got to think about that's processing on the system of how far you're going to go down and it could you know yeah you was going to say something yeah I mean it depends is the real answer um I think the old rule the old uh rule of thumb is three but we certainly go beyond three um but understand when you're dot walking what it's doing is joining the tables in the background now uh this is It's optimized for DOT walking so by default every reference field you can only dot walk on reference fields in every reference fields indexed in the database so we we are doing indexes but we're joining so if you're doing three fine it should be fine if it's you're doing four probably okay too but when you start um querying or uh putting your wear condition on that well now it's got to do the join and do the filter if you're just referencing it it's how fast the database can optimize it but understand it is joining it and if you in a situation where you have really large tables and you're dot walking four five six seven levels um what you might want to do is start looking at at some flows or how do you structure the data so that that data gets copied down so that you can shortcut some of those or we might use a database View which we'll get to later about shortcutting that but you definitely want to start with just simple dot walking and then tune if needed don't over engineer or or avoid it um unless you run into a performance problem and most of the time we're not going to and if we have a thousand records or 10 000 records that are coming back it's fine like by looking at active incidents and I need to dot walk to the callers departments manager I I'm fine just do that um but do understand in the background what is happening is it's just going to basically dynamically create those joins for us on the Fly um and they're all uh inner well the left left joins to come through I believe unless somebody's going to correct me um but that's what's happening in the background it depends um to go through don't be scared of it but when you once in all the uis if you're dot walking five six seven ten levels I just want to understand my data model as to why I'm doing that um and it becomes it starts to be hard to it starts to be hard to read and understand in all the uis when you have that much dialogue so let's see if we can grab these two questions here real quick so are there limits to the amount of fields available in dot walking and or is there a limit to the types of fields that can be done walked like created by for an example so it really comes down to whether or not there's a reference somewhere else for a particular field on what you can actually dot walk to so so created by is a special one I'm glad I'm glad that that got asked by uh Mr Anonymous or Miss Anonymous um so created by is a string because it comes it's a string because in theory you could delete a user or a record could come from some could come from someplace else so it's a string of the username even if that user doesn't exist um when we talk about data maybe we'll come back to database views there's ways that we can we can join assist user we can we can force it effectively uh to come through and there's other places we do this in in reporting um I think the CIS report Table does this where we used to only have created by but we wanted to know some more attributes like what department did did the creator come from and what we would do is we would add a field to the table that was um Creator or something like that you we wouldn't call it specifically created by but it'd be something very derivative of that and then put a a uh a flow on it or or a business rule that would say Hey whenever created by gets updated or updated by it gets updated set this field which is a reference the the limits is it must be a reference as Thomas said created by is a string which would be nice it was a reference but there's some reasons why it's not and it's not it's not going to change but you can you can make it right you can make what would have been all right keep the string but add a new field as a reference and then you get all the benefits now the the next question is uh around efficiency so you know uh which which direction should someone go should it be incident to assignment group or use your table to incident record and to determine the fastest route uh to the user's email so um I guess I guess what I what I would say and please if I say it wrong I would I would my question is where are you starting from so if you're looking at incident records and you're trying to provide some sort of reporting on incident records that's obviously where you're starting I don't know that I would I guess you could I don't know that I would start at the sys user report to then generate incidents I mean I guess you could do that direction but I think it comes down to which direction that you're coming from I don't know that there is there any efficiency that that you're aware of Adam depending on which direction that you want to go it's logic you always have to start the lowest level so if I want the incident assignment uh assignment group to the user yeah well the other and the other issue is you can't go um you you can only you can only Dot Lock is forward it's from the lowest going up so the incidence you can get the incident you can get the group but you couldn't get the list of the members in the group that wrote the incident uh that's when we come into the database we use which we'll talk about when we have M to M's um if we do a single M to M we actually start on the m to M table it gets a little weird if I wanted to do incidents to problems and I have an m to M I know uh by default we do um incidents have problems the problem field isn't a record on incident but you can also do it where um uh you can create an m to M where many problems can have many incidents in many incidents get many problems if I'm reporting on that I'd actually start on that M to M table and dot walk up both sides but there is it's all logic bound not efficiency um will do as much as we can in the efficiency to come through there are cases where I'm just doing too much and I need to I do need to create a shortcut or I need to do something with the database view it's very rare um I mean I I I might have done it but I I've been working with service now for 10 years and it's I can't think of when I actually did do something like that just dot walk um that's I think the most important thing we get from this is you just dot walk and get it to work if it's too slow then we'll talk about it but generally the vast majority of the time it's going to be performance will be fine okay great thank you for the sake of time I'm going to kind of keep going forward so I don't maybe some panelists can answer that question those other questions via type but if not we'll get to those um here shortly so uh the next thing I want to do is and I don't necessarily have to read all these but these are some just some general use cases that uh that I was able to come up with with maybe when you'll actually you know some ideas of when you can use some uh some dot walking so um and I'll just go over a couple of these I'll make sure that all of these are listed out in the uh the PDF that I'll attach to the community later so this is in the incident management process you can dot walk can't you can cut you can use double display information about design user their Department contact details on the incident form for example incident.assign2.name which we talked about a little bit earlier um and then maybe on change management you uh when working with change management dot walking can help you show the details of the change requester including their contact information so again it's very similar it's about you know when you're you're at the incident or the change or asset problem wherever any of those things that has an assigned to user or something of that relation there you can definitely uh get to in-depth walk to All That different information so again I won't necessarily go through all of these but there's some different ideas of when you can use um doubt walking and we'll have uh the next slide actually show some demo of different things but I'll make sure that these are all listed out of different things for you to think about these may be things that you're already doing uh things that you definitely have thought about but there are some different things that you can do um in in a wide variety of the platform not necessarily instant I know quite often on these calls we we do talk about incident uh a lot but that walking can obviously be used in multiple places within the uh the platform itself so uh like I said a couple different ideas or some different of things and and uh you can look at those later in that PDF so so in this so our use case is you need to show the assigned to manager and their email for instance that are in a progress state or in are in progress as their state so here anywhere that you see those pluses in any one of that information that kind of lets you know that there is uh not kind of but actually lets you know you have the ability to sort of dot walk and you can see there that actually the assign to dot manager there's already a dot walk that we did and we were able to move that over to the report and then I grab the email and you can see here I've highlighted it that those two now because of dot walking has given me the ability to add that information to a report and obviously I already set the state as in progress and when I run that then now I'll actually get some incidents back that have the assign to and the manager in their particular email this is something that's very uh probably very Elementary a lot of people have done this but this is what dot walking is and what it uh looks like so the next example is uh can you create a report that shows all incense where the assigned to manager is Fred Luddy so here we're going to do that directly uh within the condition so we're going to look at assign to and then we're going to look where manage and then we're going to actually put manager is and select Fred Luddy so here we have the dot walk and we'll put his name in and basically you just filtered down the results that you have again going from maybe 10 000 records to a couple whatever you have so again got walking directly right inside of the filter and the way that we can verify this is we can just basically go there grab the same manager drag that over and display it and then we'll know that our filter condition that we put in via DOT walking is true because now we can see it inside of our results so again got walking very similar not only can you do it in the report you can do it in the filter uh as well and this gives us the ability to show some dot walking at the table level so if you have a table and you go down to show related fields at the table level of in whatever table that you're at you can see here now with uh that Arrow that's beside it that you can go deeper into information basically that's referenced inside of this and you have the ability to filter that information and then you can filter the incidents down directly there so again we've went to assigned tools manager is Fred Luddy and We've ran that and now all the only incidents that we'll get back is where the assigned to manager uh equals Fred lady so there there was uh some examples of how you can do that at the table level you can do it uh in the filter of a report and you can do that um definitely and directly in the report as well and the results that you have listen this is not all the places that you can do this there's many places that you can do uh this that you can absolutely do it in uh performance analytics at an indicator Source if you want to filter information down there you can definitely do it there as well you can do it in scripting so here you can see if you chose to do a script and you needed a dot walk into it you can see here right there so open by dot manager is letting you know who it was opened by and their manager a couple different places here where dot walking has just been hard coded into it because it knows the notation the platform does and the scripting does that it knows that if you have uh incident dot caller ID you know what you're looking for or incident you know to display that a couple different places again that you can do that directly inside of scripting as well so dot walking although um maybe very Elementary as in just the maturity of the platform itself is very powerful in the places that you can actually use that so uh I I would definitely talk about some best practices when you're coming to this which is kind of like some things that we talked about a little bit ago with the the questions but make sure that you understand the the level right avoid excessive dot walking levels uh each dot does represent a relationship between tables and going too deep can impact performance make sure that you strive to keep God walking to a minimum ideally no more than three as Adam said three or four levels deep um if your your query requires more depth consider alternate methods like Glide record queries or and as Adam was talking about database views and things of that sort so um pay attention to the level of your dot levels that you had uh indexing Adam did talk about that as well so ensure the fields that are frequently accessed uh through dot walking or index indexing Fields allow for faster retrieval and servicenow often provides default indexes that are commonly used for Fields so probably a lot of what you're dot walking hopefully it's probably already been indexed but make sure that you if you're not an admin talk to one of your admins to look and see if especially if it's a field that you're commonly using without walking um scope applications so if you're working with scope applications or custom tables do be aware of table hierarchy and relationships specific to that application understanding the relationships will definitely help you when you use that walking to make sure it's more efficient and avoid unnecessary complexity um so understand when you're in scoped apps data security so always consider you know when using consider security when you're using dot walking ensure the users have appropriate permissions to access tables and Fields involved in dot walking this is critical to maintain data confidentiality and integrity so know you know if there's certain Fields obviously the security needs to be on make sure that you're doing that definitely and then performance testing so before implementing a dot walk um into production environment maybe you want to take a look at it and and put it through some you know thorough performance testing accessing the impact of that walking queries on system performance and especially in scenarios where a large volume of records are involved adjust your design to optimize the performance and I think again that goes back to what Adam was saying you know if you have multiple levels of dot levels obviously more than three or four find out what the efficiency of that and what the performance is of that because everybody almost everybody has an attention that span is that's pretty low SO waiting for a report to to load when there's you know it comes down to too many levels of diet walking people are definitely going to let you know so make sure that you're aware of that so you know and and following you know best practices definitely will give you the ability to harness the power of dot walking in service now and again while maintaining you know system performance and data security and streamline user experience so before I go to related list conditions or any that we need to answer out loud all right so next I want to talk about related list conditions uh again another very powerful feature and um being able to add another level of conditions to a report or to a breakdown Source or to an indicator source is uh very powerful and I want to make sure that we talk about that and they can be particularly valuable in scenarios where you want to focus on relevant data and provide users a more tailored view of information so with that so let's talk about some quick benefits of use utilizing related list conditions so precision and relevance and Reporting so related list conditions allow you to construct reports with high accuracy and concentration on relevant data uh it ensures the reports are more meaningful and pertinent to the intended audience which in turn facilitates better decision making so again being able to take a report or something of that nature that maybe has a high volume of results and using related list conditions to trim that down even more not only are people going to be happy about it but it's just it's just a better idea when it comes to actually what you're presenting to the audience so anytime that you can use a related list condition to bring down the amount of results and make the report even better and that concept definitely make sure that you take advantage of that so enhanced report performance so optimizing data processing right so by concentrating on the necessary data and related lists the system can generate reports faster and reduce the burden of resources and enable quicker access to information and enhance the user satisfaction which I kind of talked about a little bit before um increase user adoption and productivity so more concentrated and relevant information produces reports that are easier to comprehend increasing user adoption and encouraging more informed decision making process this helps the focus also allows users to spend less time on creating and modifying and interpreting reports and leading to higher productivity so if you create something that gives them that ability right from the beginning there's no editing that they have to do or anything like that because it is definitely given them what they need customization and flexibility so conditions uh allow the crafting of customized reports to cater to specific organizations or organizational or department needs uh they provide Dynamic reporting capabilities that can easily be modified to adapt to varying requirements and this is without extensive alterations to the report configuration because you're doing it directly inside of the report inside of that related list condition and then better compliance and security so controlling related list conditions in reports um and based that on predetermined conditions again ensures the protection of sensitive data and allows access only to authorized users which in turn you know helps in maintaining compliance within your organization and anything that you have of that nature so I think there's great incredibly great benefits to it it's just putting your mindset into some of these things these aren't all the benefits but these are the ones that came up with this to give you an idea of what you can do with related list conditions if you you decide to utilize those so again here are a couple different ways that you could um use you know related list conditions so in this case for an hour organization managing a large number of incidents creating a report that shows incidents related to business applications um show business applications with Associated incident that is critical and priority now I will say also that we have gotten a lot better with the data that is definitely accessible directly at the table level and I think the the more related list conditions that are created and that information is passed on to our development team they do add those into it and what I'm talking about so if you're looking at an incident report now I believe that you can get the business application directly inside of that um report it'll actually bring those uh back if you add that when you configure the rows you can add and you can bring that back you can actually filter directly on that just some different ways and again the more that we do of these um the developers find out and they add of these direct relational things directly into it so again these are a few different ways that you can use it again I'll make sure that I list these out for your consideration inside of the PDF when we get there um but again related list conditions are are very powerful and what they can do so let's show a couple of uh demos of what we can do so here's an example of change management reporting so report is needed to show any change requests that have not been assigned any tasks any change tasks so here we're just going to create the report and we're going to point to the change request table and I'm doing that here and then when we get there then on my filter I'm just going to I'm going to go ahead and say activist truce and then in my related list conditions I'm going to look for uh the change tasks uh or the change request tasks there it is so change task request and I'm going to say that's true as well so basically I want to see where it is there are no change tests that have been signed to a change request and here we can see that we have quite a few of those that uh have not been assigned no tasks have been actually assigned to the change and the way that we can verify that is we can go directly into that and under the related links we can look that there's been no change tasks that have been assigned to that so again perhaps very Elementary for some of the people that are on this call but if you're looking for a way to find out when any change requests don't have any change tasks related list condition gives you that ability so let's see what else we have here so these use cases and generate reports about configuration items only show Ci's with two or more incidents created in the last 60 days so a lot of CIS most organizations have a lot of CIS so here we want to find out okay of those Ci's and for instance have been created in the last 60 days how many of those Ci's have two or more current incidents that are actually created for that so here you can see that there's 3925 different um Ci's there so now let's actually connect that directly to the incident and we can do that and here I want to see where created on the last 60 days and I can select that there and then I can also say that has greater than two greater than or equal to two incidents and then when I click run you'll see that we'll go from 3925 to now that those those CIS there are where let me go back and just pause that so now we went from 3 900 however many it was and we're able to filter that information down to seven so seven CIS in the last 60 days have had two incidents or more uh associated with them so again a way of trimming information down for the users to um take it in and actually do something with it so another great use case that you can do there so uh before let me get to the best practices or anything that we need to answer aloud yeah there's a few in here that I think we need to talk about okay I'm not sure like I'm not sure I understand that a question that clearly so I thought maybe we could just talk about it sure which one are you talking about um I was looking at agates I have a use case list of um uh rhythms without SC tasks or all SC tasks are closed with no approval pending how would you approach that it looks like I don't know if Jane you were trying to answer that or um I don't know if you want to come because I'm not sure well so so for that one uh the or so the way that you have to do it with the related list conditions is that you're going to flip it or is there going to be a problem um an important note with the related list conditions is that they're always anded they're always anded so you may have to do two reports to come through things which is not the end of the world um but for this one if I were gonna do without SC tasks um or all of them are or all of them are closed what you're looking for if I get this right is that there are no open tasks right so they're all closed or they don't exist so you want zero open tasks I'm going to rephrase that but I think we get the same answer so we would be saying give me all my um uh yeah all my rhythms my report would be on Rhythm my related list condition would be on Essie task which references Rhythm and then I go where there are no zero open tasks and I think I get the right answer and then no pending approvals I'm not familiar with the approval is going to be um if the approval is on the Rhythm that would be fine because this pet approval should be hopefully would be a state on there um the other thing that you might have to do with related list conditions and I I love related list conditions they're super powerful um but there are still cases where I'll use the dynamic dynamic filter condition particularly if I need to use multiple you can only use one related list condition at a time um and a way to get around that is to write is to use Dynamic filter conditions um which I don't know if we're going to talk about now but I know we're going to talk about a couple weeks um so that's that's how you do it but the the trick with related risk conditions is think about what do you don't want like I'm looking for something that doesn't have this then I'll use none um it it just takes a little bit to think about it but if I want something that doesn't have this then you're looking for none of the other thing um I'll go through a couple of these other ones that I see is there a way to add uh so hopefully someone answered that one um if there's a way to add other tables to relate to this condition yes the base tables have to reference the table you're reporting on um it is not something that's done on the Fly it's like dot walking dot walking is all pre-configured because I say this is a reference to this table so I can dot lock with a related list condition sorry I don't know if we actually said it because I was typing in a different I was typing an answer in this when we this first came out I tried I tried the nickname I was going for was moonwalking you got dot walking and moonwalking where you're going backwards but they still rely on having a reference um so for if it's not showing up it's because the the table you're going for doesn't directly reference the parent table um and so if it's uh the the purple and I get from this sometimes is where it's two dot walks away and then we don't get it or it had to go through an m to m or I had some database view so related list conditions are are a lot like dot walking to go if you can use it use it it's way easier it's very configurable but it it handles lots of use casability it does not handle every use case and so that's when we'll still use the different techniques that we'll go through today okay thank you Adam I don't know if anyone if we wanted to talk through the uh the question about the classic view of reporting that from Holly one of the top I am taking a look at that to see okay because I'm not sure I know the answer to that but I wasn't gonna say about it as a group we might have that it needs to work um we need to be able to put put those in so I'm actually taking a look at that um okay and I'll try and answer that one a little bit later on if you want to um move forward oh hey another one came in and I I love this question about um CIS relationships this only handles um this only handles references direct references it does not handle uh relationships um relationships are awesome uh for related lists but reporting does not you does not use those today um Safe Harbor I keep asking for it I'd love to be able to do it um you can get the equivalent of that if you use a dynamic filter option so if I take my logic and I put in a script include um it's not exactly the same but logically you can get it to work very similarly by doing um we're doing a dynamic filter option so if you're if you're in there writing a script just write two ones you use in my report and then one use my related list condition or my related list and Adam you you answered this one I don't know if it was to this one but you can only have one related list condition no you can't have multiple so you you had said that right so you guys that have yes that's correct you can't you can't have two sets that go to the same table um you can use ores inside of it in your related list condition the the condition at the bottom can have you know big ores that's coming from the table but only one at a time within the same table reference right yeah we've done the same table reference yes that is right and well I was gonna say the comment though if you're using CIS then you might want to look at CM seem to be query Builder because you have a tremendous amount of flexibility with what you do yeah and there is a uh there is a couple sessions that we did uh if you check our previous sessions I believe that there was one last year and uh one a couple years ago or earlier than that but it actually shows uh the cmdb query Builder and and just like Adam said showing some pretty fancy stuff there so if you've not checked that out you definitely want to check that out for CI information so I'm also wanting you know for that specific question on I need the you know for Ci's that do not have a parent and the related list the parent isn't shouldn't have to be a related list so in that case you should only you should be able to apply the filter at the core table and then the related list condition just I believe it was into that I don't remember what your what your second level table was so and that's the name that once there but in general what they're saying about yeah this not being able to apply it to two two different tables within the same within the same query okay great thank you Dan and wait and let's grab that last one from art I'm gonna put Adam on the spot because I remember this and I can't remember the answer when you add columns to a list view some of them are read and appear to be automatic dot walks but are these and how would I add one I I totally remember when I used to do reporting running into those but I can't remember what they were so I recall them brown but yeah I think I think we're talking about the same red they used to be red okay well my color perception is not great um so the uh uh so this is if I'm I'm not personalizing but I'm configuring a list for you particularly in classic view um and and I'm changing the list View and I or I'm sorry you're seeing some reports um we have the green all the fields that are on the table are black you'll see ones that are green sometimes a little plus sign where I'm able to expand the Dot Lock the ones at the bottom are red um brownish right um red and what those are are related extended Fields so you'll see them if you could click on task if you do a report on task you are going to see you'll see more red Fields than you will blackfields um if you go to like plan task you'll see uh the some of the ones that come from that as well so they're allowing you to access referenced Fields um and actually there was another comment you can do it in script as well with the reference underscore from the base table it's a little odd there's use cases for this um where you need extended Fields so if I was reporting on on task I can get the number is on task and I can see incidents and changes together a report on task and say where the classes change or incident it'll show me changes and incidents together the number field is the same so I just need number but if I wanted caller if it's an incident I want to see the caller in the list View and I'm reporting on task and I also want to see changes I'd report on task and underneath on the bottom there'll be incident would be in red and it would give me the caller field um which is only on incident it's going to be null for changes so that's yeah it's the extended fields front when you're on the base table you have access to the extended Fields now I will say I believe not everybody has access to that and I really don't recall whether that Z boots on I think it does it's a system property whether or not you show that to come through so not everybody on this call may see those red fields and again if you go look on task you should see them there when you go to go to a report or you go to a report choose the columns you see those red Fields if you don't it's a system property you can just search for it about displaying extended fields all right I'm gonna go ahead and move forward to make sure we're able to get through all of the content so real quickly let's just talk about some best practice when it comes to related list conditions so keep condition simple right we talked about that so whenever possible you straightforward conditions that are easy to understand uh complex conditions can be challenging to maintained and troubleshoot so make sure that you're using those test it thoroughly make sure that before deploying the related list conditions and Productions that it's been tested in development and or in your testing environment make sure that um or ensure that the conditions filter the data as expected and uh do not unintentionally restrict information involve stakeholders so anytime that you need to do something that uh is going to be used or going to be used in related list conditions maybe talk to your stakeholders make sure exactly it is what you're doing for them is exactly what they're asking for document the conditions if you can that way that you'll know going forward if a report or something else has a related list condition that uh someone else will be aware aware of it some sort of documentation and regularly review and refined so maybe it changes so you definitely want to make sure that you check back and and that's really with um a lot more than just related list conditions so you know going back and checking and reviewing and all this stuff definitely doesn't hurt so uh the next thing I want to talk about is remote tables and uh this one we could probably spend an entire session on uh because there's just so many different things that you can actually do uh with remote tables and um the different things that are touching those and and able to create and things like that it's very very interesting um and even as I was putting all of this content together and the things that I did really enlightened me not something that I've spent an extended amount of time doing but it definitely did enlighten me and um I think that I am going to put a session on the calendar in the future where we can have a session that's solely directed towards remote tables and their capability and things like that but real quick let's talk about uh some things so how do they work right so we're more remote tables fetch data from external sources or another instance using rest or soap the data does remain read only and transient in the platform memory um and without importing or sorting you can view and handle the external data as you would internal data and moreover you can manage this data using the now platform standard tools like Glide records business rules apis scripts table references and and some other services as well so one of the things that um and you you might want to make sure that it's the the remote tables plug-in uh has been activated so if if you don't see any of the fields that we look at here in a minute or that uh when we are in the filter Navigator then you want to make sure that you get with your atom to do admin to make sure that that is there um it may come I don't know if it comes default now or not but uh just it's always a good idea to check that out uh as well so um what I'm going to do uh in our example here is I I went out and got a weather API a free weather API and I grabbed that link and what I wanted to do is I wanted to put that inside of a script that would then populate a remote table whenever it's ran and actually I'll I'll put the same information into a report so this is a screenshot of the actual website that I got it from it's not all there but um I put in where I am from and then it generated um some the latitude longitude attitude and then the hourly temperature and it's the past uh um seven days and the previous uh or the forward seven days that I grabbed and uh very very um awesome when I was able to actually make this happen so the first thing I need to do is I actually need to create a table so here it's not a big table but I'm going to create a remote table that's just going to house the two sets of information that I have pulling in and this I'm just going to name I'm gonna give it a a label and I just called it weather and then it will automatically generate a name and when I click on that you'll see that it is UST so that lets you know that is a custom table that has been created and then I'm going to save that and I don't I didn't need the mobile module that's why I unchecked that um and you can let me stop this for a minute so right over let me back it up so you can see here so if you had a module that you had already created let's say an application that you created and it was showing up in the filter Navigator and you wanted this information to show up you could use this drop down and put that in there and you could give it a new menu name so you could actually show it in the filter Navigator if you chose to I didn't need to do that for this demo so that's why I wasn't there but I wanted to make sure that I shared that with you so here all I'm doing is I'm going to create a um temp time so that's going to let us know what time the temperature actually is and that's going to be a date time and then I'm going to create a temp and those are the only two that I need to create for this particular example that I'm going to do uh pop that in there and you'll notice that the sysid was automatically uh created and then once I save that I want to pause and talk about something so if you'll notice a lot of times when you or all the time when you create a table that's not a remote table you'll get a updated on sys updated on sys created on those will be automatically created but this information is just sitting in memory so there's no it's it's every time that it's actually ran so when I add this to the script and then I run the report that's when it's going out to the API and grabbing that data and it's just putting it in memory within the platform so it doesn't need an updated on or created on or all that or any of that information because it's not storing that information you know in that table to be used anywhere else it's it's just using that remote table to store the API information and then every time that you open up and run that report that information just gets rewrote on so there's why there's no reason for that information to be there so so let me go and I added those there so now what I'm going to do is I'm going to actually add that um that same information into a definition so I got to make a definition for the report table so here I'm going to name it whatever and then I'm going to search for that table that I just created so when I type in weather there it is now I have a script that I've already that's already been wrote for me and I'm going to grab that I'm going to paste that in there and what you'll see is that same API URL that you saw previously is now inside of that script and then also the two different um columns that are inside of my table so very Bare Bones it's going to go out there it's going to grab that information like I said every time that that is called and then it's going to pull back those different results and then we'll actually be able to see it so here I've highlighted to show that it's there and then also you'll be able to see that the two tables down there are there I mean the two columns within our table are there as well so this is just defining a script that's going to write to the remote table from the API whenever it's ran and then we can actually see that inside of a report so I'll uh what I'm going to do is I'm going to go directly to that actual table so the UST weather I'm going to show that so it's there now there are all of my temperatures and rather than me having to actually go and create a report which I could do that by going to the report Builder but I'm just going to right click on that header and I'm just going to select a bar chart and that's going to open up the report designer for me now I'm going to change it up a little bit to make sure that it's information it looks good I'm going to do a spline chart here and then I'm going to I don't want it to group by anything so I'm going to remove that group by and then I also I'm going to show it I want to show it by date um and I think I made a mistake and showed it by day and then I'm going to average that and then under style I just want to see those points those markers I like that so when I run that uh it looks kind of similar to the one that we saw on the screenshot from the actual website but I need to change this I need to go back and make that date um so once I do per date and I run that again so here we can see that it's showing me the previous and then the Ford so there's some forecasting in there and but all of this information is coming directly from the API it's not any information that I have to populate or anything like that the API is doing all of that information uh for me so this is just one example of the way that you can use um remote tables right and again this is just one way of using remote tables um integration Hub is another way that you can use remote Tables by creating information inside of the integration Hub and like I said I think the amount of time that I've allotted to or more remote tables in this particular session is definitely not enough because I can see um even with just some questions that are coming coming in or things like that that it's it's one that needs its own session so I'll definitely make sure that I get that on there and for um the sake of time we have about eight minutes left and I do want to make sure that I get the database views but I also do want to make sure that I uh ask Adam if there's anything that he wants to add to this this uh and I I love remote tables um whether we're but we're using a data stream from integration Hub which a lot of the out of the box folks have to pull data in to just display extra data that's sitting someplace else we can do it that way and then also do it to transform data um to come through that the data doesn't have remote tables that don't actually have to have to have to have remote data um so I'll do them I've done them in the past where I would run a Glide Aggregate and flatten data where we wanted to uh show a report that had the the number of open incidents or or the number of groups that had more than five open incidents something to that effect which gets a little weird but I can do it by using creating a remote table which does the first level of aggregate or whatever I need to do whatever transformation and then come back and come back through with it um so remote table is incredibly useful for read data to transform it to the way I want to see it um and if you go the best thing I can say they do for remote tables before Thomas Yanks me off to talk about database views is go look at what's out of the box there's some really interesting things that are in there that make it really simple really easy and what we do is remove the complexity from the end user having to know how to report on it put that into a script so that it's just a table that has the answers I'm looking for really quickly really easily really efficiently and and I I can I can take that burden of the complexity away from my user and put it on my shoulders and make sure it's right just a really really incredible incredibly powerful tool for you awesome thank you I don't know five minutes database to use might be kind of tough we'll see what we can do here um so real quick just some best practice so you know regularly validate the data source connections make sure that what you're connecting to is what it should be and that hasn't changed optimize the data retrieval so uh limit the amount of data that you're actually pulling back make sure that you're checking that regularly maintain data security and compliance so be mindful of the data privacy regulations and security best practice on what you're actually pulling in data may be sensitive so certain eyes obviously can they be on that Implement error handling mechanisms so if something goes wrong with the the pull into the remote table uh from the API or whatever you choose to do make sure that you are you know have a way of actually seeing those errors and handling those and then of course like I said before in previous ones document review and document and review configuration so if you have that out there if you create a remote table that has a script that's pulling information back from somewhere make sure that it's documented how that's done so it can be passed off to somebody else later if it needs to be so let's see not on the right screen so um four minutes database use may be tough let me see if I can do it so uh database you use and servicenow uh and the platform is a logical virtual organization of data um database views are very very powerful and there's definitely situations that they're needed inside of the platform um and you know we can't list out all of those different scenarios here and this time that we have but it is something that is there and that is available you um some benefits of doing those things uh consolidated data access so data database views allow you to seamlessly combine data from multiple tables uh and hence report actually so consolidating related data from diverse sources into coherent view users can create more comprehensive and accurate reports efficient data manage data management database views are read-only which ensures that the underlying data Remains the Same it can't be changed this provides a later protection against unintentional data modification flexibility and data representation users can tailor database views according to their specific uh reporting needs they can select relevant columns from different tables and Define custom filters to sort that information and improve performance so for reports that regularly pull data from multiple tables maybe using a database view can optimize that performance instead of repeatedly joining tables on the Fly database view provides a pre pre-joined efficient access point for uh the data so it does require an admin to actually create those so um anyone can use a database view in a report uh you don't need to worry about uh or need to create ACLS on the fields that are inside of a database view but also if you want to require explicit read ACLS to be added there is a Glide uh record that you can actually change to do that um and again if there's any ACLS that are on underlying data that are in that those will stay true um as well so there are a lot of out of the box database views that come with the platform uh if you're not aware of those just simply go to database views you might or may not be able to actually see those but you can definitely definitely ask someone that has the access to do that but taking this list here you can definitely and this comes from the docs you can call those in a report or a PA or wherever you need to do that um so example use cases so a couple of those those if you need to user at the IT department wants to report showcasing each employee their device assigned to them and their access levels of you can consolidate data from the user table Asset Management table and user roles table making it straightforward to generate such a report again you all show these inside of the PDF just some different ways for you to think about that so the demo I'm going to go ahead and do the demo if you need to drop off for the sake of time I I definitely apologize we got to the top of the hour so quickly but I do want to make sure that we uh do show some demos here but if you need to drop I understand again the recording will be out there later on today or tomorrow and all this information will be there as well so with that let's I'm gonna I'm gonna add it to as we bring this up with database views um super powerful real useful but don't use them if you don't need them I I do see people go to database views right right away for those of us who have a SQL background you're like I do everything in views they're a tool that we need sometimes but dot walking negates the the normal use case reviews lots of times it's gonna do those joins for us so make sure that dot walking doesn't work for you and then even if I have an m to m i don't need a database for you because I just report on the dot on the mdam table and Dot Lock Up both sides I need a database view there's two use cases that I need a database for you one I'm joining to a doc ID which is what we see in um metrics and slas and Spotlight it's a doc ID it's not a real reference so I need to use a database view for that and if I'm doing two M to M's and the M2M to M if I have two M tables I will need a database view those two you need them an example Thomas has here we have the doc IDs for metrics or from assessments um then you need them but don't use them if you don't need them don't over database view yourself it's just extra maintenance that no that for no for no benefit also I totally agree with that Adam definitely uh one of the the demos that I I sort of go to a lot is because I've heard it a lot in the community from questions is survey data and um I actually did a session on this same demo and an hour-long session on the same demo uh that I can definitely reference in this uh Community event as well if you want to go back and look at that in case this becomes kind of quick for you because of like I said for the lack of time but one of I created a database View and basically what I wanted to do is I wanted to normalize survey data so when you look at survey data in the table it is in um rows with each question being its own row and things of that nature and a lot of times that can be that can be tough to sort of uh take in and consume rather than having a user uh on a row and then those actual column headers be the different questions so what I did in this is I gave the ability to actually look at that survey data and normalize so in this survey we have quite a few um different joints here or different um things that we're going to do so the first one is basically I'm just connecting to the assessment instance and that's simple I mean there it is it go to the assessment instance table and I grab that and then the next thing I do is I'm going to grab I need the questions each one of the questions and here I you have to use sys IDs now where I actually got these sys IDs from is you can go to um let me look at my notes Here if you go to how did that change sorry about that I'm going to go to this specific table so s m t underscore assessment I'm sorry metric and I'll do this with just one of them so we're gonna have to do it so this table and here I'm going to look for service desk so this is the particular survey that we're using inside of this particular demo so when I go inside of that here we can see let's go in there let it finish here so if I copy the sys ID for this particular one this is the actual survey that we're looking at in that particular um a database view that we created so when I copy that sys ID then that gives me the ability to go back into let me go back to my history get there faster foreign so again the question this is the actual survey that it's coming from which is this ID that I grad the next thing I need to do is get the actual question right so the way where I can go and actually get the question is for this particular case and again you can go back and watch the full one on this um asmt underscore assessment if I can type so this table houses all the different questions out there right and here we can see the service desk status so this is particular survey so I know that these questions are related to that right and if I group by Metric then I'll actually be able to get those particular questions for each one of those come on be nice um instance this is why there we go okay so here in the category I'm going to search for service desk welcome there we go so these particular these are the questions that are related to that particular survey so if I go into any one of these right and I go into that particular question and I right click and I grab that once it finishes loading here there we go I can copy that sys ID and again I can do that for all of the different questions right and I come in here and I paste that here that actually gives me that particular question okay so I do that for every one of the questions there may be three questions there may be ten questions whatever it may be and I grab that and I pull those two together and when I try it and I run this you'll see here that here's the status act the survey it lets me know the number it lets me know the assigned to users and now those questions are above the top well you'll notice that I have one of those that actually gives me the actual question because if you don't then they just come back as string value so the other thing that I did in this particular case is I went to the sis dictionary and I was actually able to create or change the um what they're actually gonna the value of those particular ones so now it's some relevance right because stay there because what happens is is that just saying string value or whatnot there's no there's no True Value in that I mean how are you supposed to know which question than any one of these scores actually relate to right so what you can do is you can add you can go to the sys dictionary assist documentation yeah right you're writing a label a label for the database view this is documentation I was answering a question on that moment ago uh I believe the label the module is labeled but sis underscore documentation is the table that's right I went straight to the it's it's a related list in a lot of places and I actually don't know if there is a module um but now we'll put in the the table will be the database the database view name and then we can um yeah yeah so here so again sorry about that uh so the CIS underscore documentation um dot list is the the table that I went to and basically what you do is you're going to uh create a different label for the different strings that are in there so basically I went in there and I said okay I want to Norm I gave that's the demo that's the um the database view that I had though this is the label that I want to do and then the element I choose whichever question the string value that's in those results that corresponds to that and then there's a hint that you can also do that's basically when you hover over the question that's the hint that it'll give and you can do that for every one of the particular questions that you have inside of your database View and now it's actual relevant to someone that's actually looking at it so again understanding that when we go back to this and we try it again um that's how we're actually able to get a label and again you notice when I hover over there's that same hint so by going and changing a label or creating a label in the system dictionary that corresponds to this this other to this database view a column in that you can take that and you can do that for for anything so if anything if you have a database views that returns some sort of string value based on what you're doing you can definitely change those headers to make it be um something else so again there's an hour long that we did on building this out that is going to be much better for you than the five or eight or nine minutes that I've rushed through doing it and it shows and there's also a Blog as well that's out um that I'll put into this as well that actually shows you step by step on how to change these column headers um as well so I apologize I got a little confused there all right so did any questions come in I know we're way over here but I can't see questions at the moment do we have anything we need to um about what's going on I set the where a specific uh join question or I only want to see the active changes um if you have a if a database view is not working the way that you're expecting if you can I would post it into the um into the community with a screenshot of your where Clauses it's it's hard to diagnose exactly what's on there um sequence on there but if you take a screenshot of uh the database View and the the tables and the where Clauses normally somebody can point out um what's going on to come through but I'm not sure we'll be able to answer that right now okay so real quick let me just kind of go over some best practices and I'll get everybody out of here uh because I think that these are definitely things to think about when doing uh database views so Define the the purpose clearly before creating a view ensure that you know exactly what you need to create and understand the business uh need as and what it's going to address so again like Adam said a few moments ago just don't create them to create them there that's not their value make sure that you understand and have a clear purpose and reason of why you actually need to create it maybe even that being the last source of uh the way that you need to bring information together uh limit the number of tables so again like anything else the more that you add to it the more possibility of it having some latency and things of that nature so make sure that you're looking at the the number of tables that you actually add to it and as usual with everything else optimize it and test it make sure that you've built it the best way that's going to run it the uh most judicially and get it back to that information back to the user as as quick as possible and like I said with everything else document it know the way that you have those joins know which tables are joined into what if you and especially if you're using like in my example sys IDs document that information that way if a question is added or a question has changed in the survey example you want to make sure that you have those sys IDs uh correct and always ensure data integrity and security so database views should should respect uh data access controls just because data from multiple tables is combined into a view doesn't mean that all users should still see it so make sure that you keep that information um in mind so definitely some best practices around that obviously we've been answering questions the only thing that I if we didn't get to your question or if you have more database view questions please ask those in the community feel free to tag me I'll make sure that we get you an answer to that as best we possibly can and I like I said I'll definitely um put the link to the the course that we did or the session that we did um uh last year a couple years ago that talks about this and if we need to get a database of use a session on the calendar we'll definitely do that as well looks like somebody asked for that uh the one thing that I want to say these are all slides that you've seen before uh or it was also in the video but in two weeks our own Adam Stout who's answered most of the questions on this call is gonna uh give us our first sort of uh look into leveraging gen III for analytics code assist you'll want to be at this one make sure that you put it on your calendar in two weeks to be at this one I anticipate it being uh very good all of his sessions definitely are I apologize for going over 15 minutes I appreciate all the panelists and we'll see everybody in two weeks