[Music] hi all and welcome back so in today's tutorial we'll be looking on very interesting use case and i think you must have faced in your organization also so for example a user wants to have a patch operation batch operation means what he wants to have a multi-update or you can say batch update or batch insert into a database okay so how generally the process goes for example if you want to insert a user into a i'm just considering a user but with your use case you can hit my default so you want to insert a user into a database so how it will be done is like you will be providing a record producer or service catalog will be asking certain variables to the users then it will go to the through the approval process and as a result as a result the data will get inserted into the database pretty straightforward but what if instead of one user you have to insert multiple records how a person can provide multiple record details on the server catalog it's not possible so what he will be what i will be recommending is like we will be reading through an exit so a user will be providing an excel on the service catalog or record producer and what we have to do as a part of that excel it will have a list of user data okay in our use case and we have to ins we have to read through each and every user and import it into a respective table or target table so this is the use case for us okay so i have created some flow diagram for the same means how exactly i'm planning to do that so according to my plan we'll be having one record producer okay that record producer will have a privilege to add attachment where the user detail all the user details will be stored now from this attachment what i want to do is like i want to import this excel data into a import set table from that imports a table we will run the transform map associated with the table and as a result of which the data will get inserted into the target table seems to be pretty easy right so if we look into this if we look into the entire flow what exactly we want first of all we want to we have to create a record producer that record process should have a privilege that it can have attachment okay then we have to create an important table and a transform map at the end so i'll be just creating a raw flow okay you can refine it as per ux your you uh your requirement okay what i'll be doing i will be just creating a raw flow just to get the data from the excel and import it into the target table which will with this user table so let's get started so this is my service now developer instance so let's start with the creation of the import set table so we'll be using a shortcut will not create imported table manually what we'll be doing will you be using data load features sorry it's loaded i guess load data so in load data we'll go and here we'll be providing an information for the imported table which we want to create so i will give it a name as user data if we create a table with name you underscore user underscore data now we will choose the file or we can call it access so i already have an excel for user table which has two users start name starting with that so i've deleted users whose name was starting with set okay and those other details in this particular excel and i will showcase you on this user table also and submit now the imported table got created now we have an option to create a transform map for the same so let's create a transformer i follow one nomenclature while creating a transformer just to get an understanding that for which particular source table which particular source map the transform amp is associated so what i follow i follow table name so which is user data space transform okay so we have created a transform map that so the target table will be the says user table pause so let's find this user table this is the table this is this sorted this refusable this is the one okay i will not go with all this validation copy empty fields and all the things i will keep it raw only i'll save it out and now as a next step what we have to do we have to i will use i will use the shortcut and i will use the auto map matching feed because i have extracted excel also from the user table only so it will have the same field name but if you have a different fill name that you then you can go with the manual mapping also so now you can see all the fields you have mapped to the free all the print map so we have created we have completed two stages one is uh we have created an import set table this is done and the second one we have created a transformer now what we have to do is we have to create a record producer so let's get started so i will go to record producer we will click i will just duplicate this tab out because i will require this this transform map so we'll go with the record producer and in record processor we'll create a new record provision so we will give it a name like user data import and the table so we will not be writing this particular record producer for this user table because we will not be directly inserting data into assist user table it will go y imports the table so the table name will be says data source for us which is the import set table so we'll select this data input set if you want to provide the description you can give this is the record producer for bulk set of user record i think this will be fine okay and i will save this out now this was a catch over here that will be going for data source table okay rather than this is the table and now as a part of next step we have to write some script okay before that what we have to do we have to create a privilege right provision that a user can attach the attachment so we'll be creating a variable let's create i will not create additional variables i will just create one variable and i will give it a name as it should be attachment first of all because we want get attachment as a part of this variable okay and what it should uh please attach the user excel something like this i will not yes i'm not thinking a lot for this okay and submit so now what we have to do now we have created a record producer we have created a provision that it can have attachment to it okay and let's suppose we got an attachment on the record producer now as a part of next step we have to create a logic that it will take this excel sheet and dump that data into and put the table so a lot of script will need to be written okay not a lot but yeah significant amount of script so let's get started and i wish i will i will go pretty slowly and i will explain what exactly i'm doing so first of all what we'll be doing variable new light record and first of all we'll be pulling the attachment out okay so this is under the batch this is a discord management table and here we want to find the attachment which is associated to that particular record so we will do sh er dot add query and we will be giving two things first will be tablespace id is a field on the attachment table okay which signifies which table it belongs to so we will give it which record it belongs to will give current dot get value oh we can give current dot this id let's let's give it like this i don't know why it's not giving me the auto correction the again what we have to do the second query we have to signify is like which table name so we'll give people underscore name what it will be will give it as sc underscore card and just go to item perfect now once we define the query we will just query it out now what start query now we have carried out now we have to check whether any attachment is present for this particular record or not so we will be looking like if not if no attachment is present if not s h e r dot next there is no record present for this it's pretty difficult if you are not getting um the suggestions right so if no attachments are present then we'll give simply uh add error message and what will be like you must attach the attachment i think this will be enough for right now okay and we will be doing current dot set abort action to true oh my god i have to type a lot i think set about action to true and i will try to return done if there is nothing nothing present then what we'll do right with this oh we already have a protection there i don't need to return okay now the next thing what we have to do and else okay now we we have taken care of that if attachment is not present now there may be the case that multiple attachments are present but we will be processing only one attachment right so let's check that also so we will be checking that if multiple attachment is present then we will be the system will get confused with meant to read right so we will try to import some logic for that also so what we'll do variable says attachment id equal to s gr dot get thank you so it's not giving me any suggestion oh because i made it wrong over here should we get unique value okay and now if press a gr dot get row count is greater than one that means it has multiple attachment attached to it then what we have to do we have to simply we'll do the same thing over yours i will not waste my energy on this and we will change the message you must attach what only one file at time something like this right so this scenario is also taken care what else can happen what else can happen ah we have to check the format also right whether if someone attach word document then we are screwed right so let's check for that also so variable we will give variable i will just zoom out somewhat so if you are not busy past format check equal to false let's set it as false by default and now we will check if let's say oh if i say here dot file let's go to name dot we will check and what and swift it should ends with dot xls then it's fine okay or what is as this end will be written boolean right so we have to compare it like equal to equal to true or i will copy the same and we will be checking for the other extension also for excel file which is excel sx equal to equal to true if a file name ends with any of this then that means it's a valid file okay and we will go with past formatting equal to true so that we can proceed with that but if it's not right if it's not then what we have to do again we will put the same error message i'm not adding a brief try catch or something like that you can because else the tutorial will be pretty long and we'll replace this out now what error message uh we should get the attached file should be excellent and not anything else i think this will make the user aware that what exactly we are expecting so this is done this is also k now once this check is done okay let's write our logic so now with the scenario if what was that flag pass check format that means if it's true then what we have to do so we want to import the data okay so let's add our info message first which is dot in just to un understand uh [Music] but info let's add add information for message and we will get the file looks good and okay this is them now we will be creating two variables one is variable we need two things one is imports a table and the second one is transform maps id so variable import set set table name equal to what is the name of the table there is a second wave variable transform map space id okay always give names okay if it's long also then it's fine then also it's fine but always give a name that if you come after one year to the code you will be able to understand what exactly you have written for so let's get the information what was the user table name where you underscore user underscore data so let's give you and just go to user underscore data okay and the transform of ssid so i will simply right click on this copy ssid and i will put it over here why we want this because we want to insert the data okay insert the data into the current data sources data source table so we'll be creating a record now we'll be setting the values for the creation of record now the current dot name will give and we can give any name user data import urd and then we have to give current dot import underscore set let's go to table let's cut name and it will be yeah you've guessed right it will be imported table name the variable which you have created at the top then it should be current dot file underscore dtri ri wheel method so how exactly we derive this return so it will be of course attachment i'm not giving it from my side okay this is already a fails on the imports table we are just setting those values and creating the record in that table so current dot type equal to file file and then what is current dot format to excel then what current dot g header that's got row we have to define right which row is a header this configuration are there while doing the data load but now we'll be creating it from the script right so we have to pass all this information and again current dot shade unless cotton number and we'll be giving first only equal to one and now will be like variable the task source id equal to current dot insert so now we have inserted data into data source now once this is done we need to move the attachment to the new record okay and how we can do that is like sa cr dot table table need equal to space underscore data underscore source script and then what we'll be doing variable update attachment now we have what we are doing we are setting the source table name for for this attachment to this data source okay so it will be set here dot update perfect so what we did we i moved we created data source okay and then what we are doing we are just moving the attachment table name to is data sourced perfect now this is done now we are ready to go now what we have to do now it's time to load the excel file into the imported table and everything will be taken care of transformer so how to achieve that so for that what we'll do like variable loader equal to new glide port set loader and and variable code that record equal to loader dot get code e o rt set current current and what is the first argument it should be input set record and current and after this import set record now we want to set because we are not manually creating out right so we have to create this record in the loaded state only that means the excel is through this excel excel is already loaded to the system right ideally it's already there in this assessment table but we have to create it like that so we'll do state equal tool voted record dot date now we have updated this import set record now the final step we have to run the transform map so what we have to vary but variable transform okay equal to new light import set for more worker new glide import set transformer worker and this particular class takes two input one is import set record dot society of input set record and the second one is transform map ssid now we have to just execute it out so transform worker dot set only true and transform worker dot start what it will do it will start executing the transformer when when you you get the button right transform so i got this code from this so i'm just taking the code from the that ui action okay so i'm just we'll start it out and then we can give message gs dot add info message because if we have reached this day that means everything is fine and we can give the user data is imported board action to pro and i think this is it i miss the semicolon over here so i will just give you a briefing about the code what exactly we have done so basically first of all for the current record we have find this attachment okay that data chargement then we check if there is no attachment then we send the error message if there are multiple attachment then we send the message that it should have only one attachment then if it's not an excel then we have order we send the error message let's see it should be excel only okay once that is done what we did we created an import set record and we loaded that x and we have attached that excel into imports and load data record okay we have created a import set out of it and then we run the transform pretty simple now i will save this out okay there are higher chances that it will get fed because we have done it in the first go and now let's give it a try before that let's verify this is the table so if i go to says underscore user dot test so here if i start with said ideally there should be no one the starting is that no one is there right now let's give it a try so try it you get and this is it's pretty raw right but let's give it a try so i will go click to add excel so we have this user accent it's getting uploaded and now if i submit so the script will run okay and oh i didn't give the tag url okay but let's see the data got inserted i will just didn't get inserted where exactly we went wrong so configure item um this seems to be good cgr dot update so now uh i have debugged the code out okay so and added as it was getting redirected to the data source table okay and we don't want that it means to some different table okay so i'd added subreddit urs on the failure scenarios and actually there was a spelling mistake okay on the on this transform of society so i fixed it out okay and now let's give it a try so if we go we try it now okay let's see the user records first so still there is no record with which starts with username said and we'll go to the record producer once again i will browse the excel sheet now this is the accession is getting uploading and if we submit it now so ideally now it should work so now we can see two messages it's the correct format okay starting import and user data import is started successfully so let's verify it out now on the user table so we'll go to the user table and if we refresh it now so the record got inserted from the data data import set or we can through transform map or we can call it through excel also so this was an interesting use case okay uh i've created i have just completed it out in a raw manner now you can based on your use case you can define the workflows or you can define the approval policies or whatever you want to do in order to achieve the same okay hope you like this video please like follow subscribe to my channel to get this kind of videos okay in the in the future also please drop comments so that i get this kind of ideas okay which will keep me motivating create motivating for creation of this kind of videos until then stay safe you