Transform map script

[BanuMahalakshmi]

Hi,

Please advise me to implement the below scenario, Target Level 1 approver field is a list collector.  I need to lookup the source email address and get the sys ID of related user and build an array of users to put in target field.  If the email is not associated with an active LDAP user with account type = Employee or Contractor. thanks in advanced.

[Thiagofmeira]

This isn't complicate.

First you must to check "Use source script"
Then you must adapt the script below.

// Normalize the list of emails and convert it to a Set for faster lookup
// Example of normalization: ["  John.Doe@example.com ", "JANE.doe@example.com"] →  ["john.doe@example.com", "jane.doe@example.com"]

    var emailSet = new Set(source.u_level_1_appr__email_address.map(function(email) {
        return email.toLowerCase().trim();
    }));

    // Create an array to store valid emails
    var validUsers = [];

    // GlideRecord query
    var gr = new GlideRecord('sys_user');
    gr.addQuery('email', 'IN', Array.from(emailSet).join(','));
    gr.addQuery('account_type', 'IN', 'Employee,Contractor'); //Here you must edit and add the fields and the values available in your table.
    gr.query();

    while (gr.next()) {
        var sysId = gr.sys_id.toString();
        validUsers.push(sysId);
    }

    return validUsers;

 
I hope it can help you!

Best regards,
Thiago

[tejas1111]

Hi @BanuMahalakshmi ,

You can handle this with a small script. Basically, you’ll need to take the source email(s), look them up in the sys_user table, and then push the valid user sys_ids into your Level 1 Approver list collector field. While doing the lookup, you can add conditions so that only active users with account type = Employee or Contractor are considered.

(function executeRule(current, previous) {

    // Assuming your source emails come in a comma separated field
    var sourceEmails = current.u_source_emails + "";
    if (!sourceEmails)
        return;

    var emailList = sourceEmails.split(",");
    var approvers = [];

    for (var i = 0; i < emailList.length; i++) {
        var email = emailList[i].trim();
        if (!email)
            continue;

        var gr = new GlideRecord("sys_user");
        gr.addQuery("email", email);
        gr.addQuery("active", true);
        gr.addQuery("u_account_type", "IN", "Employee,Contractor"); // adjust field name if different
        gr.query();

        if (gr.next()) {
            approvers.push(gr.getUniqueValue());
        }
    }

    if (approvers.length > 0) {
        current.u_level1_approvers = approvers.toString(); // list collector expects comma-separated sys_ids
    }

})(current, previous);

r example, if the source email field has "abc@example.com, xyz@example.com":

• If abc@example.com belongs to an active Employee, it gets added 

• If xyz@example.com is inactive or doesn’t match, it’s skipped 

[kaushal_snow]

Hi @BanuMahalakshmi ,

Within the Transform Map record, locate the Transform Scripts related list and click new to create.  Select onBefore to ensure the script runs before each row transformation.

(function transformRow(source, target, map, log, isUpdate) {
    var emails = (source.u_source_emails || '').toString().split(/\s*,\s*/);
    var userIds = [];

    for (var i = 0; i < emails.length; i++) {
        var email = emails[i].trim();
        if (!email) continue;

        var gr = new GlideRecord('sys_user');
        gr.addActiveQuery(); 
        gr.addQuery('email', email);
        gr.query();
        if (gr.next()) {
            var acctType = gr.getValue('account_type');
            if (acctType === 'Employee' || acctType === 'Contractor') {
                userIds.push(gr.getUniqueValue());
            }
        } else {
            log.info('No active user found with email: ' + email);
        }
    }

    if (userIds.length > 0) {
       
        target.u_target_level1_approver = userIds.join(',');
    }
})(source, target, map, log, action === 'update');

If you found my response helpful, please mark it as ‘Accept as Solution’ and ‘Helpful’. This helps other community members find the right answer more easily and supports the community.