How to compare records within the same custom table and update a reference field.

[Nadeem Basha]

@Chuck Tomasi

Hi Chuck ,

Is there any script that can be written to compare records within the same table.

I have got a custom table that has records belongs to the year 2023,2024 and the problem is in the records that belong to 2024 ,there is a reference field called "Last year" which displays empty.

Expected behaviour is that the "Last year" field should have been mapped with the respective 2023 records and that means for every 2024 records there was already 2023 records available in the table but it was not  mapped .

Is there any script which can be written in the backend to map the 2023 records with respective 2024 records ?.

Thanks

[Amit Pandey]

Hi @Nadeem Basha 

You can write a background script in ServiceNow-

var records2023 = new GlideRecord('your_custom_table');
records2023.addQuery('your_date_field', 'STARTSWITH', '2023'); // Adjust the date field name and condition
records2023.query();

while (records2023.next()) {

    var records2024 = new GlideRecord('your_custom_table');
    records2024.addQuery('your_date_field', 'STARTSWITH', '2024'); // Adjust the date field name and condition
    // Add additional conditions here if needed to match the records
    // For example: records2024.addQuery('some_field', records2023.some_field);
    records2024.query();
    if (records2024.next()) {
        // Map the 2023 record to the corresponding 2024 record
        records2024.last_year = records2023.sys_id; // Adjust the field name
        records2024.update();
        gs.info('Mapped record for year 2023 with sys_id: ' + records2023.sys_id + ' to record for year 2024 with sys_id: ' + records2024.sys_id);
    } else {
        gs.info('No matching record found for year 2024 for record with sys_id: ' + records2023.sys_id);
    }
}

Please mark my answer helpful and correct.

Regards,

Amit

[Nadeem Basha]

Hi Amit,

Thank you for the response.

Will try with the above script and keep u posted if works as expected or not .

Thanks

[Nadeem Basha]

@Amit Pandey 

Hi Amit,

I have tried the above code and it almost worked but not delivered the expected result.

The reason is because all the 2023 records are trying to get mapped to a single 2024 record and i will share the screenshot below in attachment.

Just to keep you informed that for example if there are 250 records available for 2024 which means there are same number of records available for 2023 aswell and those records should be mapped with its respective 2024 records.

Inorder to map each and every 2023 records with its corresponding 2024 records there are few conditions which must me met ,if you need those conditions i can provide it aswell.

But i just want to know what logic can we use in the above script to map all the 2023 records to its respective 2024 records.

If any further clarification needed from my side, pls revert i can explain it in a different way.

Thanks

[sushantmalsure]

can you share your script here? and probably can help you better