[Music] good morning good afternoon good evening wherever you are and whenever you are welcome to the community live stream my name is chuck tomasi from servicenow just checking make sure everything is working here looks like it is a lot of people joining us in the live chat thank you very much this is september 24th and our topic is flow designer transform functions today because this is the show that gets in technical with the platform and gets you down deep but whether or not you're just starting on the platform or you've been around for a while i assure you there's always something in here i've learned something hopefully you'll learn something as well and if you do learn something be sure to share that make sure you go over to youtube and click that like button so other people know there's something helpful in here subscribe to the channel there's lots more for every video i put out i think there's about four or five others that are are appearing on the servicenow community channel as well so i strongly invite you to turn on those notifications i just got one as a matter of fact i should have muted my device it's not but you will get that as well in fact i'm going to do that now there we go turn that down just a little bit and i am going to continue on with the pre-roll if you're not watching this on youtube then you also have the option of doing this on twitch and get that for a couple of weeks over there just in case you have any uh technical issues with youtube getting through this just check and see looks like oh looks like dhruv ended his meeting on time good to have you here drew and everybody else that is uh joining in and saying hello we also invite you to go over to the developer community at developer.servicenow.com where you can get yourself a free personal developer instance a free learning plan to get up to speed on becoming a developer you want to advance your career you want to make more money you want to bring okay i'm not going to make false promises but this could happen it could happen to you if you go over to developer.servicenow.com and get the get going with the apis and the documentation it's all over there including a couple things that i'm going to mention in just one moment but i also want to include the meetups over at meetup.com that's part of our developer community we've had number of developer meetups there's one or two coming up next week uh there's one the following week it just it keeps going in fact let's take a quick glance at what's coming up september 29th is next week we've got the vancouver meetup october 13th october 1st let's see october 13th is richmond october 1st is phoenix i'll hopefully be in attendance for that one it's not only in my time zone it's in my town we've got the texas virtual developer meetup not just like austin or houston this is the whole texas team and we are going to be going through that on october 16th utah in november we're out to november already wow great planning people utah virtual meetup so they've got one in october and one in november excellent lots of stuff coming up 23 000 people and counting around the globe surely you can find a chapter that's near you if not please let us know and we'll help you set one up okay i want to remind you that we still have the breakpoint podcast going it's been going for almost two months now this is an audio podcast where i talk to product managers and customers and my teammates about servicenow developer stuff we just had one that came out on tuesday with jason mckee of course talking about creatorcon 2020 that's coming up mark your calendars it's still a month out but it's not too early to mark your calendars this is of course a virtual event so you can find it online we'll send you all that information in just a few days you'll have all the registration information all the uh session lineups and sign up for that stuff so october 27th is the date of the live content october 20th is when the on-demand workshops come out and we're doing that in advance so you can get together at the social hour happy hour or whatever you want to call it afterwards and talk to the experts and say hey i was working on the flow designer workshop or i was doing this or i was doing that and kind of what you do after you've been working side by side with people in the real life so we've got a little bit of a different spin on that october 20th is when that comes out watch your email box on october 8th for that information the other thing we are talking about next wednesday so the discussion with jason mckee just came out on the 23rd of september the one with andrew barnes which comes out on the 30th is going to be talking about hacktoberfest this is what we call spoketoberfest last year we're increasing it this year watch for an article on our community excuse me on our developer portal blog developer.servicenow.com blog.do is where you can find the blog packtoberfest is going to be mainly focused on uh integration spokes or flow designer spokes as well as now com now experience components still early working on that does that uh logo kind of remind you of harry potter at all i'm not sure that's that's the official hacktoberfest 2020 logo that i got off their site so just just me go to the developer blog we don't have the article out yet i am checking to see when that comes out but it'll be on the next day or so because we really want to promote this get involved with the open source community and i've if you're looking for ideas i've got some ideas if you want to build them so hit me up and i will give you some ideas of what you could possibly build this is rewarded based on pull requests on valid github listen to the podcast next week andrew and i talk about it for nearly 30 minutes there's a lot of information in there so good luck with that i want to remind you one more time that we do have another tech now tuesday coming up on october 20th all around the first part of process automation designer and playbook with yaranguez so go sign up for that oh i forgot the url on that one it is bitly slash tn 80 reg tn is in tech now 8 0 reg as in registration i apologize that's a new graphic that we just slipped in yesterday should have gone with the old graphic see what happens when you try changing things on the fly so we'll get into that we won't be doing any code today so let's get right into the content on my personal developer instance i have logged in i don't need you to remember the password again so let's jump in talking about transform functions we begin by going to flow designer this is of course our workflow engine for the last several years turn that phone off we don't need that and i've got one called transform test very simple flow all i'm going to be doing is playing with the transform functions on the data pills they allow you to avoid scripting really for some of those mundane things those simple things like hey here comes a comma separated value it's in a data pill rather than writing an inline function something like this and getting this script box down here you can actually turn that off turn that off you can make use of functions that are built into the system now we're all familiar with if you've been watching the videos or playing around with flow designer we're all familiar with what happens when you click one of these pluses you get a menu of features that look like this and you get a nice little description over on the side okay shouldn't be foreign to most of you the transform functions are on the data pills so i am going to start out very simple and i've got an employee record it does a lookup on employee records pretty simple thing and for each one that it finds i've got three employee records you can see them over here let's look at our sample data employees i've got three records chuck jeremy and craig happen to be the host of tech now okay we've got a lookup that happens and for each employee record i am going to take the name field okay and name has a first name and a last name easy enough now when i hover over so we can zoom in on that when i hover over you see that little fx that appears that is my transform functions and i can bring up this transform function menu and it's got a number of things i can do for example string manipulations let's convert this to uppercase and it gives you some nice examples over here of what your input would look like what your output would look like let's just do two uppercase and apply that pretty simple and now that i have a transform function on that data pill it shows up with the fx lit up notice that it doesn't go away when i don't hover over it you can do this to any data pill make sure things are still working i like to glance down at the chat room every once in a while people say hey i can't hear you or the screen went dark so i'm watching it all as it happens all right that's our transform function let's save that and do a quick test on it see what happens we i would expect that the three records come back with all uppercase names in the log hit test hit run and there's our test this is the execution details we get when we click that and i look at the log and down here array it says uppercase my name i go to the next record uppercase craigsstep uppercase jeremy duncan i didn't sort these i said i don't remember sorting them sort of descending on something i don't remember okay that worked great simple way to do uppercase and many other things let's take a look at some other functions that we can use on that transform datapill thingy we've got loading loading loading i applied to upper case now you can put multiples of these together for example there are suggested transforms based on the data type if i put in a date field it has different results than it does if i put in a string field i'm doing a string field only right now so i can actually parse this thing apart i can do a split operation and transform it into an array oh that might be fun let's split based on a space character i always get nervous with space because sometimes when you enter space it doesn't recognize that so let's split on a space character then i can get an item from an array you could say zero one two three four let's get the i wish it had a first or a last last would be a really nice thing let's get the zeroth element just like saying that and if you're not sure there's more information down here be cool if you could put in kind of like service portal has with dollar first and dollar last that would be fun oh look there's a last character and notice that it's lining these up over here split get item from array and let's get the first character from the first name i know that wasn't very original we could probably get the first character from the last name but let's do that just as an example i should see k c and j right that's what i would expect i apply that i now have split this into an array and get the first character from the first element there we are let's see what happens click off over there my fx is still lit up but again at any time i can click back into there and see what's happening very easy to get a summary of that click click click this might be a pretty short episode there's no code going on hard to make mistakes but i can do a little more exploring run the test and view the results and i get in the log there's my c right there hopefully my head's not in the way blow that up way up there's my c and i get the can't quite get to the next record do that get to record two there's the k you get the idea and there's the j starting to sound like men in black designations agent k agent j and agent c let's do one more because i don't think i hold a whole lot of examples on this let's see let's oh i have an idea let's take this if we go into the employee records i have a field called p code and it has these numbers in them you see this might be i'm getting it from a glide record you may get it from a rest response you might get something else notice that they all have these wonderful numbers in them i would like to get the maximum value out of those numbers yes we can do that with a transform function so let's do that i'm back here x x x okay now we know what the format is it's comma separated values so let's first start by doing a split i know i had a split up there before but let's do that we now have an array and we should be able to find not get item from array it was i love how that returns a complex object that's interesting string date time utilities and value appears get key value map we're going to get into that in just a second uh complex data some of these i haven't seen before where is my last character split substring trim there was a way to do a max in here i swear i saw it sanitize simple math there it is i thought simple math originally was going to be you know add one to this thing it's not in there yet can you define your own transform functions good question not yet i did ask that of jake berman oh about two three months ago but not yet but that is on the roadmap i don't have a time when that might be involved so you pass it an array of numbers and it will come back with stuff so let's see if this works i'm going to split it and i'm going to find the max value and if we look at these three records the max value for craig is by 15 the max value for this one is 1670 or jeremy and the max value for me is 10 45. okay kind of get some rough ideas of what we're looking for we know what a good run example would look like let's test that run test and get that look at the log we have zero zero zero uh oh you know what i might have done um it might not be returning an array of numbers let's look at that closer i thought log the max value p code string on an employee record i thought i did this before if i've got my split returns an array.string okay so i probably need to transform that into a number so simple math sanitize utility get item from array i need to transform those into numbers let's see if we can find something suggested transforms no just transforms on this minimum and sum no that's might be you are looking at the name attribute you are right i am looking at the name attribute thank you wrong field dave you get a magic coin okay so let's go to our for each employee p code totally mess that up let that be a lesson to you get the right data pill okay now we have a split interestingly enough this says it's going to return an array.string so i'm curious how it knows to convert that to a number do we have to do a number thing in here probably not but from there we could get the simple math let's just try this i thought this is all i did last time it's been a while since i ran these let's just do that again let me check i've got split and max and done and test run run run make sure you click run otherwise it doesn't run and mr log says 1045 515 and 1670. that sounds like the numbers i had before which is curious because it feels like there's some sort of implicit data conversion going on behind the scenes because if you look at what max says max says it wants an array.number but split says it returns an array.string which is fine because if you sorted these as strings the number 67 would come out after 10 45 for example so i it's working somehow i'm not sure i have complete faith in how it's working yet but thank you behind the scenes java programmers who write the platform and recognize the data types or take advantage of javascript's implicit lack of type checking i don't know that one's that one's going to be an interesting one uh the other note i have on here is a function i want to try something there is this test sql on there and i have a couple of records that have something in there which says i i purposely went and looked up some bad sql statements let me look at these there's one that says test sequel x is small and of course there's always one of my favorite if you if you're a fan of the xkcd cartoon probably one of the most famous ones is the bobby tables one i somebody actually went and registered bobby let's see if i can type this bobby dash tables.com and all it is is the xkcd cartoon if you haven't seen this it's kind of fun that says this is hi this is your son's school we're having some computer trouble oh dear did he break something they say well in a way did you really name your son robert quote parenthesis semicolon drop table students semicolon minus minus oh yes little bobby tables is what we call him says well we've lost this year's student records i hope you're happy and the mom responds back says and i hope you've learned to sanitize your database inputs okay one of the things we've got let me let me take this as an example because this is right out of the cartoon which i think is funny and i'm going to drop that into i think it was my record jeremy craig my record i'm going to be use the bobby tables one and i will save that and we're going to use this test sql field so let's use the right data pill this time dave thank you for each employee sql there it is and there is a sanitize function on this so imagine if you were getting an input from a form and somebody typed in that whole little bobby tables one you could clean that up and inspect that uh it's not available for orlando release only i'm on paris so it's orlando and above i just never had a chance to dig dive deep into this let's go to sanitize sql and let's see sanitize identifier says returns a string with escaped special characters and ejected values sanitize sql value returns a string with escape special characters injected values for sql values perhaps the input string this is i'm not sure what the difference is what do you say we find out parameters needs a database so you specify what type of database you've got and then it will know how to properly escape those characters and sometimes it's just wrapping them in a quote so let's take those crazy things and what are we trying first the sanitize identifier let's see what we get this is how we learn test run go and view output i should have three records in there there's my log and it puts quotes around the outside it says hey congratulations you're now a string interestingly enough they're backticks if you look closely see if i can zoom in on that today weird all right well not as good as my zoom last time okay putting quotes around that that is a little bit behind my head maybe zooming there now you see how they are back quotes i like i've called those back ticks before in a previous life the second record also quoted them with fact ticks that means something in my sequel to to escape them and this one also so that's how it does it with the identifier operator let's try a different one cancel that bring this up and take that out and use the sql value select the database again as my sql i'm most familiar with that one i have had a little bit of oracle experience but we're not going to go there today test run run run run get the stuff and once again let's look at the log this one's using single quotes and it looks like if my imagination is not fooling me it changed a single quote to a double quote notice that this quote right after robert is a single quote it turned it into a double quote interesting look what it did you mean there we go it changed it put a single quote on the front and end of this string and then change the single quote to a double quote so they wouldn't conflict there you go that is i assume it did the same kind of thing for the other ones just to keep them safe yeah this one was x is small if i look at craig's record here let me put my big fat head away for just a second and there's the other one might be a little hard to see but this one had several quotes in it and it fixed them all up so single quotes on the outside turn all the single quotes index so you don't have open strings that could possibly mess things up and of course if you put this into a sql statement it's either going to mean something like part of a select statement or update or delete or it's just going to make the thing invalid and then it'll go i don't know what you're talking about which is probably what you want if it is in fact an invalid query that is that the end of the story let me check my notes i thought i had something else no there's one more that i wanted to show you okay several episodes ago we did this thing where we had uh employee points and put that away does that help not really we had employee points and then we did a lookup table to say if you've got in this range you're a novice if you're in this other range you're an expert or a wizard so you could use the same thing with customer loyalty points you've spent this many dollars or you've flown this many miles you're gold silver diamond platinum whatever okay that was a simple exercise of lookup tables going to do something similar with that transform function to say hey if you're novice well maybe by the time you get to novice let's say we give our employees a 50 bonus or 10 bonus you know amazon gift card or something and then when they get to intermediate or expert we give them a little more bonus just some sort of monetary incentive it's not part of that same lookup table because it does do a range but when it hits a key value you could do a lookup that way so let's see if we can walk through this i'm going to take that data pill off and put not a function come on charles turn that off i'm going to put the band which is going to be a choice it'll come back with novice intermediate expert you get the idea uh double quotes are the escape characters in sql good to know thank you expert people who know more than sequel than i do what i'm going to do is this where'd it go where'd it go where to go is it complex data no that says 2xml sanitize shell arguments you can do the same kind of thing if you with that it wasn't simple math utilities there it is okay key value map this is an interesting one and i think this is new to paris but i haven't checked the exact parameters i'm told by jake that this is a new one i give it a default value which is it was 10. no it was novice i'm trying to remember if it was the key or the value it's the default value now or default input we're going to find out maybe view more will tell us the default value returned if the provided key is not found okay so i do in fact want something like a zero in there you get no bonus if i don't find a band for you when you reach novice you get ten dollars when you reach intermediate you get 50. when you reach uh what was the other one expert you get 100. let's see how that goes very simple key value map easy to do a really quick look up this way and let's do a make sure our data supports this it's always a good idea um i need the band field up there and the band says i have one novice one intermediate and one expert craig is our expert jeremy's intermediate and apparently i'm the obvious today when i hit test run probably should have also put the name of the employee there but i would expect to get one of each now look at the log aha i have a 10. look at that that's a wonderful thing right back to here i have a 100 and i have a 50. because i said it i have to do it let's also put in the employee's name so i just move my cursor to the front of the line and go for each employee with their name oh you know it would be really fun let's put two of these together what did i do before i said split on space then take the first element which i think is going to be zero because arrays start at zero and then get the first character just because we can okay so i'm going to string a couple together on the first thing this one's got a function that one's got a function put a couple of dashes in the space between them we're getting fancy now run that test i should see like a c10 j50 k100 and drum roll please look at this c10 isn't that an aircraft and go to the next one k100 j50 not a bad little thing to do for function i didn't have to write any code that's the point there's no code involved with these transform functions and i would expect more to be coming out in the future uh can this transform capability in any way be used to create temporary calculated attributes for reporting purposes without actually adding a field to a table every time you want a new calculation um good question it looks like this is just an output thing or or use it as an input for example i'm using it as an input to the log so the transform is happening retrieves it from the record does the transform then sends it to the log action so if your action does something it will be transformed before it sends that that's when the transform happens for reporting you mentioned specifically reporting um if you're reporting through flow designer but not for reporting reporting there's no transform function uh on reporting that i'm aware of that works like this so hope that answers that question the other thing i'd like to see that's hopefully on the roadmap i don't have any confirmation of this yet is a way to take that transform value and then save it almost like the old workflow scratch pad or some sort of temporary value that you can use later in your flow so you could say all right i'm going to get the c out of chuck and save that because i need that as maybe a suffix for a subflow or something of that nature which i suppose you could just do on the fly but you may need it in multiple places so it makes more sense to save it that's uh that's about all i've got for flow designer transform functions they're easy they're quick i invite you to take a look at them go into the you know what i didn't realize this before i just found something else out it tells you that both of these have a transform on them be nice if we could see what that transform was right here in the execution details wouldn't it otherwise we've got to go back here and say well what was that and there it is split get array first character that kind of thing i invite you to take a look and go through these get familiar with some of them so you know what's there date and time functions date and times are not the most pleasant thing to deal with on any platform but yet you have a number of things you can do here add time so if we need to add a duration or seconds or whatever date to string string to time subtract time we've got a number of string operations utilities get item from array get items get item from name values i thought i saw that before let's see what it says for the help this is not what i just put in says returns a value associated with a key from a map of name values where is the map coming from name of the key used output string value associated with matching key a map of name values but you pass it a key and a default oh okay so the data pill you put in needs to be a key value map apparently here's a key value map we've got that we did that some simple math we did that sanitizing shadow arguments complex data what's in here 2xml says serializes the input complex object to xml interesting so one of the outputs we had was a complex object i believe there's a two array one of these i can't remember what it was let me see uh input complex object we were fooling around with something and it said oh sorry i'm on the wrong gotta click these to see the help for that thing doing that first column doesn't help uh i want an output of a complex object i thought array was not a complex object array did not uh array was in string split nope the output without one of them was in here i swear it was you've got trim which i use all the time when i'm doing string manipulations in anything really anytime somebody puts in a string i want to make sure that there's no leading spaces or tabs or new lines or ending you can clean that thing up so it's character alphanumeric character to alphanumeric character that's what trim does is it cleans up the front and back of any string same as the javascript trim function okay that is about all i've got invite you to take a look at them very useful reduce the scripting capability or the need for scripting in flow designer so that you can get things done more effectively and you'll thank your future self at some point say i don't need all this script in my flow it's easier to build easier maintain easier to test so with that join me on monday the 28th of september for views and view rules again this is one of those things that i learned 12 years ago but either the added functions or we didn't cover it completely because there's a lot more to views and view rules than i ever remember there's a lot and i'm going to cover as much as i can in this session then i will be taking a break from september 28th to october 28th for the community live stream only because this demands a lot of my time to prepare and deliver and all that stuff because we need to focus on creatorcon remember creatorcon's coming october 27th so there's videos to make and demos and discussions and scripts and technical stuff happening behind the scenes so hopefully you'll be able to make it to that give us a minute of your time and we will see you at creatorcon so i need to take a break from this but i'll be back on october 28th with something i'm sure you'll enjoy about tying in scripted rest apis which i covered in excuse me august and service portal widgets which i also covered in august so we're going to put those two together to find out how to interact with the server via a scripted rest api and why you would want to do that so think about that i'm putting i'm starting to put some of these together that we've already covered so if you haven't october might be a good time to go back and catch up on some of those episodes you may have missed so invite you to do that until i see you again on monday or maybe live coding happy hour or maybe you'll hear it on the podcast thanks for watching go out share something join the community be part of that organization and i guarantee you you will learn and you will grow and you will become a better service now administrator developer builder whatever till then take care have a wonderful time and see you next time bye [Music] you