[Music] if I tell you that you can use Excel formulas and servicenow to populate data in a field with calculation does it not sound cool hello everyone welcome to SAS with service now in this video you will learn about one of new features introduced in Tokyo version of servicenow called formula Builder now what is a Formula Builder formula Builder is a tool that allows developers to write formulas that create calculated Fields with a simple no code syntax it is integrated with table Builder which allows users to search for functions and provides syntax guidance during formula composition some of the features of formula Builder are you can use formula Fields without writing any code you can use Excel like functions for calculations like sum multiply and if it provides syntax guidance for clarity when writing formulas you can also debug errors to help construct correctly formatted formulas let's see benefit of using formula Builder the major benefit of using formula Builder is no code where you can use Simple formulas rather than using JavaScript coding for arithmetic calculations or any other type of calculations formula Builder feature is available in field dictionary which you can enable with calculated checkbox and select formula in calculation type field before Tokyo version you will only see script option in this particular field formula buildup feature is also available in app engine Studio and you can use it with table Builder where you have section of using formula where you can select the field formula Builder also validates syntax if you misspell a column name you will receive an invalid update error when trying to save the dictionary entry it also checks for incorrect function names used in the formula field formula Builder provides a table which stores list of all functions which can be used in Formula builder for calculation and the name of the table is sys underscore formula underscore function now let's see a quick demo of formula Builder this is my personal developer instance let's see that table first which stores all different functions used in Formula Builder so I will just type here sys or I will just go to all and I will type says formula underscore function dot list if I will type this you have this table and you can see these are all different functions available which you can use in Formula Builder a lot of functions like average concatenate divide if these are logical we have logical operators as well like R so you have if condition you have logical operator like and and there are some other functions as well which you can use in Formula build now let's see a quick demo that how you can use formula Builder so for that what I will do I will create a demo table where we can use this formula Builder so I can show you the results that how exactly you can use formula Builder when you have to basically build or create anything in service now whilst developing the requirement for your customers and clients so in that case I will quickly go to tables and I will click on tables and I will create a new table and I will create a global table for now with global scope so I have clicked on new and here I will provide the name I will try to provide the name um as demo table and which is demo table I think I can or I can put formula Builder demo table I press tab I will extend this table from task table the reason is because I want to use some existing Fields as well of task table when I will show you the how exactly you can use functions so we might need some of the fields from the start stable as well I can definitely create but why don't we use uh existing one if you already have them in in the system I will not create any module uh so I will just create three fields so I will create a f b which is formula Builder [Music] field one and I will select the type as string I will select string here and then here I will put FB field two and here I will select this string type again now why exactly I'm creating these fields so that I can show you all those calculations with the help of function of this formula Builder so here I will put so these are two fields which we will use for calculation and where exactly I will show the output that will be result field so it is FB result field these are just random names I'm giving for this demo so because this is just a demo but once you will learn formula Builder that how you can apply formula Builder on any field then you can use the existing field you have in your system or you can also create new Fields as per the business requirement you get from your customers and clients and then you can use it so in that case I will just save so these three fields are created and they are part of this table what else I will do I will open this table in a list View for that I will just click on this show list so here is the table but what I will do I also want to open a new tab because that's how I can work so in that case maybe I will just copy it again and here I will just type list so this will open the list of Records basically the list view of the table which we have created which is Formula Builder demo table and what I will do I will just create a new record because we will see the live change in data so in that case I will just submit this as of now you can see these are all inherited fields from task table I will add those three Fields as well which we have just created in this table so I will go here and I will just go to FB so we have all these three Fields I will bring them in the right and I will also put it in the list so now you can see we have field one field two and the result field now how exactly you can apply the formula Builder so in order to use formula Builder you have to basically select a field where you want to see the output so in this case our field is FB result field so I have to open the dictionary of this field and what I will do I will directly go to here and I will just put maybe FB I have these fields and I will just open this dictionary of this field in a new tab which is right here so it is opened so I have to go to Advanced View because you will see that option in advanced View and that section is calculated value this is the section where you can see option to use formula Builder so just select calculated value you have to check this checkbox calculated so I will just check this checkbox and now you can see the calculation type you have script but there is a new option that is Formula so if I select formula it will show this field now which is Formula field which is again a formula Builder field where you can apply or write functions which are available in Formula Builder so for now what I will do we have three Fields overall this one this one and this one this is the output one and these are the one which for which basically we need calculation so we will apply calculations on those these two fields and we will get the output here let's say I want to do addition if there is a value here and if there is a value here I want to add them and for addition we have the function called sum so what I will do I will come here and I will just make it a little big so that you can see it so here I will put the formula which is sum it is the function it is the available function as part of formula Builder to add any values arguments which you will mention as part of the fields what kind of argument it needs it needs argument directly from the fields in the first field we have is FB field one so what I will do I will put here U f b field 1 comma U f b field 2. and I don't need to have I don't need to put any kind of semicolon or colon that's it sum and you need to put the argument which is field one and field two what exactly it will do it will perform the addition it will sum these two Fields the values available in these two fields and I am going to save this so dictionary is saved this field now has become a calculated field and which is also the type is Formula not the script one and if I go here and let's say I put any number maybe 50 and here I put maybe 20 and if I click on Save now if I will refresh you will see the magic you can see it basically did the addition and here it added it's did the sum for these two Fields the values available in these two Fields 50 plus 20. so that's how you can use formula builder in servicenow in Formula Builder you can use lot of functions to meet the business requirement you get from your customers and clients so now I will show you how you can use all other available functions in Formula builder for different calculations so starting with first function that is sum which we have just seen that is which will add the values available in fields which Returns the sum of all the arguments so let's try this again in my instance so I will go to my instance here as of now we added values available in two Fields why don't we try with three fields so we you can see we also have short description I will put some number in short description as well how I will just change this and here I will put 30 so we have this value 30 in short description if I refresh this you will see it will not change the output in result field we still have 70 which is the addition of these two Fields the values available in these two Fields how can I change this simple I just need to go to the dictionary but before that I just want to highlight one important point that you can see I can edit this field I can edit this field as well but can I edit this field which we just made as a calculated field answer is no let's try this see here it says security prevents writing to this field I cannot edit the reason is because now I have made this field as calculated field so I cannot modify this field anymore so basically any user you want so I cannot modify this so now I will go to here and I will go to dictionary again here we have some these two Fields I will add short description as well and then you will see the magic so I will add that short description I will save this now this time output will be different which is stored in this field and I will go here and I will refresh this let's see what happens absolutely now you can see the output is 100 the reason is it is adding value from this field this field and this field which is 30. I can change this right away so see if I change this to let's say 40 it will become 110. and if I click on refresh that's it that's how you can use some function for any kind of calculation you want to do in service now and that is also without writing any single code you can just use these functions and this is the first function which we learned where you can add different values from different fields you have the next function is subtract which Returns the result value after subtracting argument 2 from argument 1. so let's see this so we have this 50 20 I will go here and here I will put subtract sub tract and I will just remove this short description now let's see if I keep this do I get any error uh I it does not give me any error but let me save this will I get any error yes see here here too many arguments for function subtract I think this is what I was talking about about syntax validation so I will just remove this short description because subtract needs just two arguments so field one field two and I'm going to save this now you will see that we will not get any error anymore so I'm going to save this it is just saving the record and now I will go to our list View and here I will refresh this and Magic will happen the C here the cow count was the number was one at 10 but now it is 30 the reason is because it is doing 50 minus 20 that means subtract 20 from 50 and it is giving you the output that's how you can use subtract function as well after completing all the functions most of the functions available in in service now for formula Builder we will also try to use some complex functions when I when I say complex functions we will try to use multiple functions together the next function is multiply which Returns the multiplied value of the arguments so I will go to my instance here it will do 50 into this 20 and then it will give you the output which is 5 to the 10 which is thousandth so I should get 1000 value here and just go here and I will make it multiply I don't have to change anything one thing I want to show you about name of the fields so let's say I am deleting this F if I will try to save this you will see the error see feel this not found in this particular table as I said that that field has to be available in that field only because you have created so validation will happen for that particular field as well so here I will just put F again and I will save this this time I will not get any error because field is available in this table where I am applying this formula Builder so I have this multiply I will go to this list I will click on all and you can see output is thousand which is showing in this field where we have applied formula so that's how you can use multiply function next function is divide which Returns the questioned value question Yeah question yeah Q question yeah light camera action the next light camera action next function is divide which Returns the questioned value after dividing argument 2 by argument one this is very important statement it divides argument 2 by argument one so if I go to my instance again we have this 50 20 fifth that means it will do 50 by 20 and that's how it will give you the result so let's try this I will come here and I will just type divide and I will save this and we will see that what kind of output we will get so here if I click on refresh let's see yes absolutely you can see the output is 2.5 because it is 50 divided by 20 and this will be the output so that's how you can use divide function as well with formula Builder and which is very useful as well next function is lowercase which converts the input string to all lowercase characters let's take a look so here this time I will not use these two Fields maybe I will use short description so but before that I will just change the value here which is this is my demo you can see everything is in capital I will just save this and I will go to the dictionary and here I can put basically lower case and I can put the name of the field from which I have to pull the data make it as lowercase and show it in the result field which is this field so here I can just put short underscore description and I will just save this it is saved I will go here and I will just refresh this and yeah we can see the result This Is My Demo but it is in lower case that's how you can use lowercase function with formula Builder the next function is uppercase it just reverse off lowercase it converts the input string to all uppercase characters let's take a look so here we have this this is my demo we have this one I will just change this so I will make it this is my demo all in lower case I will save this and you will see the magic here if I reload this refresh it is again um let me check why it is not oh we have not applied the function yet so here I will put upper case and I will save this and I will go to the list and I will refresh the list absolutely you can see that it has converted all the letters into capital letter which is like this this is my demo and you can see this whole string is in lowercase but here it is an upper case that's how you can use uppercase function with formula Builder next function is title case which converts the input string to all title case characters I will show you how you can do this so you can see it says this is my demo it was all in capitals but what I will do I will just put it here put like title case which is in short description it will basically take action on short description field so I will go to the list now because we have applied the function I will click on all which will refresh yes you can see it has changed this is this is my demo so it has four words and first letter of every word is in uppercase so that's the feature you get from title case function the next function is time diff which finds the difference between two days for duration field and I will say this is very useful function which I think almost every developer will use and the best part is that now you can do or calculate time difference maybe those time differences from two different fields that you can calculate without writing a single line of code how we will do that let's take a look so if I go here now in this case I will basically use date fields so for that I will go to the record where is our record okay I will do one thing I will just open this record um because I have to add those date fields which are not available in the list right now uh I will just maybe configure form layout and I will just add those date fields so we have this actual end on start both our date and time fields and I will just click on save so now you can see we have these dates for now just to show you the demo I will just select this date we have this date and time and I will just add a few minutes so maybe that'll be 59. uh it should be 59. so basically it is adding H the difference is eight minutes and we know that so it's clearly seen here that it is eight minutes of difference but we want to see whether this difference is visible in our system by calculation so in that case I will go here and let me just check the name of these fields works and and we have this work start I think I should have reversed it so start should come first uh but that's fine maybe I will change this that is better and I will make it one and I will save this so work start work end and if I go to the dictionary I just put time diff and here I have to use those two fields so I'll just put work start and here I will put work and and I will save this and let's see what happens so overall we know that it has eight minutes of difference but we want to see that here it's the same field result field which will show this output so it will calculate and show you the output so let's refresh this if I refresh yes you can see it is showing you eight minutes of difference so that's how you can use time diff function in order to calculate duration between two fields or basically the overall data which you want to populate as part of your requirement you get from your customers and clients next function is replace this is also very useful and important function it replaces characters in The Source string with the characters in The Target string let's try this so I will go here this time we will use short description here we have this this is my demo and I will change this to this is SAS demo how I will just change this word how let's see that I will go to the dictionary and here I will just put replace and I will first put that where exactly it has to apply so I will select the field which is short description I will put comma here I will put uh the string which I want to replace and that is my then I have to put the string which I want to populate in place of the uh my string so I will put basically SAS and I will save this I hope we don't get any error uh okay it says my not found in table it says my but we are not showing any uh short description uh let's see this is my demo it is not visible short description maybe I will just I think it's double quotation basically it's saying that it is not able to find but we have this this is my demo uh I have this let's save this I hope we don't get any error now it is just taking a little bit more time yes you can see that we did not get any error it is still loading so you can see that we did not get any error but will we get the output so this is how your syntax would be so the field name then double quotation the letter or the word you want to replace or a string you want to replace and then you have to put the string you want to basically override so in that case if I go here and if I refresh this let's see if we see the magic yes you can see it says this is SAS demo that means it replaced my word with SAS that's how you can use replace function with formula Builder and servers now next function is now which Returns the current date and time of the instance in ISO format let's try this this is also very interesting and useful function so I will directly use in this field because we want to see the date and time so I will go to the dictionary I will just put now and I will put the name of the field where I want to show this I think we already have short description so we can do this as well but that's fine I think we have this result field so we should try that so this is uh SAS demo um I think in that case if I want to show in the same field then why would I select any field not at all it will be like a function that's it I will just remove this and save this we don't need to give any argument not at all and you will see the magic yourself just save this one and go to here click on all and you will see the magic yes so here it is populating current date and time which is a different uh time zone but you can see that it is Auto populating and I am 100 sure that a lot of developers and I would say almost all developers must have written different code for diff these kind of calculation specifically current date and time because servicenow also provides API to calculate current date and time even if you want to do some date calculation day if you want to calculate the difference between dates and time that is how you must have done with coding but with these functions do not write you don't need to write any kind of code which is really great next function is concatenate which basically joins one or more input strings into a single string now this is also a great feature or I would say function which you can use or you must have used as part of your coding in order to Club the string but now you can use just this function so let's try this so what I will do I have this field one field two and we will try to cup the club down in that case just think these are like a name fields like first name or last name and we will populate that here we will also try email address so let's try this with name first here I will just put like this so we have uh we have concatenate I will just write Con cat enate that is the spelling we have and here I can write U f b field one okay and I can use v u f b uh field two that is good and what I will do I will just click on Save I'm not putting anything else right now because I let's see the output and then you can change some data in your formula field to get the right output again so let's go here I will just give the value here as let's say SAS width save and it is saving and I can go here and I will just give another name which is SAS with servicenow let's see what we get here so I will click on this refresh absolutely so you can see that it says SAS with service now it clubbed those two string now but important part is let's say I want to have a space between SAS with all these different different words I will go to the dictionary here we will do something I will just add space like this let's see what happens I'll click on Save I shouldn't get any error let's see yes we didn't get any error that's good I go here and it's time if I click on all you can see that we have a space now SAS width service now now let's see I want to add more Fields like I want to maybe create an email so in that case so let us say user is putting if you if I give you the use case let's say user is user is putting uh his or her first name and last name and you want to generate email address automatically uh like for your particular domain so you can do that how you put field one and I will do one thing I will just put whatever string I want so you can see it will do dot this comma I will just do in a string add let's say SAS now sasnow.com and I will just save this if I will save this and if I go to the list View and you will see the magic absolutely SAS with DOT servicenow at sasnow.com so that's how you can use concatenate in order to Club the strings you have it is really great feature which you can use that is also without any code next function is length this is also very useful function because you have to sometimes calculate the number of characters you have in the string so basically this function Returns the total number of characters in the input string let's see how exactly it calculates so if I go here we have this this is my demo we want to basically check the length of the characters in this in this field so in that case I will go to um here and I will just do length and I will put the name of the field which is short underscore description that's it I will just click on Save and you will see the magic if I go to this all refresh here this time result will be different it will not be email address because we have changed the formula now it will show the number of characters we have in short description so that is 15 so basically one one two three four five six seven eight nine ten okay one two three four five six seven eight nine ten eleven twelve thirteen fourteen fifteen because it also basically consider space as a character and that's the reason we have the length of the string is 15. that's how you can use length function as well in order to calculate length of basically a particular string next function is average which Returns the average value of the arguments so let's try this so this time I will just provide the let's say 50 and here I will put 50 so we want to know the average so in that case I will go to this dictionary and I will just write average that's a spelling I have to use and I will just use the name of the fields so ufb field underscore one u f b field underscore two and that's it for now and I will save this when I will save this the output will be changed so it is saving and then I will go to this it is still saving because you can always only see the output ones this field dictionary will be saved with the new formula you're applying so it is saved and if I refresh this absolutely now this is the average we are calculating average from two Fields where value is 50 it is 50 50 divided by 2 and the average is 50. that's how you can use average function as well with the help of formula builder in servicenow now these are all major functions which you can use with formula Builder and basically use in different kind of requirement you get from your customers and clients let's see that table again so if I go to this all and here I type sys formula and I just type function dot list I just want to see the list of available functions we have so we also have these operators so like and we have F condition logical so here we have if um we have maximum and I think it will show you the syntax as well let's let's see that so maybe we will see for uh uh Max just try to see that what kind of syntax you can use because that is the main purpose of this table as well that it shows all out of the box functions formula functions available and you can use this you can see the description so it says return highest value in the specified arguments so okay it says Max that means choose which which particular number is the max one which here it's 20. that's how you can use and if I go to Min Min will show you the minimum number which is from the argument we have already used multiply now and I can also show you the syntax again see it says syntax which is input no arguments are required for this function see it says no arguments are required because you can use this in the same field so I will close this we also have or so we have and or if so logical operators and condition as well so you have this or this conditional expression functional call variable example or this this okay so that's how you can uh mention here so what I will do let's try these uh with some complex calculation as I mentioned earlier that after completing this section of talking about all different function available functions I will just try to show you some complex functions as well how you can use it so let's go to that dictionary again we will just try to make a random uh random complex functions so what I will do if and that's the important thing if ah you I think let's go to f as well I just want to see the syntax so we where we have if here's the if because you can if you if you're not sure about the syntax you can just come here if conditional expression statement when true statement when false okay so you have to put the conditional so I think there should be an example see we have an example uh case dot incident dot count is greater than equal to 5 then high and medium okay when false so you have to provide conditional expression then you have to provide the statement when it will true and again you have to provide the statement if it will become false if instant count is greater than 5 then you have to put it high if it is that you can just put in the medium that's how you have to return let's try this so what I will do if uh you uh befold is greater than greater than 5 in that case you put um greater than five I'm just showing the example and here and I can just put less than five that's it and if I can just click on Save let's see what happens if I come here if I refresh this it is greater than 5 for sure this field you can see it is greater than 5 so we are getting this right result uh I can basically add more complex function it is just the one let's try more so if it is greater than 5 in that case I will just do one thing if this is greater than 5 that do one thing do some yes do some but some of what I want to do some of this I will just do like this ub1 and here I have two and if it is a less that in that case maybe divide and I will just put I will just copy put it like this or it's better multiply yes let's try this so we should have this closing bracket as well um let's use greater than this sum this multiply this and that we have this very good I think it looks good let's save this if I save this will I get any error I don't think we should get yeah see we don't have any error that means syntax is completely fine and I will come here if I refresh this see the magic if this value is greater than 5 then do some of these fields if it is less than 5 then multiply I will show you that as well I will put it three if I save this output should be 300 now no 550 it's 150. absolutely see the magic so that's how you can use complex functions multiple functions as well and you can build your own basically formula with basically this is the Excel formula I think it's kind of pretty much straightforward and easy as well and you don't have to think about any kind of code it's pretty much straightforward like you do in Excel sheet so thanks for watching this video please like share and comment and do not forget to subscribe to my channel to learn more about servicenow