How to populate BU value in User table based on user table source column values and group table Name

[e__rajesh_badam]

Hi Everyone,

Need help to populate BU values in user Profile.

Challenge is the values which need to consider are present in sys_user and sys_user_group table.

Source is the column in User table, Have to use one of the value in Source column i.e., OU= PHA (only three letters) and need to check that value in  sys_user_group table in group table column i.e., Name =PHA, and we need to check value which is present in the Parent column and that value need to place it into user BU column. In Above Scenario the value present in Parent column of PHA is Philippines. Need to pull this value and need to store that value into the BU column of sys_user table.

Like we have around 60 OU's by tagging with parent Names. I need to populate the Value accordingly into user profile based on Source column and by pulling parent value.

Looking for help to achieve it.

Final code (issue resolved) one added in the Loop, please checkout.

[Ankur Bawiskar · Accepted solution]

@e__rajesh_badam 

u_bu is reference field pointing to sys_user_group?

If yes then try this, but please enhance it further as per your requirement

(function executeRule(current, previous /*null when async*/ ) {
    // Step 1: Extract the 3 characters from the source field
    var sourceValue = current.source_field; // Replace 'source_field' with the actual field name
    var extractedValue = sourceValue.substring(3, 6); // Adjust the indices as needed

    // Step 2: Query the sys_user_group table with the extracted value
    var groupGR = new GlideRecord('sys_user_group');
    groupGR.addQuery('name', extractedValue); // Replace 'name' with the actual column name if different
    groupGR.query();
    if (groupGR.next()) {
        // Step 3: Get the parent group record
        var parentGroup = groupGR.parent; // Adjust if the parent field is different

        // Step 4: Store the parent group in the bu field of the sys_user table
        current.u_bu = parentGroup;
    }
})(current, previous);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar · Accepted solution]

@e__rajesh_badam 

which field in sys_user_group holds the 3 characters?

As per your earlier image it was name field so I gave name field

is u_business_unit a reference field? if yes then enhance below

you can enhance as per your requirement

var groupGR = new GlideRecord('sys_user_group');
groupGR.addQuery('source','ldap:'+ extractedValue); // are you comparing the correct value here
// groupGR.addQuery('')
groupGR.query();
if (groupGR.next()) {
// Step 3: Get the parent group record
var parentGroup = groupGR.parent.sys_id; // use sys_id if u_business_unit is reference to Group
gs.addInfoMessage('parent:'+parentGroup);// Showing respective Parent value but not loading it into User BU column.

// Step 4: Store the parent group in the bu field of the sys_user table
current.u_business_unit = parentGroup;

}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@e__rajesh_badam 

where are you writing this script? in transform map script?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[e__rajesh_badam]

No in Before Insert Business rule, May I know what was the miss i did it? @Ankur Bawiskar 

[Ankur Bawiskar · Accepted solution]

@e__rajesh_badam 

u_bu is reference field pointing to sys_user_group?

If yes then try this, but please enhance it further as per your requirement

(function executeRule(current, previous /*null when async*/ ) {
    // Step 1: Extract the 3 characters from the source field
    var sourceValue = current.source_field; // Replace 'source_field' with the actual field name
    var extractedValue = sourceValue.substring(3, 6); // Adjust the indices as needed

    // Step 2: Query the sys_user_group table with the extracted value
    var groupGR = new GlideRecord('sys_user_group');
    groupGR.addQuery('name', extractedValue); // Replace 'name' with the actual column name if different
    groupGR.query();
    if (groupGR.next()) {
        // Step 3: Get the parent group record
        var parentGroup = groupGR.parent; // Adjust if the parent field is different

        // Step 4: Store the parent group in the bu field of the sys_user table
        current.u_bu = parentGroup;
    }
})(current, previous);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[e__rajesh_badam]

Thanks @Ankur Bawiskar Parent Value is fetching but not getting load into BU field, could you pls help me on it

Here is the script which i updated it,

(function executeRule(current, previous /*null when async*/ ) {

    // Extract the Required characters from the source field

    var sourceValue = current.source;

    var extractedValue = sourceValue.substring(sourceValue.indexOf("OU="));

    gs.addInfoMessage("exvalu:"+extractedValue);

    var ou_array = sourceValue.split(",OU=") ;

    gs.addInfoMessage('ou_array:'+ou_array);

    // Query the sys_user_group table with the extracted value

    var groupGR = new GlideRecord('sys_user_group');

    groupGR.addQuery('source','ldap:'+ extractedValue);

//  groupGR.addQuery('')

    groupGR.query();

    if (groupGR.next()) {

        // Step 3: Get the parent group record

        var parentGroup = groupGR.parent.name;

        gs.addInfoMessage('parent:'+parentGroup);// Showing respective Parent value but not loading it into User BU column.

        // Step 4: Store the parent group in the bu field of the sys_user table

        current.u_business_unit = parentGroup;

    }

})(current, previous);

[Ankur Bawiskar · Accepted solution]

@e__rajesh_badam 

which field in sys_user_group holds the 3 characters?

As per your earlier image it was name field so I gave name field

is u_business_unit a reference field? if yes then enhance below

you can enhance as per your requirement

var groupGR = new GlideRecord('sys_user_group');
groupGR.addQuery('source','ldap:'+ extractedValue); // are you comparing the correct value here
// groupGR.addQuery('')
groupGR.query();
if (groupGR.next()) {
// Step 3: Get the parent group record
var parentGroup = groupGR.parent.sys_id; // use sys_id if u_business_unit is reference to Group
gs.addInfoMessage('parent:'+parentGroup);// Showing respective Parent value but not loading it into User BU column.

// Step 4: Store the parent group in the bu field of the sys_user table
current.u_business_unit = parentGroup;

}

If my response helped please mark it correct and close the thread so that it benefits future readers.