Help me bulk update the field "created_by" field of ceratin users in the sys_report table .

Aditya Sinha
Tera Contributor

Case - Hi everyone! I have users in my client environment where the userID of employees from client organisation starts with AA.  Now there are a few hundred users from company B who have been uploaded in the sys_user table where for every user from company B there are 2 records in the user table- one with user id as their email id & one with their userID starting from AA (according to client organisation norms).

 

Note- 1.{All of the company B employees have an email ending with @bb.com which stays same in both versions of their record-only userID is different}

2. [Modifying the user table is not an option]. 

 

Requirement : Now my requirement is to ensure all the reports created by employees from B should be updated with their userIDs starting with AA .[Currently  reports created by company B users have their created by  - with the userIDs which have their emails]. 

 

How to implement this requirement ? Can use a script or a business rule. 

2 REPLIES 2

Bert_c1
Kilo Patron

Write a script in a Scheduled job that runs "On demand" or run it from Scripts - Background.

LucasBrown
Mega Contributor

Embark on the voyage to harmonize user identities in the realm of reports, adhering to the sacred law of non-modification of the user table. Bestow upon this endeavor the charm of a well-crafted script:

 

```javascript

// GlideRecord to traverse the Reports table

var reportsGR = new GlideRecord('report_table');

reportsGR.addQuery('created_by.email', 'ENDSWITH', '@bb.com');

reportsGR.query();

 

// Iterate through reports

while (reportsGR.next()) {

    // GlideRecord to locate corresponding user in sys_user table

    var userGR = new GlideRecord('sys_user');

    userGR.addQuery('email', reportsGR.created_by.email);

    userGR.addQuery('user_id', 'STARTSWITH', 'AA');

    userGR.query();

 

    // If user found, update the report's created_by field

    if (userGR.next()) {

        reportsGR.setValue('created_by', userGR.user_id);

        reportsGR.update();

    }

}

```

 

This script orchestrates a symphony of GlideRecords, dancing through the Reports and Sys_user tables. It aligns the UserID of reports with the sacred 'AA' prefix, paying homage to the client's organizational norms.

 

Implement this script judiciously, and may your reports resonate with the unified aura of impeccable data governance.