Transform maps: how to cross-reference a table?

[jaqueline4]

Hi,

I am quite new to creating transform maps. How can I reference a foreign field with a condition (to return error if duplicate /no match found)? My source file contains the manager's userid, not the sys id. Could any please give me a hand with the script?

Thanks!

[edwin_munoz]

Hello Jaqueline,

1. On the transform map create a new Field Map.
2. On the source field select the field from you import set table that contains the manager user id.
3. On target field select the field that you want to populate in your target table, I'm expecting this field to be a reference to the user table.
4. On Referenced value field name type: user_name.
5. On choice action select: Reject.
6. Select the coalesce check box

This will reject the records where the user id from the import doesn't match a user in ServiceNow. The coalesce check box prevent duplicates.

If you want to do this via script you will need to create an onBefore transform map script and use a script similar to this:

var userId = source.yourFieldNameHere;



//Logic to review if user exists



var user = new GlideRecord('sys_user');

var userExists = user.get('user_name', userId );



if(! userExists){

         error = true;

} else {



         //Logic to check for duplicates

         var currentUser = new GlideRecord('your target table');

         var userDuplicate = currentUser.get('your field name', user.sys_id);



         if(userDuplicate ){

                   error = true;

         } else{

                   // Set target field value

                   target.your_field_name = user.sys_id;

         }

}

Thank you and welcome to the community!