all right hello and welcome to uh another episode of inside the barrel this one's a pint nice and short and sweet um today the what we're gonna today is an issue i ran into the other day on a client project where um they wanted to allow the end user to upload or import record data into a table right so the example that i'm going to use is time card data so you have people that have time card spreadsheets and they want to upload it to the time card table um and so i i someone at cask had some some answers for this but what i had found ultimately was it was based on some community posts that i'd seen so the code is out there i've just gathered it into a working demo for people yeah so um yeah without further ado we can move over to the the share yeah and i can i just want to like talk a little bit more like i think this is a common like customer issue where like somebody is used to using excel for so long and they have like lots of data and yeah yeah where i've seen is consultants will recommend well like let's move it to a multi-variable row set right and it's like you know just press the new button fill out this row of data press the new fill out this row of data and i find there's like a limit to even using that right like just at some point after like five or ten or depending how many rows you have it's just way faster to do it in excel and like i think this is a really good one for people to to kind of keep as a nugget because it could help uh bridge that gap of a process when the process is completely out of service now to import that data into servicenow so it can be utilized such as time card data yeah and you know another another thing that i see this being beneficial for is as as an admin i mean how many requests do you get to import data not to mention your day-to-day plus your backlog of all the stuff you have to do so things can start getting behind right so you have your end users trying to say hey i need stuff uploaded and now you can empower your uh users to get their data in without uh having to wait in a queue or or put a request in you know they are now empowered to uh to get that into the system [Applause] yeah no i'm with you yeah i think anytime you empower users right like it's uh yeah it really makes admins lives easier so yes yeah for sure so i don't know how busy we are yeah so i'll share your screen and let's jump right into it all right perfect so again this was just a real basic throw together um example of what we're doing but it's it's a time card record producer uh the one of the keys here is that we we point it towards the data source table not the actual time card table because we'll we'll handle that separately and really most of this is handled right here in this script filled on the record producer and we can walk through this real quick so what we're going to there is i realized after i wrote this stuff because i did a lot of copy and pasting that um some of this isn't needed anymore some of this checking just because you can use the uh attachment question or uh field type and so you can't you can't add more than one record so there's a check in here to see if there's more than one attachment out there this this section here obviously you wouldn't need that anymore because you're only it only allows one anyways um but uh basically the concept here is you add the you know on the uh the record producer itself you click to add the the excel file and uh what you're doing is your ch you're looking at the the attachment table for that record and then as you grab that glide record object you can check to see if there's a file and obviously this one probably isn't needed anymore either because you can make the attachment field mandatory so you don't really need that one anymore either because i think this was originally based on people just attaching records to the the record producer itself you know the drag and drop method and there you can add a number of records um and so that was where some of these checks came from because of that makes sense so so then you'd probably want to disable like or hide attachments right i know that's an option on that yeah yeah you'd want to do that just because you don't want to have mult multiple entry points for the files you want to really lock it down to the single um [Music] click to add file and then uh so another nice thing though you can do is you can check the file itself to see if it's got an extension of xls or xlsx to make sure that they have uploaded at least the correct file and then i have heard that servicenow has a method of or that they've they have an example in one thing that they do where they check the columns to make sure you've got the correct columns but it's honestly it's on an on submit though because they there's some some reason there that they do it through the on submit versus anywhere else and i wasn't sure if if i was going to ask you about this but i totally spaced it javascript is able to dig into it from the client side i don't like it's been a long time since i've had to deal with files yeah in javascript so i don't remember if if we can dig into the file itself before it's submitted and i would assume this is why servicenow does it on submit once it's already in there that they can look at it i would i would think either way i mean like yes as much as you can put on the client for instant feedback would be great um i think that like if this is like being submitted as a record um i think you just want some validation on like the transform side to make sure that it has the column so it just doesn't input bogus data and so i think that's probably an acceptable place like what i imagine you would do is also have like a flow attached to this that sends somebody an email when like the transform is done and if any rows were skipped like let them know so like they can kind of um see see if like some of the data didn't get in so they're not confused when they go to the system they're like where's like half the data right right right yeah obviously you can make this pretty robust um this is a quick uh throw together here but yeah and and now that i remember it's the reason i do it on the submit is because once it's into the import table you can just check the columns there and see that they exist but i was uh you know same thing if we can get it on the client it makes the the you you know usability ux that much better yeah but so and then here's another little thing that i came across too so in native you know obviously if you fail some check it's not the right file type or whatever you want to bring them back to that record producer right what do you do from the portal side you know and so you can do a producer.redirect which is native so you'd send them back to the native um catalog item link but if it's if you're in the portal you can do the portal redirect i didn't know that existed this is new to me um and then you send them to the catalog item there in the in the portal but what's neat is you can have them both listed here and only one will work depending on where you are so you don't have to do any checks to say oh am i in native or am i in portal you just run both of them and it'll handle it on its on its own cool so that was kind of neat yeah i was pretty excited about that one i'm pretty sure it's been around for like years but it's okay all right so um we get through all the checking that's that's happened everything's good um what we're gonna do is set a couple of constant values here one is the table that you're ultimately going to go or the import table that you're going to use and then the actual transform maps this id now another thing that came up after i built this for the client was they wanted to do a couple of transforms as this goes through and this you can pass it a a string a comma delimited string so you could have multiple transform maps here and use that to process in that order cool so we're going to go ahead and set up the um the table the uh the data source record here uh and set that up tell it's a in excel you can specify the header row and the sheet number and then insert that into the source table um [Music] and and you have to get that assist id because you're going to need it and and then one other little piece that came up was on that original sis attachment call you have to move that attachment to this record so a very simple way instead of doing like the copy attachment and all that stuff all you do is use reset the table name and it will update that record to now make the attachment show up on the the data source record because that's where you're going to grab it from as an attachment interesting yeah nice little little trick yeah so yeah so repeat that one more time because that sounds a little okay so currently you have a sys attachment record right when you run this this record producer creates a record in this attachment table um you want to make sure that that attachment is attached to the data source correct so that the transform runs on it yep right so all you're doing is you're saying okay this original um because that sys attachment gr was what we did okay i got it i got it because because the the table name is going to be this thing yeah yeah and so all you do is uh cool yeah you just update that table name to this this the nice data source record uh super cool yeah yeah yeah very simple very easy um and then once that's done because yeah i i was as i was copying pasting i couldn't remember why this was there so i hadn't i moved it out and i'm like this isn't working and then it occurred to me i'm like oh yeah you gotta move the attachment over all right so then we have this glide import set loader um which is going to take the current data source record as part of the method there the properties in there and then you're going to start that process um gotcha yeah it looks like it looks like you're like getting it the import set online like 66 and then you're loading it right on 67. and i wonder here is like where you can do some checks right like at some point like maybe before line 70 you could i believe yeah i think you could because you could go back and check that import set table and look for columns and stuff like that so you still it's not necessarily that it's you're all the way down the process you still can stop it here if a check fails but um it's not it's not instantaneous right it's not that client side happens right there but you still can do it here and i would agree i think you know if you if you put something into um this this line my keyboard doesn't want to work interesting um that's weird then uh then i think you could do that you could do some checks and and and look for certain things um and then anyway so then you do this uh import set transformer worker which is going to run um that transform map and it'll it'll put it in the background and then it'll start uh and then since we don't actually want i think if i remember correctly we don't necessarily need it well it does create the record um but again you could you could have two of the direct the redirects here so after it's done you can send them back to the catalog or the home page on the service portal um or somewhere within native and what you should get is um so we can run through real quick i have a a single line um oh there's nothing going on there we go all right so and i think while you're looking for it like i think what's also useful on this catalog item is to provide like a template for somebody right so i'm thinking you in like have a description here that says like you know plea how to use this catalog item and then like an example template that they could download um yeah you know what what would oh my goodness i can't even click on anything i'm not sure what's happened the stream broke your uh keyboard well it sure feels that way i'm trying to see if i had something oh there we go something just something like popped up that was like well i yeah it's okay so i'm if you hadn't noticed i'm standing right i was trying to make sure my cables because i have a rasmus under my desk i was trying to make sure nothing was like getting pulled but i think i think something did because all of a sudden my uh i have a you know this usbc yeah and and it did some all of a sudden my sd card is like oh it's not properly ejected and stuff like that so who knows anyways so this time card test was yeah i grabbed it from the time card table i i did the spreadsheet template and then i just put a single line in and it should be a three seven date for some hours and so forth um and so what what we'll get when i run this is we should get some messages um i put some gs info messages in there and so you can see it's correct format starting import time card import uh started successfully and this is the data source import you know this is the really [Music] this is the record and obviously this is why you want to redirect it you don't want them coming back to this record yeah but i don't need to see that and what we should get now so we have a a a row in there but we should get one more for the seven and there it is and um we can look at it and you can see that [Applause] it's so good but you can see it loaded the stuff in and it totaled um that but you can see how this would work you know for for users that need to import a data set of time cards and have it get into the system without going through an admin cue or a backlog or submitting a request do you want to maybe show the import set um table um for people yeah yeah we can get into that just so that they could see like how the data um i think it was from not not from data sources but from because this is that data source yeah this is actually one thing i didn't want to bring up though it it will create each time you clean it yeah you definitely want some clean up on that um just because it will create those uh go to imports it's under advanced right there yeah and so sort by let's load completed probably is fine yeah answer that top one so just if you haven't seen one right like this kind of is kind of what it looks like the import set runs and then if you click on the row or yeah click on that one that's fine you'll this just tells you how much was run and if you had any logs but if you go back one there was another tab that uh we the imports that rose so you can see that you know this row was inserted so yeah yeah and again you know i think it's just it's just a nice way to offload some of that um work that an admin's gonna get day to day or whatever and you can build a whole you know whole process around this to save yourself time and energy um for different you know different use cases yeah no i think it's great especially if you add some checks to make sure that the data that they're loading is what they want and maybe it's not a record producer it's a catalog item that goes through an approval record like yeah you you could definitely throw some checks in there um so it doesn't necessarily create the record right or what you know right away you can sit on it and then get it approved and then have it do that so you're not bringing in bad data right yeah i think that's that's a good idea yeah i think it's like a really nice little package that you could you know help you know if we if we think of a digital transformation right of in a consulting world you can't always get from a to z instantly right you do need some like phases or baby steps that you go through and i think this is like a good example of if someone has lots of data they need to get it in and maybe that data comes consistently like here's like a process you could utilize with people and you could you know you could maybe even not open it up to just any end user you could have ad or you could have uh what are they called you know a higher level user power users power users that's what i'm after yeah someone that you can trust that you don't really need to worry too much about but again that's probably bad process that you don't yeah but you just open it up but i think it's great right a user criteria on a group maybe or on somebody with a specific role like you could definitely you know find the right balance here because you don't want anyone just to be able to submit it so yeah yeah because you know we've all been with those than users that are like i don't know how to use computers yeah and it's uh it's also tough like you probably if we were really robust we'd probably want an easy way to to roll back the data that's that's that's probably the harder tricky one is like as an admin now like if someone uploads something and they're like oh no i uploaded you know crap data then you're like oh man now i have to spend this time you know well so i i actually have a i posted something on my linkedin the other the other week that is this there's another component to this from the same client that i had to add in where their their process was if you submit a time card if you submit time card data and it's incorrect you basically end up submitting an opposite time card record so if you submit a time card a with seven hours on monday and that's incorrect you would submit time card b with minus seven hours on monday interesting and then one more so a third one with the correct hours and i i have a solution that loops through that and just adds the the things together so it only creates one record with the correct the corrected time amount yeah [Laughter] so you can get creative with it for sure yeah you know yeah there's a lot of avenues you can go down cool well i i think uh i think we'll keep it you know at 20 minutes here and yeah if you enjoyed it like subscribe if you have other topics you want us to kind of dive into i think next week we'll try to dive into san diego release since that just came out um so we'll we're we're going to try to explore all of the the hidden gems that servicenow provided and maybe things we don't like yeah maybe yeah wow again uh hope you enjoyed it and thanks again john for for leading us through that yeah no problems i have a good time we'll talk to everyone later