Script include to bulk delete company records with no company ID

[Nabilah]

Hi, I have created a script include to call with a fix script to bulk delete company records WHERE company ID is empty and there are no reference records attached to it (e.g Req item, tiering assessment, risk assessment or issue). 

var CompanyCleanupUtils = Class.create();
CompanyCleanupUtils.prototype = {
    initialize: function(log) {
        this.log = (log === undefined) ? gs : log;
        this.deletedCount = 0; // Counter for deleted records
        this.skippedCount = 0; // Counter for skipped records
    },

    cleanCompanies: function() {
        this.log.info("Starting company cleanup process");

        // Query for companies where company_id is empty
        var companyGR = new GlideRecord('core_company');
        companyGR.addNullQuery('u_company_id'); // Find companies where company_id is empty
        companyGR.query();

        while (companyGR.next()) {
            var companySysId = companyGR.sys_id.toString();
            var companyName = companyGR.name.toString();

            // Check for references in related tables
            var hasReferences = this.hasRelatedRecords(companySysId);

            if (!hasReferences) {
                // No references found, log the company that would be deleted
                this.log.info("TEST MODE: Company " + companyName + " (sys_id: " + companySysId + ") would be deleted.");
                this.deletedCount++;
            } else {
                this.log.info("Company " + companyName + " (sys_id: " + companySysId + ") has related records, skipping deletion.");
                this.skippedCount++;
            }
        }

        this.log.info("Company cleanup process complete (Test Mode - no records deleted).");
        this.log.info("Total companies deleted: " + this.deletedCount);
        this.log.info("Total companies skipped: " + this.skippedCount);
    },

    hasRelatedRecords: function(companySysId) {
        // Check for related request items (sc_req_item table)
        if (this.hasRelatedRecordsInTable('sc_req_item', 'company', companySysId)) {
            return true;
        }

        // Check for related tiering assessments 
        if (this.hasRelatedRecordsInTable('sn_vdr_risk_asmt_vdr_tiering_assessment', 'company', companySysId)) {
            return true;
        }

        // Check for related risk assessments 
        if (this.hasRelatedRecordsInTable('sn_vdr_risk_asmt_assessment', 'company', companySysId)) {
            return true;
        }

        // Check for related issues 
        if (this.hasRelatedRecordsInTable('sn_vdr_risk_asmt_issue', 'company', companySysId)) {
            return true;
        }

        // No related records found
        return false;
    },

    hasRelatedRecordsInTable: function(tableName, companyField, companySysId) {
        var relatedGR = new GlideRecord(tableName);
        relatedGR.addQuery(companyField, companySysId);
        relatedGR.query();
        return relatedGR.hasNext();
    },

    type: 'CompanyCleanupUtils'
};

I think my hasRelatedRecords function is not working properly as according to the outcome it states there are 0 skipped records. I have company records with empty company ID with related records so that should not be the case. 

On my company form, I have a related list with the following tabs (request item, tiering assessment, risk assessment and issues). Expected outcome is to delete company records with no company ID and no records in the related lists. 

Please can someone point me to the right direction

[debendudas]

Hi @Nabilah ,

I have used your code and it's giving proper output for me.

For testing purpose, I created a custom field (u_company_id) in core_company table.

Please check/validate the below:

1. Confirm if the u_company_id is the correct field or not.

2. Please confirm the related table name and the reference field names for those tables are correct or not.

3. Suggestion: Please use GlideAggregate in hasRelatedRecordsInTable function.

[Nabilah]

Hi @debendudas, The field and related table names are all correct. The count is correct for the number of records with no company ID. However it is incorrect for the number of skipped records (Company records with no company ID and no related records).

Please can you clarify where exactly I would use GlideAggreagate in the hasRelatedRecordsInTable function?

[debendudas · Accepted solution]

In this case, Check the below for the records which has related records but not getting skipped:

1. Note down the sys_id of these records.

2. Run the below script in background and check if any records are showing or not

function showRelatedRecords(companySysId, tableName, refFieldName) {
    var recordGr = new GlideRecord(tableName);
    recordGr.addQuery(refFieldName, companySysId);
    recordGr.query();
	var records = [];
	while(recordGr.next()){
		records.push({
			sysId: recordGr.getValue("sys_id"),
			number: recordGr.getValue("number")
		});
	}

	gs.log(tableName + "has the below records: " + JSON.stringify(records));
}

// Replace the <sys_id> with the sys_id of the company record
showRelatedRecords(<sys_id>, "sc_req_item", "company");
showRelatedRecords(<sys_id>, "sn_vdr_risk_asmt_assessment", "company");
showRelatedRecords(<sys_id>, "sn_vdr_risk_asmt_issue", "company");

[Nabilah]

Hi @debendudas, thank you the records are showing, turns out I had the reference field name wrong. It should have been "vendor" instead of "company"