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

[Nadeem Basha]

@sushantmalsure 

Hi Sushan,

Please find the script below and let me know what adjustment needed to have each 2024 records  getting mapped with its respective 2023 records.

Thanks

[sushantmalsure · Accepted solution]

Hello 

There has to be one more condition to be added here:

records2024.addEncodedQuery('u_best_practices.u_yearIN2024^u_entity=b6c31971dbe6a850ed96102139961950');

This condition should be related to identify exact record from 2024 which needs to be updated. Like for example, short description can be identifier which is same for the record of 2023 and 2024. So it will look like following:

records2024.addEncodedQuery('u_best_practices.u_yearIN2024^u_entity=b6c31971dbe6a850ed96102139961950^short_description='+records2023.short_description);

And this is the reason in your logs you are always getting same sys_id (which probably is 1st record from 2024 for said entity) which is INCORRECT.

So basically you need to find one more relation between 2023 and 2024 record and adjust in above line.

[Nadeem Basha]

@sushantmalsure 

Thank you for your help , it worked out .

Would you mind to help me in another requirement with Flow designer ,pls find the community link below and revert if any clarification is needed for the flow designer part .

Flow designer - custom actions - ServiceNow Community

Thanks

[sushantmalsure]

You can run a background script to update all 2024 records to contain 2023 record in referenced field.

In order to help you better , share your table name , year field name and details to compare related 2023's record from 2024 (like an identifier on 2023 record which is to identify from 2024 record).

[Nadeem Basha]

Hi Sushant,

Thank you for the response.

Will keep you posted on this .

Thanks