Hi, my scripting isn't that great so forgive me.
I have an import set table that looks like this:
| u_department | u_rc |
| Accounting | 130 |
| HR | 202 |
| Serviceville | 101 |
I created a transform map that maps u_department and u_rc to name and id on the cmn_department table respectively. My cmn_department table looks like this:
| name | id |
| Accounting | |
| HR | 202 |
| Serviceville - Branch 20 |
|
Some departments in cmn_department are already complete, some are missing the ID, and some have " - Branch #"
appended to the name.
I'm trying to create an OnBefore Transform Script that will change source.u_department to target.name if target.name contains source.u_department. I would also like to skip the rows that completely match, and update the id of rows in cmn_department that are empty.
This is what I have, but it is just adding every single row in my import set to cmn_department with the name field for each row blank:
(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
// Add your code here
if (target.name.toString().includes(source.u_department)) {
source.u_department = target.name.toString();
} else {
ignore = true;
}
})(source, map, log, target);
Thanks for any help!
You might inactive the OnBefore Transform Script then try using source script to coalesce u_department and name as below sample script.
answer = (function transformEntry(source) {
// Get department name from source
var depName = source.u_department;
// Query department name from department table that department name contains department name from source
var grDep = new GlideRecord('cmn_department');
grDep.addEncodedQuery('nameLIKE' + depName);
grDep.query();
// From above query, check if the query found any match record
if (grDep.next()) {
return grDep.name; // Match - it will update existing record.
} else {
return depName // Not match - it will insert new department record.
}
})(source);
It should look like this.
However, this script might not work if you have similar department name such as Serviceville - Branch 20, Serviceville - Branch 21, Serviceville - Branch 22, etc.
Hope this help 😉
Regards,
Oat
