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 

so what script did you start with and where are you stuck?

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

[e__rajesh_badam]

Hi @Ankur Bawiskar ,

Thinking to use After BR but not getting how to call two table values and make relationship to store value in User BU column.

Regards,

Rajesh

[Ankur Bawiskar]

@e__rajesh_badam 

you can do this logic in before insert on sys_user

1) get the source field value, extract the 3 characters

2) query sys_user_group with the column name=Extracted value from step 1

3) then you get the group record and you get the parent i.e. Philippines group record

4) store that in the group field of sys_user

I shared the logic, please start the script from your side and it will be learning for you as well

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

[e__rajesh_badam]

Hi @Ankur Bawiskar ,

Thanks for sharing logic and tried below code but not working out, i mean Value not populating.

Could you please help me out to achieve it please.

Var SRCVALUE = current.getvalue('source');

var extractOU = SRCVALUE.substring(SRCVALUE.indexOf(",OU="));

source.u_memberof = extractOU;

var ouGroup = new GlideRecord("sys_user_group");

ouGroup.query();

if ( ouGroup.hasNext() ) {

  ouGroup.next();

//to check parent group is exist or not

  if ( ! ouGroup.parent.nil() ) {

//to check group member doesn't already exist first

    var grpMem = new GlideRecord("sys_user_grmember");

    grpMem.addQuery("group",ouGroup.parent);

    grpMem.addQuery("user",target.sys_id);

    grpMem.query();

    if ( ! grpMem.hasNext() ) {

      grpMem.initialize();

updateBusinessUnit(ouGroup.parent);

    }

  }

}

// To - Update Business Unit in User Record

function  updateBusinessUnit(groupName) {  

var usrRec = new GlideRecord("sys_user");

    usrRec.query();

    if (usrRec.next()) {

usrRec.u_bu = groupName;

usrRec.update();

    }

}