populate reference field using transform map scri

[dilini]

I am importing asset data into "alm_asset" table. I need to map "AssignedToUIN" (i.e. employee number) into "assigned_to" reference field in alm_asset table. So I used bellow scrip and didn't work.

if (source.u_fldAssignedToUIN !== null) {

  var gr = new GlideRecord('sys_user');

  gr.addQuery('employee_number', source.u_fldAssignedToUIN);

              gr.addNotNullQuery('user_id');

  gr.query();

  if (gr.next()) {

  answer = gr.sys_id;

  }

}

[sergiu_panaite]

Hi Dilini,

If you take as example one of the values of "u_fldAssignedToUIN" and test it via background scripts, does it returns anything?

So, as example, let's assume "u_fldAssignedToUIN" = '12345' then run via background scripts:

var gr = new GlideRecord('sys_user');
gr.addQuery('employee_number', '12345');
gr.addNotNullQuery('user_id');
gr.query();

gs.log('We found: ' + gr.getRowCount() + ' records');

If it returns at least one record (count 1) then GlideRecord call works and we need to look further into the script.

Also, instead of this:

if (source.u_fldAssignedToUIN !== null) {

I would use this:

if (source.u_fldAssignedToUIN) {

Regards,

Sergiu

[Brad Tilton · Accepted solution]

You could try using the Referenced value field name field and just set it to employee number. From the wiki:

Referenced value field name

When the target field is a reference field, the transform map needs a way to match incoming source values to existing records in the reference field's source table. Since most imports do not provide a 32-character sys_id value, you must specify a column from the reference field's source table that contains values that match the incoming source values. When there is a matching record, the transform map stores the sys_id of the matching record in the target field. If there is no matching record, the transform map creates a new record in the reference field's table and stores the sys_id of the new record in the target field. If you leave this field blank, the transform map looks for matching values from the display value column of the reference field table. For example, suppose you are importing incident records and the incoming data lists user IDs for the Assigned to field. If you leave Referenced value field name blank, the transform map searches for matching values in the User table's display value column: name. By setting the Referenced value field name to the user_name column you can match the user ID values to the appropriate user records.

Creating New Transform Maps - ServiceNow Wiki

[dilini]

Thank you very much!