welcome back guys in this video i'm going to showcase the capabilities of on fallen insert transform script before moving on to the implementation part let me just give you a small overview of what foreign insert is all about so on file insert transform script refers to uh in a transform map specifically whenever you are creating a record in a table which is not your target table but a table which is referenced by your target table so let me just show you how this works so for example if i am importing data into my user table so my target table in that case is my user table but if i talk about a particular reference field on this user table for example department so department is a field which is referencing some other table that is cmn underscore department table so in this case if a record is getting inserted into cmn underscore department table while an import ran to import data into the user table then it would be called a file insert because it is not directly inserting into the target table but inserting into a reference table as well so if we want to update anything on this particular table while data is getting inserted into it and inserted into the target table then we use on for insert scripts so what all i can do with this script is for example if i want to update let's say a particular field of any of the department for example if let's say this department is newly created so based on the import which is running to import data into the user table only the value of the field which we specify in the import will be updated using that but if we want to update any other field for that we will have to use script or we will have to use the on-file insert scripts so i think that would be more clear once we take a look at the implementation so let's jump on to that so i have already registered an event which i'm going to use ahead in my scripts so [Music] this is the event name which equated today so let me just copy this and i'm going to head directly to the transform map which was created to load user data load users is the name of the transform map which i created earlier today and this is the transform map where i'm just importing five fields for the user table target table is user and for department field map whatever is the name of department coming into the imported data i am populating in the name field of the department table so now i'm going to head to the transform scripts tab and i'll click on new let's change when to one file insert and whenever we are importing data into a particular table and there can be multiple fields which are reference fields so in that case you might want to do different different things for every other reference field in that case it is a recommended exports to always specify the name of the field for which you are planning to run this script so i am going to mention department over here and this is the place i am [Music] going to figure my event so the second parameter would be action so so action has two values either insert or update which specifies whether the record is getting inserted or updated next parameter is value which specifies the value which is coming in in the foreign table that is the reference table and this is also the first parameter of this event and the fourth parameter this of this function of this api and which is also the second parameter of the event we will see how this works and we can also plan to make it dynamic but for now let's just proceed with the hard coded value which i have not had coded yet but i'm going to show it to you moving forward so let's just save this event script and once this event is triggered i want to run a script which i am going to write in a script action let's click on new and since it starts with you i'm just going to head towards the bottom of the list update department so this is the function i'm going to call next now the reason i'm creating this function is let's assume use case over here so whenever a user data is imported if the department does not exist in department table a new department will be created i also want to update the id field for that department so i can do that using this script which i'm going to write right now which is otherwise not possible directly because my target table is user table not my department table all right then i'm going to add a query and i will be looking for the department name which just got inserted so the field name is name this is the name field and i am going to look for the value which i passed as the first parameter of the event so i can access that using event dot one and let's just use two string if that value exists i am going to find it and update the id for data card so what next dot ids equals to i'm going to give a random value to the id okay so this is the hardcoded value which i was just talking about a couple of minutes back now let me just save this script and i'm just going to load some data into the user table let's see how it goes and i have already created an excel sheet with some data username is bruce wayne and the department is batman so id we are not using as of now we'll see how we can use it let's have to load data this is my existing table open submit transform transformation completed so this is the user and it is created this is the department [Music] and to my surprise it looks like the id has not updated that's strange so let's go back and see if there is an issue with the script it should not be the case update department okay so this active field is not checked now let me save this record again and i'm going to delete the existing data which was just created so that we can run it again we go to more data and load the data again fyi i have already deleted the data which we loaded so i'm going to use the same file again without making any changes submit one transform and i'm going to transform it now there is one insert come on all right so if i refresh the list again user is created department is created and id is also updated in the same department so we just saw how we can use on-file insert script to date additional values on the reference record now i also talked about how we can make updation of the remaining fields dynamic so let's see how we can do that so i'm just going to do for the same field again removing the hard coded value and passing in the value from my file insert script itself and you can obviously try for yourself how it works with multiple fields so i am going to navigate back to my transform map because that's where i can access the on file insert script which i created for load users transform map so this is where i am going to pass a value for my id field and since i am already getting the id as i have already shown you in this excel sheet i am already getting the id as part of the data which is getting imported so i can simply use source dot underscore id now let's annotate this thing and if you want to pass multiple fields you can also do that by cascading the data into one single variable and then passing it over here let me just save it and i'm going to navigate back again to my script action which will be executed when this event is fired update department is the one and this is the place where i am updating the id let me move the hard coded value and i can simply use the parent to like i use the parameter one okay and that's it so now i have made it dynamic and the value will be again populated on the id field let me change the data and let's see this time it is pepsi so i have saved this data now i'm going to load it back again in my user table come on come on so this is executed i'm going to go for the hc user and if i check for the department so department id is again executed and this time it's not a hard coded value but a value which was imported as part of the user data so this is how we can use on file insert script to update values on the reference record thank you for watching bye bye have a good list of today