Storing different values containing commas (,) to a GlideList using transform Maps

[Azfar]

Hi Friends,
I am configuring Software Licenses form. I have a field called as Entitled Location which is a " Glide List " ( the one with a lock icon ).
i am migrating data using staging tables & transform maps. When i transfer data to this field ( Entitled Location ), it delimits the values by comma (,).
The irony is my data does contain comma. Here is an example :

Value to be migrated : Perth,Australia.

However, if i don't customize this, it gets stored as 2 values : Perth & Australia . Understand that since glidelist delimits values by " , " by default.

Next, I write a Transform Script in my transform maps to delimit the values by Semi-colon ( ; ) and query the locations table to only give valid values [ if i don't query, Glide list seems to take anything ( i really mean anything - even if its not a valid location)].

Here is my Code :

var locations=source.u_entitled_location; //taking locations from staging table
var gr = new GlideRecord('cmn_location');
var temp= new Array();
temp=locations.split(";");//delimiting by ; since multiple values have ; inbetween in my data
var length=temp.length;
for(var i =0;i<temp.length;i++)
{
var check = temp<i>;
var locationName = check.toString();
gr.addQuery('name',locationName);
gr.query(); // querying the location table to check whether its a valid value or not
while(gr.next())
{
target.u_entitled_location=gr.sys_id;
}
gr.update();
}

The problem here is that only one value gets transformed in my Target table.

Example ( Value in Staging table ) --> Perth,Australia;Sydney,Australia

Current Value in Target Table : Perth,Australia

Expected Value : Perth,Australia,Sydney,Australia where Perth,Australia is 1st value & Sydney,Australia is 2nd value


Though it delimits at ; and gives me a proper value, it gives only the value at the first position & ignored Sydney,Australia

Can someone have a look & help me out with this ? Thanks a lot in advance guys!

[gaidem]

Here's an approach:

var locations=source.u_entitled_location;
var array1 = [];
var string1 = locations.split(';');
var string2 = string1[0].split(',');
for (var i = 0; i < string2.length; i++) {
    array1.push(string2<i>);
}
for (var ii = 1; ii < string1.length; ii++) {
    array1.push(string1[ii]);
}
for (var iii = 0; iii < array1.length; iii++) {
    var gr = new GlideRecord('cmn_location');
    gr.addQuery('name', array1[iii]);
    gr.query(); // querying the location table to check whether its a valid value or not
    while (gr.next()) {
        target.u_entitled_location = gr.sys_id;
    }
}

This assumes that any values with a comma in them will always be at the end.

[Azfar]

Thanks Matt. Tried this but doesn't gives me expected results.

Only the Second value gets stored now ignoring the first one.

Value in the staging table : perth,Australia;Sydney,Australia

Value in the target table using above code : Sydney,Australia

[gaidem]

I ran this in a background script:

var locations="Perth,Australia;Sydney,Australia";
var array1 = [];
var string1 = locations.split(';');
var string2 = string1[0].split(',');
for (var i = 0; i < string2.length; i++) {
    array1.push(string2<i>);
}
for (var ii = 1; ii < string1.length; ii++) {
    array1.push(string1[ii]);
}
for (var iii = 0; iii < array1.length; iii++) {
    var gr = new GlideRecord('cmn_location');
    gr.addQuery('name', array1[iii]);
    gr.query(); // querying the location table to check whether its a valid value or not
    while (gr.next()) {
       gs.print(gr.name);
    }
}

This indicated to me it works. Try running that and see if you get:

*** Script: Perth
*** Script: Australia
*** Script: Sydney,Australia

[Azfar]

Unfortunately, I do not have access to the Background Scripts.
I tried it with " On Before " Transform Script.

It SHould work with that as well. or am i missing a point?