Automate the Auto Map Matching Fields

Hong · ‎10-30-2018

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 · ‎10-30-2018

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();
  }
}

View solution in original post

Hong · ‎10-31-2018

Attached is a image of the error

Hong · ‎10-31-2018

Hi Pradeep,

It seems after putting in the sys_id into the script, the fields are not being mapped between the source and target tables. I looked at the UI action "Auto Map Matching Fields" record, and am wondering if the condition "new TableUtils(current.source_table).tableExists() && current.canWrite() && new GlideRecord('sys_transform_entry').canCreate();" might be why. Does this condition matter when we need to map the fields?

Thanks,

Hong

Pradeep Sharma · ‎10-31-2018

I got this working from the Global scope. Is the table created part of custom scope application?

-Pradeep Sharma

Hong · ‎10-31-2018

Yes, I am creating this for a custom scope application. Forgot to mention that.

-Hong

Pradeep Sharma · ‎10-31-2018

OK, I haven't tried it for custom scope app. Will check in sometime and let you know.

-Pradeep Sharma