Validate user email before data load

[VSN]

Hi All,

I am uploading data into ServiceNow table using Import set and trans form maps.

example we can consider the table as program.

when i was upload the data into program table, it contains the a field called "program manager" list type and refer to user table.now i have different users with same name in service now user table. if i upload data it will give result of multiple users to program manager field.

so instead of that i was using email field as reference, while updating the data into import table, i have 2 fields in that template one is" program manager "and another is "program manager email".

when uploading the data user having with the particular email id user only i need to attach to the program manager field in the program table. how to achieve this functionality.

[Ankur Bawiskar]

@VSN 

you can use onBefore transform script for this and don't have field for program manager target field

what is coming in your staging table?

does 1 row have multiple userids or emails in single column of import set?

how does the data look like in staging table

If 1 excel or csv cell contains 2 ore 3 emails comma separated then do this

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

    // Add your code here
    var arr = [];
    var sourceValues = source.u_emails;
    var gr = new GlideRecord("sys_user");
    gr.addQuery("email", "IN", sourceValues);
    gr.query();
    while (gr.next()) {
        arr.push(gr.getUniqueValue());
    }
    target.u_program_manager = arr.toString();

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

If my response helped please mark it correct and close the thread so that it benefits future readers.

[VSN]

@Ankur Bawiskar 

does 1 row have multiple userids or emails in single column of import set? -- ONLY 1 user details

how does the data look like in staging table - staging table table store all the data user name, email, etcc.. but based on user email we have to show user name in the target record.

If 1 excel or csv cell contains 2 ore 3 emails comma separated then do this-- no , only one user, 

[Ankur Bawiskar]

@VSN 

if 1 cell contains only 1 user then what it contains? name of that user?

if yes then in "Referenced value field name" in field map give the name column

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Robert H · Accepted solution]

Hello @VSN ,

In the Field Map for the Program Manager, use "Program manager email" as the source field and set Referenced value field name to "email".

That will make the import look up the users by their email address.

Regards,

Robert