Automate the Auto Map Matching Fields

[Hong]

Hi,

I'm looking for a way to auto map matching field between two tables (Source and Target) without doing it manually. I want to create a scheduled transformation that automatically maps the matching fields. Is this possible through script or other methods?

Cheers,

-----

Hong

[Pradeep Sharma · Accepted solution]

Hi Hong,

Please refer the below script and adjust it accordingly.

var transformGR = new GlideRecord('sys_transform_map');
transformGR.addQuery('sys_id','4e1ee8fcc0a80094004ea6a3e3ac6627'); //Replace 4e1ee8fcc0a80094004ea6a3e3ac6627 with the record sys_id
transformGR.query();
while(transformGR.next())
{
var sourceGR = new GlideRecord(transformGR.source_table);
sourceGR.initialize();
var targetGR = new GlideRecord(transformGR.target_table);
targetGR.initialize();

var targetGRElements = targetGR.getFields();

var fields = sourceGR.getFields();
for (var i = 0; i < fields.size(); i++) {
  var field = fields.get(i);
  var name = field.getName();
  var label = field.getED().getLabel();

  //skipe virtual fields like Tags
  if (!field.getED().isInDatabase())
	  continue;
	
  var tname = matchFields(name, label);
  if (tname == 'manager' && targetGR.getElement(tname).getED().hasAttribute('no_auto_map'))
      continue;

  if (!isSkip(name) && tname != null && !isSkip(tname)) {
    createTransformEntry(name, tname);
  }
}


function matchLabel(label) {
  label = label.toLowerCase();

  for(var i = 0; i < targetGRElements.size(); i++) {
    var field = targetGRElements.get(i);
    var tlabel = field.getLabel().toLowerCase();

    if (label == tlabel) {
      return field.getName();
    }
  }

  return null;
}


}
function matchFields(name, label) {
  var tname = matchName(name);
  if (tname != null)
    return tname;

  tname = matchLabel(label);
  if (tname != null)
    return tname;

  tname = matchLabelToName(label);
  if (tname != null)
    return tname;

  return null;
}
function matchName(name) {
  for(var i = 0; i < targetGRElements.size(); i++) {
    var field = targetGRElements.get(i);

    if (name == field.getName()) {
      return field.getName();
    }
  }

  return null;
}
function matchLabel(label) {
  label = label.toLowerCase();

  for(var i = 0; i < targetGRElements.size(); i++) {
    var field = targetGRElements.get(i);
    var tlabel = field.getLabel().toLowerCase();

    if (label == tlabel) {
      return field.getName();
    }
  }

  return null;
}
function matchLabelToName(name) {
  name = name.toLowerCase();

  for(var i = 0; i < targetGRElements.size(); i++) {
    var field = targetGRElements.get(i);

    if (name == field.getName()) {
      return field.getName();
    }
  }

  return null;
}
function isSkip(name) {
    // skip sys fields
    if (name == "sys_created_on" ||
        name == "sys_updated_on" ||
        name == "sys_created_by" ||
        name == "sys_updated_by" ||
        name == "sys_mod_count" ||
        name == "sys_class_name" ||
        name == "sys_id" ||
        name == "sys_import_set" ||
        name == "sys_import_row" ||
        name == "sys_import_state")
      return true;

    return false;
}
function createTransformEntry(sourceName, targetName) {
  var entryGR = new GlideRecord("sys_transform_entry");
  entryGR.addQuery("map",transformGR.sys_id);
  entryGR.addQuery("source_table",transformGR.source_table);
  entryGR.addQuery("source_field",sourceName);
  entryGR.addQuery("target_table",transformGR.target_table);
  entryGR.addQuery("target_field",targetName);
  entryGR.query();
  if (!entryGR.next()) {
    entryGR.map = transformGR.sys_id;
    entryGR.source_table = transformGR.source_table;
    entryGR.source_field = sourceName;
    entryGR.target_table = transformGR.target_table;
    entryGR.target_field = targetName;
    entryGR.insert();
  }
}

[Hong]

Sorry if this might sound like a dumb question. But for which of the tables am I getting the sys_id to put into the script? Thanks.

[Pradeep Sharma]

It is for the Transform map that you have created.

-Pradeep Sharma

[Hong]

I am getting the following error: "Error during insert of x_278895_coc_proje_allnamesfinal (null)". 

When I tried the code, I deleted the field maps for testing.

Thanks,

Hong