how to remove duplicate location without affecting its referenced records ?

[Mahesh23]

Hello community,

Can someone help us with automating the below requirement.

Requirement :

We have 2 duplicate locations 
1) ABC Street

2) ABC st

Location 1 is referenced in more than 100 other records like user location in sys_user table, cmdb_ci etc.

similarly Location 2 is referenced in more than 50 other records like user location in sys_user table, cmdb_ci etc.

we need to replace the records where Location 2 is used with Location 1 and delete the Location 2.

also there some Locations having 4 or 5 duplicates with minor changes in the location name

like 

3) 3350 ABC Avenue

4) 3350 ABC Ave

5) 3350 ABC 

Thanks in advance.

[OlaN]

Hi,

You can create either a Flow or a fix script that changes the value on these referenced records.

Both solutions require that you know all places where the location is used or you will end up with broken/deleted references.

[Mahesh23]

Thanks for your response OlaN. Could please help us with steps or logic ?

[Harneet Sital]

Hi Mahesh, 

You can do the following, c

Step 1: Create a new table that has two attributes  (tables inheriting dl_lookup table are free and not counted in your subscription)

Field 1 - Correct location

Field 2 - Incorrect location (both reference fields)

This will be your database where you store the incorrect locations and corresponding correct locations. 

Step 2: Create a scheduled job/fix script as suggested above to find the incorrect location and update it with the correct one. 

Edit: I will help you with the script in a bit

Step 3: Rectify the source of the location data and not simply delete the records in ServiceNow as these will pop back up. 

(I repeat - DO NOT Delete the locations, make them inactive)

[OlaN]

I can show how a Flow would be configured to update records pointing to the wrong location, example below.