Update all the previous records and keep the current

[Jake Adams]

Hi,

I have an import API on Key Values (cmdb_key_value) table. It has only four fields CI, Key, Tag, Value. 

In the import I might get a record with same CI, Tag, Value as below:

Now if I again get an new import with the same CI, Tag and Value. I want to keep only the latest record and in all the above 3 records the Key value must be updated to blank/Inactive.

How can I achieve this?

[Danish Bhairag2]

Hi @Jake Adams ,

You can use an Import Set transform script and a Business Rule to handle the logic for updating records based on the latest import. Here's how you can approach it:

### 1. Import Set Transform Script:

Create an Import Set Transform Script for your Import Set table (`cmdb_key_value`). In this script, you can handle the logic for updating records and marking older records as inactive.

(function runTransformMap(source, map, log, target, transformError, alreadyTransformed) {

    // Check if a record with the same CI, Tag, and Value already exists

    var existingRecord = new GlideRecord('cmdb_key_value');

    existingRecord.addQuery('CI', source.CI);

    existingRecord.addQuery('Tag', source.Tag);

    existingRecord.addQuery('Value', source.Value);

    existingRecord.orderByDesc('sys_created_on'); // Order by creation date in descending order

    existingRecord.query();

    if (existingRecord.next()) {

        // Mark the existing record as inactive

        existingRecord.Key = ''; // Set Key to blank/Inactive

        existingRecord.update();

    }

    // Create or update the current import record

    target.CI = source.CI;

    target.Key = source.Key; // Assuming Key is a field in the import data

    target.Tag = source.Tag;

    target.Value = source.Value;

})(source, map, log, target, transformError, alreadyTransformed);

This script checks if a record with the same CI, Tag, and Value already exists. If it does, the existing record's Key is set to blank/Inactive. Then, the current import record is created or updated.

### 2. Business Rule (After Insert or Update):

Create a Business Rule that triggers after an insert or update operation on the `cmdb_key_value` table. In this Business Rule, you can further handle logic related to marking older records as inactive.

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

    // Check if the Key is empty (indicating the record is inactive)

    if (current.Key.nil()) {

        // Mark older records with the same CI, Tag, and Value as inactive

        var olderRecords = new GlideRecord('cmdb_key_value');

        olderRecords.addQuery('CI', current.CI);

        olderRecords.addQuery('Tag', current.Tag);

        olderRecords.addQuery('Value', current.Value);

        olderRecords.addQuery('sys_id', '!=', current.sys_id); // Exclude the current record

        olderRecords.query();

        while (olderRecords.next()) {

            olderRecords.Key = ''; // Set Key to blank/Inactive

            olderRecords.update();

        }

    }

})(current, previous);

This Business Rule triggers after an insert or update. It checks if the current record's Key is empty, indicating that it's inactive. If the Key is empty, the rule finds older records with the same CI, Tag, and Value and marks them as inactive by setting the Key to blank.

Ensure that you test these scripts thoroughly in a safe environment before applying them to your production instance to avoid unintended consequences.

Thanks,

Danish

[Jake Adams]

Hi,

I have tried using the onbefore transform script:

(function runTransformMap(source, map, log, target, transformError, alreadyTransformed) {

var gr = new GlideRecord('cmdb_key_value');
gr.addQuery('configuration_item', source.u_configuration_item);
gr.addQuery('tag', source.u_tag);
gr.addQuery('value', source.u_value);
gr.orderByDesc('sys_created_on');
gr.query();

while (gr.next()) {

gr.key = ' '; 
gr.update();
}

})(source, map, log, target, transformError, alreadyTransformed);

But it's not updating the existing records.

[Jake Adams]

Hi, 

I am trying it but still it's not updating the existing records

(function runTransformMap(source, map, log, target, transformError, alreadyTransformed) {

var existingRecord = new GlideRecord('cmdb_key_value');

existingRecord.orderByDesc('sys_created_on');
existingRecord.addQuery('configuration_item', source.u_configuration_item);
existingRecord.addQuery('tag', source.u_tag);
existingRecord.addQuery('value', source.u_value);
//existingRecord.addQuery('key', source.u_key);
existingRecord.query();

while (existingRecord.next()) {
existingRecord.key = 'inactive';
existingRecord.setWorkflow(false);
existingRecord.autoSysFields(false);
existingRecord.updateMultiple();
}

// target.configuration_item = source.u_configuration_item;
// target.key = source.u_key;
// target.tag = source.u_tag;
// target.value = source.u_value;

})(source, map, log, target, transformError, alreadyTransformed);

[Danish Bhairag2]

Hi @Jake Adams ,

Did u try the 2nd step as well?

### 2. Business Rule (After Insert or Update):

Create a Business Rule that triggers after an insert or update operation on the `cmdb_key_value` table. In this Business Rule, you can further handle logic related to marking older records as inactive.

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

    // Check if the Key is empty (indicating the record is inactive)

    if (current.Key.nil()) {

        // Mark older records with the same CI, Tag, and Value as inactive

        var olderRecords = new GlideRecord('cmdb_key_value');

        olderRecords.addQuery('CI', current.CI);

        olderRecords.addQuery('Tag', current.Tag);

        olderRecords.addQuery('Value', current.Value);

        olderRecords.addQuery('sys_id', '!=', current.sys_id); // Exclude the current record

        olderRecords.query();

        while (olderRecords.next()) {

            olderRecords.Key = ''; // Set Key to blank/Inactive

            olderRecords.update();

        }

    }

})(current, previous);

This Business Rule triggers after an insert or update. It checks if the current record's Key is empty, indicating that it's inactive. If the Key is empty, the rule finds older records with the same CI, Tag, and Value and marks them as inactive by setting the Key to blank.

Thanks,

Danish