hi everyone in this video we are going to take a look at how to import records into servicenow that contain references to other records in a table where there is more than one unique field or one field that uniquely identifies that record one more than one index coalesce field whatever you want to call it not just one which is usually the case a two or three so how will we achieve this well stay tuned continue watching let's find out how in a previous video we used this Json file as a data source this Json file contains some locations five of them two suburbs two cities and one state they all are uniquely identified using this Geo region ID field here so we've got a 10 here a 20 and so forth this region ID is also important because we have a hierarchical structure here just like in the regular locations table and servicenow so there's a reference here to a parent so I've got 100 there another 100 and a 1000 and so forth okay unfortunately this was not the file that I retrieved from an API that I connected to recently it was in fact this one here or at least a version of a version that I connected to that I retrieved contain thousands of files the one that I've got here just contains five just for demonstration purposes we have a look here we've got the same Fields we've got Geo region level one here and Geo region id1 here we've got a region id2 here for indrapilly which is this suburb of Brisbane in Australia if you have a look here we've got another Geo region ID of one we've got another Geo region ID of two ouch okay and we've got another third geo-region ID of one for the state of Queensland these geo-region IDs are in fact unique within their own respective levels so the level three which indicates a state in Australia this one is a unique identifier within that level a unique identifier of the state similarly the region ID of one and two here for Brisbane and Redlands are unique within the level of two which is a reference to a city and the same goes for these two suburbs here that the ID that we've got here unique yeah kind of uh within the level in this case one which is the suburb level okay so how are we going to deal with this okay so if we want to reference a parent we can't just use this one geo-region ID field this is actually what we did in the previous video we looked at okay we actually went to the parent field map and we specified uh the Geo region ID or the that Target Field in the Target table to be the referenced ID okay because it wasn't the sysid we're actually going to use this ID here however we now have a little bit of a dilemma but we have a solution we have the Dilemma the problem that we have is that uh we've actually got two Fields now that together uniquely identify a record that we want to reference as the parents so we can't just use this uh identifier here the value of this field here Geo region parent ID that will not suffice for us the system will not know which one to select is at this one here the suburb to Wong is it the one for the city Brisbane or is it the one for the state of Queensland okay so let's go to our table that we were looking at before in the previous video the same table the same location data we're saving here so nothing's changed here if you haven't looked at that video yet take a look at it I go through the structure of this table very briefly and if we look at the form itself again pretty straightforward ID name level parent in the list nothing dramatic nothing really to see here uh if we go to our data source I've got a new data source here with that new Json file as an attachment okay uh specify the path to get to the array for all those records and I've also gone ahead and done or created a transform map so if we have a look at that here the transform map is similar to what we had before containing those five fields that are mapped to the Target table so uh nothing really new here I'll actually set two fields to be coalesce Fields so which I haven't done yet so that will be the Geo region level and also the ID of the region another one just underneath that one as well there we'll just set that to two as well okay similar uh to what we did in the previous video I've got a little transform script here as well if we take a quick look at that we explained in the previous video that we use this little transform script just to take selected records for the import because we're referencing parents we need to have those parents in the table first before we can then import the children so I'm just doing the import batch by batch here so initially I'm just taking the the states here which is the level three and then I'll take the cities and then I'll import the suburbs okay so that's all I've got at the moment oops let me come back to the transform map and what we'll do with the transform map we will set the parent I'll just cancel that we'll set the parent field map here uh this will need to the value that we had in the previous video which was the value in the Target table the reference value field name because we're not going to use assist ID we're actually going to use this value here the geographic region ID so let's pop that back in here geographic region ID okay there we go and save that and let's just run the transform and see what we get so initially we're only going to import one file the the state so we shouldn't have too many problems with that if we have a look at the file again and we've only got one record that is a state which is a state of Queensland and it actually has no parents so yeah we were shouldn't have any problems uh importing that record so let's go ahead and do that I've actually loaded the file already so I think I'm good to transform it so let's do that and let's go ahead and have a look at our Target table and we've got the state of Queensland okay hooray okay we can check that box we've imported the state all right let's move on to the cities now so I'm going to go back to my transform map and as we did in the previous video I'm going to change the onload script value or the before script value rather not the onload the 4 transform script the value that I've got in here from 3 which is the state to two which is the city okay let's save that and we'll just run the transform again go back to my data source here oops and load those records once more and run the transform let's see what we get okay so back here do a refresh okay yeah we've got a little bit of a conundrum or the IDS here the as I said they're not unique they're unique to the level but not unique between every single record or between levels so that actually looks okay at the moment we've got our two cities uh that have the parent uh Queensland actually that isn't yeah that's correct yeah parent Queensland City Brisbane Redlands located in the state of Queensland okay so far so good now let's import the suburbs if we have a look at our file again we've got the parent ID of one for both of them actually they're actually suburbs in Brisbane but if we have a look at our Target table here we've actually got two references to one Brisbane and Queensland so which one will the system select as being the parent if we just leave our uh transform map as it is so let's uh just leave it as it is let's do another data load let's change the value of our on before transfold script to one to pick up the suburbs and then we'll go to our data source again and do another load and data source and let's just see what the result is all right so uh load all records yes and run transform yes transform all right drum roll refresh okay we have a problem all right and we have our three records that were there before okay they're all fine at the moment okay but the problem we have at the moment is that our suburbs in Brisbane to Wong and indrapilly okay they've been classified as suburbs that's fine but the parent should not be Queensland the parent should actually be Brisbane and brisbane's parent is Queensland okay so the way that this Json file actually works is that the region level here or the parent should be one level Above So for tawang we've got a region level of one the region level should be two for the parent with an ID of one in this case if we come down here to Brisbane the region ID is one and the region level is two so that is our parents so we need a way in our transform to identify Brisbane as our parent because just relying on the uh ID here it's not enough because we actually have more than one of those records so the way to do that is transform script okay another kind of transform script so let's take a look uh at that I'm going to delete these two records actually these ones here that are incorrect so let's go ahead and do that delete delete okay and let's come to our transform map let's have field maps rather let's come back here to our transform map and we'll go and have a look at the field map for our parents because at the moment that field map isn't doing the job for us so if we come to our field maps here and go to the one for parents parent id1 we can see here we've got a reference to the geographic region ID we're saying that's basically the unique or uniquely identifiers the record but in our case it doesn't uniquely identify the record we actually need two Fields not just this field we also need the region level field as well and unfortunately there's no possibility to put something else in here it's just one field value only so how do we fix this well first thing we do we get rid of that okay and what we need to do here is to um set the source field or actually set the source script field rather to true we need to use a script instead okay because our calculation is a little bit more advanced than just specifying one field we need to what do we need to do let's take a look we need to do a lookup for the import imported record that we have we need to do a lookup of this table to show us okay uh find me the parents with that ID and with this uh level City okay and we need to do that with a script it's not possible using the standard configuration so we need to use a source script so I've got one here ready to go the region parent field map JavaScript so we're going to do a few things here similar to what I just described so the first step here will be to set the parent level to be one more that the imported regions level as I said before the parent level will be one more so if we're importing a suburb which is level one the parent will be level two and this is what we do with these lines here okay once we do have that parent record level determined we need to find the parent record based on the parent ID and that parent level so we need to do a lookup of that Target table assuming that those records have already been imported uh and we need to find out okay what is the value of that record or what record is it we should only return one so if we actually find one record then we're going to set the value of the sysid field to be the sysid of that record that we find and then we return the sysid and that's it okay so let's copy that and let's put that into our Source script there and before I save it I'm just going to make one small change here to the region level here because we're actually looking for suburb records which are a level one and then we want to find the parent level which will be one more than that which will be a level two so then everything else will work okay so I'll update that and then we can run the data load again so if I go back to my data source here and then do another load of that file run the transform and then if we come back to our Target table and do a refresh we now have our two new records here in drapili and tawang which is being classified as suburbs and the parent for both is Brisbane and that's it okay so the main thing to recognize here is that in your field map here uh for whatever reference field it is you won't be able to rely on just one field as you normally could and specify that field value here the reference value field name to uniquely identify the record if there's two or more records that you have to use to identify the record you'll have to use a source script and similar to what I've done in this Source script here just do a lookup of the table and to get the record that you need to classify as the reference record to put it in to your imported record okay so that's about it so I hope you found this video helpful and we'll see you next time bye for now