Update ID field in department from User-Import Transform maps

Nisha30 · 3 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 · 3 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

Nisha30 · 3 weeks ago

Hi @Ankur Bawiskar

tried this as well. in sys_user table the department is not updating

Thanks

Ankur Bawiskar · 3 weeks ago

@Nisha30

you removed field map?

when you added the transform script logic did you add logs and debug?

share the debugging results

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

source is coming undefined put a log and saw. even though in excel values are there for source.u_department

Ankur Bawiskar · 3 weeks ago

@Nisha30

So it means the column name is wrong.

if the source value comes then the logic I shared will work for sure.

are you sure the source column name is correct?

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

the excel sheet has the column for department and I have updated the script as source.u_department

Thanks