Update ID field in department from User-Import Transform maps

Nisha30 · 4 weeks ago

HI,

We are loading User data via transform maps to sys_user table. However the department field also is mapped to department field in user table. (source_department)

SInce the choice is set Create=TRUE for Department, it is creating Departments in cmn_department table.

However if that happens we want to update the ID field in cmn_department table with some static value to identify what came via import . (since we also manually upload departments )

Thanks

Ankur Bawiskar · 4 weeks ago

@Nisha30

in that case don't use field map for department. please use onBefore transform script

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

    // Add your code here
    var sourceDepartment = source.u_department_name;
    var gr = new GlideRecord("cmn_department");
    gr.addQuery("name", sourceDepartment); // query with name if department name is coming in staging table
    gr.query();
    if (gr.next()) {
        target.department = gr.getUniqueValue();
    } else {
		// not found then create
        gr.initialize();
        gr.name = sourceDepartment;
		// populate other fields if you wish
        gr.id = 'Some Unique ID';
        gr.insert();
        target.department = gr.getUniqueValue();
    }

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

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

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

Ankur Bawiskar · 3 weeks ago

@Nisha30

You wrote script in onBefore transform script right?

share the dictionary config of that department field from staging table

share updated transform script and share screenshots.

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

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Nisha30 · 3 weeks ago

Hi @Ankur Bawiskar

Yes did in onBefore script.

Ankur Bawiskar · 3 weeks ago

@Nisha30

Seems you didn't check my response properly.

I clearly informed don't use field map and use onBefore transform script for this

But the above is in field map script

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

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Nisha30 · 3 weeks ago

Yes @Ankur Bawiskar removed field map completely. Only onBefore transform script.

sys_user is getting updated with Department . however in cmn_department ID field is not populated.

Thanks

Ankur Bawiskar · 3 weeks ago

@Nisha30

it should work fine.

share the script here since the new DEPT is getting created and set then that new field should be set here as well.

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

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader