hi everyone in this video we are going to take a look at how to import records that contain a reference field where the reference table contains a unique field that is not the sysid so let's get started for this demonstration I've created a custom table to save my geographic regions the fields include an ID a level which is a choice field and the choices over here are suburb city and state we have the name of the region and then finally the parent which as you can see is a reference field to this same table to the same geographic regions table which means we're going to have a hierarchy and we're going to take advantage of this level field here so that suburbs have a parent that are cities and cities are parents that are States if we have a look at the form at the moment there are no records in here and I've got the ID name level and the parent Fields displayed okay I've taken the liberty of creating a data source already uh that has an attachment which is a Json file and we can take a quick look at that file it's this one here it only contains a few records the first two here are suburbs so we can see here that the geographic region or the level rather is set to one which indicates that it's a suburb we have the ID and the name and so forth and we've got a reference to the parent ID as well you can see both of these reference the parent ID of 100 which is this one here which is the city of Brisbane and this one in turn references another parent which is 1000 and this 1000 is the one this last entry here the state of Queensland in Australia okay so that's what we're going to import so let's come back here to our data source as I said I've already got that file attached to this data source and here under path for each row I've got the path um using the format that you need to recognize so the system recognizes each entry each record that you're going to import if you look at the documentation on this uh you'll see that for a simple array in a Json file you need to specify that the name of the array twice yes so geographic regions is the one that I've got here that first node either the node or the the array itself okay and then I've also taken the liberty of creating a transform map and if we have a look at this transform map it's pretty basic it basically just matches uh the fields that we've got in that Json file to the fields that we have in the Target table and I've set the region ID to be the coalesce field okay so at the moment that field is unique this one right here okay all right so let's go ahead now and run this transform import the records and let's just see what the result is so I'll go back to my data source here I will load the records from the file and then run the transform click transform it's done and let's have a look at the results here okay so we do have records here but it's not actually quite what we want so we have references to the the two suburbs of Brisbane which are indrapilli and tawang and you can see here they've got a reference to a parent which is 100 which doesn't really help us that much we don't really know what 100 is just by looking at this but we can see here this record is a the reference to 100 which is the city of Brisbane okay that has a parent which is one thousand and 1000 is the state of Queensland so they are all kind of okay we would like to have the the name of the the the city or the state here instead of just a number but we also have uh the appearance of these two records here uh which don't have an ID which are apparently suburbs and they have no parent and we just don't know what they are so why do we have that well if we have a look at our transform map and we come down and I open up the field map for the parent we can see here that it because it's a choice field um if we don't have a value for that already in the table we're actually going to create that entry okay so what's happened is that if we have a look at our file here we've got a reference to a parent ID of 100 here and here and then a parent ID of a thousand here and here and what's probably happened is that the system has tried to import those record has imported those records first we haven't got a reference for those yet because the parent for those records uh Records that we haven't yet imported so for the reference to 100 is here which is our third record and the reference to 1000 is our fifth record and because we have had in our field map the choice action set to create the system has just gone ahead and created those records not really knowing what they are okay so that's not what we need or want so let's go ahead and delete all of those records and we'll try again okay so let's start over kind of because we've got most things here that we need the problem at the moment is that the reference to the parent is a not the sysid and this explains why we have the number here displayed in sort of the name of the record and it's all just a little bit out of sync so when you have a situation where the referenced field that you've got or reference record and the unique ID for that record is not the sysid and often that is the case so sometimes you may have a reference to an email address or a user ID or something else it's just not the sysid and you want to use that instead to reconcile so the system knows which record to use to reference in our case this reference here the parent ID is a reference to the table itself and if we have a look at our file here the unique ID at the moment for this file is the Geo region ID here so we just need to point to that in our field map so if we come back to our table here and have a look at the corresponding field in our Target table it's this one here geographic region ID that's what we need to use so what we can do in our field map for our parent we can just specify that field name right here Geographic region ID okay and we will save that and let's try again and let's see what the result is this time okay let's see if this works or not okay so we'll go back to my data source here there we go we'll just load those records again okay load all records into the same import set table we've got five new inserts there and we'll go ahead and run the transform once more okay transform all right drum roll let's come back here let's see what the result is that looks a little bit better okay we've got our two suburbs of Brisbane tawang and indrapilly okay we've got our two cities in Queensland Brisbane and Redlands you can see the parent is referenced to Queensland and we've got the Queensland estate which does not have any parent because we didn't have the record for Australia the country okay so that's working that's fine sometimes it may not necessarily work depending on the order in which you are importing records um so what I mean is when you're importing records that do have a hierarchy uh the reference to the parent record will need to be already in the system before you create the child record otherwise you may have a missing reference or the uh the system will create an extra reference just like we saw earlier so what you can do there are different ways to tackle this simple ways more complex ways depending on the data that you've got but one thing that you can try is in your field maps here let me go back to my transform map you can run and on before script just to take the fields or the records that you want to import first and then run it again and take the second batch of records that you want to import run it again take the third batch of records that you want to import and so forth so in our case the system has already taken care of this itself but what we could have done and I'll do it again just to show you we could actually just take oops the parent record first the parent of parents so the state Queensland so we know that when we import these two records here the two cities in Queensland we've got the reference to Queensland already in the system and that reference will be imported or will work out just fine uh likewise when we import the two City Records here we can then finally import the two suburbs of the city of Brisbane knowing that the city of Brisbane has already been imported so here I've got a simple script here that I'm going to put in it's an on before script that I'm going to use in my transform app which basically just says uh if the region level is one in this case this will be our well actually I should change that to level three because that will be the one that I want to import first so let me just change it to three uh we are going to set uh the ignore flag to false in other words we're going to import that record and for everything else we're going to say ignore to true we won't import those records so if I do that and come back to my target table I will delete these records once more there we go and then we can come back to our transform map here we can go to our transform scripts and then we can just create a new on before transform script so there are different times in which your transform script can start take a look at the documentation to see what all these values actually mean but I'm going to select an on before and then just paste that script that I just copied okay and submit that so what should happen now is that when we run this transform again the state of Queensland will be the only record that is imported so let's do that let's come back to our oops to my transform map add to my data source rather let's come back data source yep we'll load those records again uh we'll come down here load those records so we're going to load five records again but we're only going to pick one of them to import so let's run the transform transform and then if I come back to the Target table refresh we just have the state of Queensland okay which means now we can safely import the children of Queensland the cities in Queensland knowing that the reference is there the import will work just fine so let's just uh do that let's see through to completion let's go back to our transform map again and we'll just make a small modification to that script so that we're going to import the level two records instead of the level three ones so let's come down here to our script uh we'll change that to level two save it and we'll just do the same process again we'll go to our data source import the records again again we're going to create five new records in our import set table but this time we're only going to take two of them to two cities so load all records run the transform and then we'll come back to our Target table we'll do a refresh and now we've got the two cities Brisbane and Redlands okay and then maybe just finally we can change this to level one which will be our suburbs and I've got two suburbs in Brisbane and then they will be imported just like that so we'll come back here to our data source again load all records again there are different ways of doing this of implementing this I'm doing a kind of I suppose the easy way or maybe the easiest way but there are other ways that you could do it you could actually uh just import after your first import run you could have maybe an event that is logged um and then a script action that then executes when that event is triggered to say okay let's import then or the records that we ignored uh the first time so as I said we'll just keep it simple here and we'll just import them batch by batch using the same data source essentially and if I come now to refresh you we kind of end up where we kind of started uh or what we had once we had import or we specified the reference field to be the geographic region ID and this is what we have here so that's working so uh in this video just to recap we have solved a couple of little problems uh the first problem being where the referenced field um contains uh or is a record in a table where the sysid is not the unique ID of that record in our case it's the region ID okay so that's what we specified uh in our parent transform map and the second problem we solved was to import the records in a certain order so that we have the parents imported first we have the children of those parents imported and then so forth so we can confidently import records knowing that the reference to the parent record is already there and the system can reference it so I hope you found this short little video useful and we'll see you next time