Update ID field in department from User-Import Transform maps

[Nisha30]

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

[GlideFather]

Hi @Nisha30,

Departments are stored in [cmn_department] table and User [sys_user] is only referencing it... that should be all good.

What's the issue then?

However you should create some check whether that Department is already existing to avoid creating duplicates. Please share some more details of your Transform Map and its configurations.

[Ravi Gaurav]

Hello Nisha,

• Open your Transform Map (target: sys_user).

• Open the Field Map where source source_department maps to target department (reference to cmn_department).

• Check Use source script and paste something like this (adjust field names as needed):
  
  try the below code suggested by Perplex and VS Open Geminin
  
  

  (function transformEntry(source) {
  var deptName = (source.source_department || '').toString().trim();
  if (!deptName)
  return ''; // don't set department

  var dept = new GlideRecord('cmn_department');
  dept.addQuery('name', deptName);
  dept.query();
  if (dept.next()) {
  // Reuse existing department
  return dept.getUniqueValue();
  }

  // Create & tag as "came via import"
  dept.initialize();
  dept.name = deptName;

  // >>> IMPORTANT: replace 'u_id' with your actual field to tag the record <<<
  // e.g., u_import_tag / u_source / code — whatever you use to identify imported rows
  dept.u_id = 'IMPORT_TM'; // static marker you wanted
  // Optionally add more metadata:
  // dept.u_source = 'User Data Import';
  // dept.active = true;

  var newId = dept.insert();
  return newId || '';
  })(source);
  
  --------------Below is something which I have Suggested
  
  

  Option B: Keep “Create = true” and tag via a Business Rule on cmn_department

  If you want to keep the default auto-create behavior, add a Before Insert BR on cmn_department that tags departments only when they’re created by your import.

  Pre-req for reliability

  • Run the Transform as a dedicated user (e.g., import.user). In the Transform Map, set “Run as” to that user.

  • Then the BR can safely tag records created by that user only.

  Business Rule (Before Insert | when: Insert | order: low)

  (function executeRule(current /*, previous */) {

  // Tag only records created by the import user

  if (gs.getUserName() == 'import.user') {

  if (JSUtil.nil(current.u_id))

  { current.u_id = 'IMPORT_TM'; // your static marker } } })(current);

  -------------------------Thank YOU ------------------

[Nisha30]

HI @Ravi Gaurav  thanks for sharing. First one seems more preferable . But it did not worked. Department is not inserted in sys_user table itself.

[Ankur Bawiskar · Accepted solution]

@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.