How to populate data by comma separated based on another field in source table in the import sets

[msc]

Hi 

How to populate data by comma separated based on another field in source table in the import sets using transform scripts

For example 

Excel sheet contains 

Manager email and employee names 

Here same manager email is repeated in the excel sheet with different employee names So before transforming this in to target table I want to join that employee names in to one and then I want to transform to that target fields.

Here target table is incident for example I say

And assigned to field is manager email and 

Description is list employee names.

[Anshu_Anand_]

I tried and its working

1. excel data

2. After import into snow

3. ran a background script on this table

//fetch unique manager names

var arrayUtil = new ArrayUtil();  
var gr = new GlideRecord("u_employee");
gr.query();
var arr=[];
while(gr.next())
{
arr.push(gr.u_manager.toString());
}
arr = arrayUtil.unique(arr);

// merge employee name with matching manager

for(var i=0;i<arr.length;i++)
{
var gr1 = new GlideRecord("u_employee");
gr1.addQuery('u_manager',arr[i]);
gr1.query();

var str="";
while(gr1.next())
 {
  str=str+gr1.u_employee+",";
 }
str = str.substring(0, str.length - 1);
createNew(arr[i],str);
}

// create new entry into import table
function createNew(arr,str)
{
var gr2= new GlideRecord("u_employee");
gr2.newRecord();
gr2.u_employee=str;
gr2.u_manager=arr;
gr2.insert();
}

//deleted rest of the records
var gr4 = new GlideRecord("u_employee");
gr4.addEncodedQuery('u_employeeNOT LIKE,');
gr4.query();
while(gr4.next())
{
gr4.deleteRecord();
}

4. New look of table (need to increase size of string 40 to 1000 for employee)

5. Exported into excel again or create your own transform map as per your requirement.

If its help, please mark answer as correct

[msc]

Hi Anshu So here u_employee table is the source table right And background script you wrote is on start or on before transform script Lastly can I map these to assigned to and description filed of incident table and making colease as true

[Anshu_Anand_]

u_employee is table created by data source.

I just ran a background script here depending one if its only 1 time run .

For everytime,

It seems difficult onStart() as well as using source script

problem is that even if i try to use some logic, data needs to be ignored once the import is completed for the rest of the data.

Its better to apply some excel logic on excel data it self before importing.

[msc]

Hi Anshu

i just did this by onbefor transform script  see below

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

//fetch unique manager emails

var arrayUtil = new ArrayUtil();
var gr = new GlideRecord("u_vtask_creation");
gr.query();
var arr=[];
while(gr.next())
{
arr.push(gr.u_manager_email.toString());
}
arr = arrayUtil.unique(arr);

// merge employee name with matching manager

for(var i=0;i<arr.length;i++)
{
var gr1 = new GlideRecord("u_vtask_creation");
gr1.addQuery('u_manager_email',arr[i]);
gr1.query();

var str = "";
while(gr1.next())
{

str=str+gr1.u_employee_first_name+ ' ' +gr1.u_employee_last_name +",";

}
str = str.substring(0, str.length - 1);
createNew(arr[i],str);
}
//create new entry into target table

function createNew(arr,str)
{
var gr2= new GlideRecord("u_vtask");
gr2.newRecord();
gr2.assigned_to = arr;
gr2.description = str;
gr2.insert();
}

})(source, map, log, target);

records are creating but it will increasing the count of records and also description field employees names are repeating  how canii restrict this .

please help me on this