[Music] hello and welcome to creator toolbox this is a show where we help make sure that you have the tools you need to be an effective builder administrator and developer uh before we get started uh let's do some introductions we'll start with the only other person here who doesn't really need an intro but we'll do it anyway wow what a setup what a setup hey my name is chuck tomasi senior developer advocate at servicenow been here since 2010 was a customer for a couple before that and history of computers and i.t and development way back into the early 1980s uh it is it is my pleasure to be here we've got some fun stuff to show you that kept coming up in the well i'll save the topic after you do your introduction go ahead brad all right my name is brad tilton and i am a developer advocate for the developer program here at servicenow i have been developing on this platform since 2008 i've been a customer a partner a trainer and an employee and i'm i'm excited about today's show because it's a topic that i know like absolutely nothing about i haven't actually touched it yet so i'm excited for us to get some hands on it you will be surprised that that this is like a familiar friend that we've seen around the platform that we're going to use it a different way i when i started digging in i was like really is that all okay so if if you what we're talking about is report calculations or report function fields and then suddenly the light bulb goes on and says ah i've seen that before i may have even played with that before so we were doing the uh quebec rollouts and talking about it at developer meetups and that slide kept coming up and saying i haven't touched it yet but i'm going to get to it this was our chance to get to it so we want to introduce you to another one of the features that came out in quebec that i think you're going to find very helpful in the way that you build these functions and the way that you maintain them and expose them and i'll do a couple of examples and then we're going to do some exploring this is not completely scripted this is not completely demoed but i wanted to set up a couple of success cases for you and then we're going to go off the uh the beaten path and explore some of our own and and say well what about this or how about that or let's try this so we're going to explore together and hope you can happy that you're here to join us all right we have any announcements brad do we have any announcements to start it on before we get started that's a great question uh knowledge registration is live if you haven't registered for knowledge and and and you're also watching this that shouldn't be a group of people that exist so you should be registered for knowledge if this is interesting to you uh because we have so much more content at knowledge so that's uh that's the big one i think anything else we've got an episode of breakpoint tomorrow with jason mckee about creatorcon the what you'll be seeing and what's new and uh just it was a lot of fun it's a it's a great discussion so jason's always fun to have on the show he's returning we had him on last fall for the fall creator con this time he's going to be telling us about the one that's coming up in may so stick with us for that if you're not already subscribed devlink dot sn break dash point i know that's a mouthful devlink sn breakpoint put some dots and dashes in there you'll get it all right with that said what do you say we get started on exploring function fields and reporting let's do it let me do a quick screen share all right in three two one there we go hopefully we can sync that up fred is pushing the buttons today and i've got my pdi and what i i started building something out for another project for a charity contribution so people would pledge some amounts so here you can see jason roy has pledged 100 something could be sheckles could be donnari could be dollars we don't know it's 100 tootsie rolls let's says and servicenow being the generous company that it is is going to contribute and match that amount so when we create a donation record or something else we want to know what that total amount is in tootsie rolls so furry friends maybe it's dog treats i don't know but that doesn't matter we're donating 100 and we'd like to make a simple calculation to do to just double that number now we could either add the number to itself or we could multiply times two but i'm going to jump over here to reports create new if you've gone through a basic sysadmin course this should look pretty familiar and let's call this uh donations right we'll get that from the pledge table which sounds like i'm doing some housework now that i think about it not that i was promoting any particular kind of furniture polish i never thought of it in that context before let's do next and i'm going to stick with a list just to show you what's going on here so this is your typical list and when i go to next on the configure section notice that we have this configure function field woohoo all right i've still got choose columns which is all the columns that i've already got in my table but this configure function field says hold on you made some changes the report in order to calculate we should save first because i haven't saved anything yet so this is this is being a good brad on my shoulder saying save save often that's right i think you're going to always have that stick that you're going to be labeled as the man who saves i just get too nervous so let's call this um a donation field and you're just giving it a label how it's going to report on how it's going to show up on that report now i've got lots of different return types in here dates date time duration percent large hole number whole number true false honestly i haven't played with a lot of these let's explore those a little later in the show but it gives you a little usage over here now these are going to simply determine what that how that value is represented on the report so if i say i want a whole number because i had a whole number for the amount field on the report i can then add an operation and these should look familiar because they are the same thing you will get if you search the documentation on report on function fields and we did a little bit of this and i think it was live code just a week or so ago when we were putting together some things for the karaoke app we introduced a function field into that so you've got an add subtract now function fields happen at the database layer we are taking advantage of database functions so they're very fast it's not like a business rule the the whole reason we want to use wrong tab i use a function field is so that we don't have to maintain a separate field with a business rule or a flow or something that says oh the amount changed therefore i must go and change either the calculation on a calculated field or i need to go run a business rule to update this other field it it keeps it as its own distinct field running this function easier to maintain and performance driven that's one thing we all need to be aware of when we're writing applications is where can i get those little fine tunings out of performance and that's why function fields were created so did this table already have a function field on it and you're configuring that or this adding a new function field this is adding a new function field and you you kind of uh gave a hint at what we're doing i thought this was creating something specific to reports it is actually injecting a new field into the table so it that's the part that's the secret that got me a little while ago so i am going to let's just do a multiply and you get this little function builder this is what's going into the function part of my function field so function fields are on the dictionary for that table and if i cursor here i already selected my operation but i can start adding fields with this nice field picker and i say i want the amount and ordinarily if you want to multiply by two you could put another field in here you could say i want the amount times number or whatever but if you simply put a 2 in here it says right here function has returned an invalid result well i'm not ever since i was about eight years old i always multiplied times two and got a good result not sure what's going on there so i discovered that he put quotes around it and it's happy very unintuitive but that's what works so let's just quickly save that now that i've defined the function field it doesn't automatically show up on the report so i have to choose columns and looky there there's my donation i will put that on the report somewhere next to the amount would be nice and you have to hit refresh and this would be a good time to save two so you see 100 times 2 20 times 2. this is checking out it looks pretty pretty good that was easy multiply times two you could also do an add amount comma amount would have done the same thing yeah this this is cool you know one of the uh one of the pieces of feedback that i think we got around functions function fields when they came out were it's still you know this is really good functionality for somebody who maybe is not a developer and who is just looking to run some reports and and do some you know they just want to change some things on the reports uh similar to excel and you really had to give them al almost you know some sort of delegated developer or admin access for them to be able to add the fields and then they have to kind of know how the function field uh you know how the method names are working and all those things so you know being able to do this with all the pickers and in the front end is a nice nice way to enable more people to use these definitely that little builder window is going to help a lot of people okay let me take now obviously i could go to a single score i can sum up the donations based on add a filter of status complete whatever you want your reporting skills go beyond this but that's the way to build a quick simple function if i go to configure another function i can see i've got my other one listed here let's create one more uh i have no imagination on this one so i'm going to call it foo and what i tried to do and failed miserably was try to create a date field because i'd like to take um the date and and add say 30 days to it maybe we don't actually recognize the donation until 30 days after the pledge and simply doing 30 of course gives me an invalid result doing 30 here is it adding 30 days 30 minutes i really don't know it doesn't do a whole lot of date time operations other than day of week which returns a number i'm not sure how useful that would be on a report but um i'm still exploring some of these functions and some of these data types but maybe another one would be um i i also tried to put in some comparators let's just see what happens to foo when we save that we save it and let's put it on the we'll do one more save just to be good let's put foo on the report next to donation and run i always forget to run foo has no value in it so it doesn't know how to add something to a date or i'm doing it wrong maybe i should say i don't know how to add something to a date here's another thing to watch for yes thank you before you go into configure function field save because maybe we take foo and say we don't need that anymore we've got a deactivate button down here that deactivates the function field and it says hang on it's used in more than one report you want to deactivate it anyway i'm saying yes i want to deactivate it and i will show you in studio what that did to my pledge table not the line item table pledge table i have let's add the active field to my list layout somewhere that'll be good foo is now active false donation is oops didn't mean to click in that but okay we'll show you this way too donation is active true why do i care well here's what happens when you go back to the report and you say configure function field notice foo does not change show up on the list because it's been deactivated if you try to create another one called foo let's make this one a whole number that is the length of the charity i'm donating to i don't know why this would be useful but hey oh it created it because it's a different type all right that didn't work last time it said hey you can't do that because there's already a field called foo interesting let's go see what happened on the table we are clearly in uncharted waters today close that refresh that and i have a foo false what did i just create what was that that's it oh food changed to a date oh so it apparently but it's still active false all right you're going to have to watch out for that so if i choose columns there's no foo i can't choose and go wait i just defined it did i not configure function field you may get into this crazy place where you said i just created a field called foo it was a date so watch that active field if you deactivate something it's still on the table but it's not going to let you play with it and sometimes it'll say you already have a field called that and you're like no i don't i don't see it odds are you or somebody else created it and now it's inactive so if you've got those people out there that are happily reporting reports and they change their mind and they start running into these situations they may call you and you have to jump in the dictionary and say what do you want me to do deactivate it or blow the old one away i mean reactivate it or blow the old one away so if i said this should have been a whole number this is my old date field i'm not sure what it did maybe i did something wrong let's try that again i don't know i didn't catch it whole number of charity save function field successfully created configure function field doesn't show up studio refresh huh it didn't do anything it didn't give a error either that's even scarier are there any errors in the uh the system log and the logs anywhere let's find out um don't know if this is related to me or not but there are warnings i'm not very good at stack traces have never been unparsable 2051 unparcel 2051 not sure what that means this is probably every record it's trying to display that on who knows that's that could that may or may not be our issue so keep an eye on that dictionary it could get ugly but i can either like i said reactivate it or delete the column and do it again as a as a whole number i'm going to take a different report just to show you we still have our donation function field defined here but if we create a report on a different table let's go back to reports view run create new and call this one fine items with amount i have another table out here called line item kind of like a purchase request or you know any any kind of thing that has line up it has a quantity a price a skew and a description well i would like to take quantity times price and make a new report column on it so let's do this on table line item not gift item where'd it go i didn't do this one yet ah dirty line item next uh list is fine next choose columns sure let's let's put these next to each other that's good enough i don't care we're not we're not out for production value here configure function field save and continue always notice donation doesn't show up that is a function field on the other table so it's not holding these in some sort of library where you can interchange them because it wouldn't make sense if you said i wanted say amount times two when i don't have an amount so let's create a new one this one is going to be called when we got price which is a unit price and a quantity sure we're going to go for a floating point number because the price is a floating point number or decimal does it give you i never really understood the difference between decimal and floating point number but i do know that decimals have an issue with um flow designer huh i don't know given a choice go with floating point see here's an example floating point 10.1234567 decimal okay the decimal precision depends on what your localization settings okay two decimal points floating whereas this one says more than two all right which may be handy if you're doing division but i'm simply doing multiplication yeah i was trying to set up a demo with um flow designer and my field was a decimal and it wouldn't let me drag the data pill where i needed it to to say hey is this greater than a hundred dollars i need to do a comparison and it went um now maybe that's been addressed or not but i've kind of been i've been uh what's what is that habit that you're taking when when something doesn't work and you just stick with it when it does work yeah so let's do two fields we'll say price times quantity and that didn't work okay it jammed them up next to each other wherever the cursor is that's where your field is going to go it says this looks good i could conceivably continue to go times price too many yards for a multiplayer it will tell you so that's a nice little builder there are some of these operations like concat that you can put on an n number of these things so i like the example concat sysid dash number dash name and you can just keep if you wanted to make your own not not a display value but some sort of you know part number based on some other fields you can do that here with the concat i did play around with that one that's a very nice feature so let's add our amount to the report put that over here somewhere and hopefully it will do our math properly after we refresh come on chuck stay with the game amount 10.5 which is 1.05 times 10. 39.06 1752. notice it's only giving us the amount of precision that we got so the question becomes can we change that change the if you change the output oh it doesn't let you change it later oh bummer makes sense yeah i it is it is a data type right in the dictionary and changing data types on your dictionaries is not a good thing to do there are a couple other things yeah go ahead i was just going to say would you have a question about um can you create your own custom glide functions not that i know this is the library from the docs page add subtract multiply divide concat date diff day of week what do we got about 10 of these things uh that is what we're going to get and then there's three common ones can we do multiple operations in the same that was one of my outstanding questions that i wanted to explore i don't know i don't know so i think no but i we should try it yeah i didn't see any examples of it but that doesn't mean you can't we're going to try now so let's take the amount and let's call this like discount so i can either let's before i create a new one i get i attempt one we'll try to do this in one field attempt two will take the output of one field and use it in another how's that yeah and then so let's configure this so this would if we did like uh i'm sure we can't run just like a javascript in there right like instead of quantity i don't think so because this is a function field it's uh well i don't know i think this part makes a lot of sense it will tell us if the if the syntax is wrong so let's do like another multiply on here multiply field multiply is not a supported function dot walk field quantity dot multiple not supported scripter and script so it's telling us interesting it's telling us some very interesting yeah it's it's like anticipating what we're trying to do sorry if one of those fields was a reference field and we tried to dot lock it i wonder if it would give us a an error like if you were if you were like dot walking um the employee field from the other one let's go let's go do that so let's do view run open up our donations configure function field and let's create a new one hold i don't know why you would want to know the name of the length of somebody's name but let's return a whole number of length uh you can't dot walk in here notice it doesn't say show related fields dot name dot walk field employee dot name not supported interesting and i i have to wonder if that's because of the way it's implemented at the database layer yeah i mean i i think it's i think it's nice that it's telling us it's not supported and not oh yeah working on it yeah so let's go back to our other example thing what we're working on line items with the mount i want to see we obviously have a difficult time doing two glide or two glide function operations in one so let's call another one discount amount make that a decimal just for fun and let's do a multiply the amount does it recognize it it doesn't recognize another function field oh that's a bummer what if we put it in anyway amount times zero point oops try that again 0.8 ah a little difficult to put two things together that's what i wanted to explore interesting yeah i i haven't found a use for a true false either i was thinking so so what if you had a field and you called it big spender and it was if a certain you know if uh the donation table if the donation was greater than like a thousand if you return true if not what what function would you use to do that though well that's a good question is there a uh greater than no because it's all it's it's doing things to other things that's true so i i thought of the same kind of thing i said all right let's let's call this long name oops sorry not sure how that happened let's call this long name and we'll return a true false because i did try this before let's get rid of that and say our operation is the length of the description is greater than 10. oh i did not okay it says the syntax is correct let's see yeah that's what i'm saying dude let's see [Laughter] my exploration the other day with trying operations was not that good so we'll put in long name next to description and it says i think it came out as all false false false false false false false false this is the longest one one two three four five six seven eight nine 10 11. that should be true did i count right yeah counter right you got it we could always change the function too to say five so i don't know if the expression was correct but there's something else going on there's still a few things about function fields that i'd like to see but i don't know how to do it let's go back here maybe let's just try that it see it says the syntax is correct yeah uh dhruv says he wants to try putting parentheses around them oh that's interesting to nest them see if that works okay so it would be multiply and then in here multiply price we'll do 10. like that oh ho it knows that let's see actually let's let's go back to our discount we want quantity in here so price times quantity will give you an amount and then like times point eight zero point eight you get a twenty percent disk oh it likes the syntax let's see oh yes update it interesting oh nice i'm liking this better every minute okay save just for fun and run it worked it worked it worked ten times two cents a sixteen uh times is twenty with a twenty percent discount true wow you you get the special mario coin yeah okay i think maybe he knew it before he may be one step ahead of us on this one [Laughter] so just to review you can nest these it's just the the first argument will be another function now given that i i still don't know about the comparator that we do on like long name if that helps us here but it might also help you put together things like um substring and position those often work together can cat coalesce takes the number of comma separated fields as input and returns the first non-empty value i don't know if i like that name coalesce because we're dealing with import sets but the rest of these and day of week what could you do with that is it returns zero uh one through seven but it does offer you a second argument to say my week starts on monday or my week starts on sunday which is very nice okay so yeah mathematically this isn't too bad i mean you you could do some easy stuff like on this line item report and say here's how much you would have been charged here's your discount and the discount could come from you know another field obviously you want to make that dynamic more than saying 0.8 so spandon had a question about uh who can do this who can create function fields i i looked it up and it looks like there is a function field admin role that oh really someone where they they have the ability to create function fields admin nurse during reports create a function field before you begin roll required admin function field admin each function field requires a label return type and definition the definition consists of operating all right i think dhruv is our hero today anything else you'd like to try brad i now you've got me thinking about the true false how i know possibly populate it maybe could we try returning a uh a one or a zero and you think that would uh i don't know that that would be useful like i'm just trying to think of how we could actually return a true since none of our options oh i see what you're saying because the definition of this thing did say a time field okay it's a boolean active equals true or active equals false i i mean what if active was already a boolean field why would you do that that let's go back to yeah i i wonder if it's like a you know maybe we're gonna get some more functions in a future release and it's just setting that up i i have no uh no future knowledge of what's happening in this area let's maybe you're right let's try this add one to something what are we adding to let's add something that here add this to the quantity and we'll call this long name is a stupid field name now but we're going to add that and return a true false and set the quantity on some of these can you do list editing in a report yes you can good run okay so if the quantity was zero we added one it is a zero or a one okay that's important to note because at the database layer boolean true false fields are stored as zeros and ones we know that when you when we do a glide record query and you get back you do a get value on a boolean field you're going to get a one or a zero as a string which is really annoying so i can't think of a use case for that one right right now it makes me wonder if you could then chain some of these together yeah i don't see any way to do boolean logic with ands and ors though okay these are these are simple these are quick i don't know if we'll get more in the future haven't heard but it'd be nice so i don't have a whole lot more to say about that there they are what they are we've played around we learned a little bit so i'm gonna finish the screen sharing and we'll finish this one up all right my screen went way over there for whatever reason zoom you amaze me sometimes there we go so thank you very much for joining us and exploring and absolutely to the chat for for uh kicking in some ideas and contributions that's that's why we love live streaming with this group if you can't if you can join us on tuesdays 8 a.m pacific time 11 a.m eastern or whatever that is in your time zone and we will be here each tuesday except the last tuesday of the month which is now tech now tuesday so every tuesday morning you've got something to watch live join us anything else uh i think that's it for me thanks uh thanks everybody for joining us uh i learned about these uh function fields through reporting i think this is gonna be a great thing that you can kind of give out to some of the people that maybe aren't admins or developers in your organization and help them you know do some better reporting and get more value out of that so uh yeah all right well thanks everybody and we'll see you uh again on friday for live coding happy hour see you soon people you