Reference sys_id is getting saved after deleting data in list field

[ssoniy]

I have a Field Type 'List' in abc which is reference to some other table xyz. When i delete the data from xyz table and check the field in abc it is displaying with sys_id. how to resolve this issue?

[Shahed Shah1]

Hi Sonia

From this scenario, it sounds like you need to configure Cascade Delete Rules (if not already done) to indicate what would happen when a referenced record is deleted. Without this, any reference field pointing to a non-existent record will show a sys_id as that is the stored value.

Hope this helps

[ssoniy]

currently, on the Reference cascade rule field is showing --None--

[ssoniy]

i tried with cascade rule field with delete .. the issue stil exist

[Shahed Shah1]

Ah, you mentioned it's a Glide List. The documentation does mention this is for Reference fields. Which made me wonder.

After trying this out on a demo Instance, Reference fields would clear out the value but a Glide List doesn't, and we end up with the issue that you have mentioned (that you get a sys_id showing since the referenced record no longer exists). I played around with setting the option to Clear, but no dice.

This sounds like a possible defect. I could not find a known error and I could not find any documentation to say that cascade deletes are not supported. So, I would recommend raising an Incident with Customer Support on HI. They will get an official not supported/defect statement from the development team.

As a Glide List can store multiple records and a Reference field just stores, the cascade rules may only be supported as a single reference is easy to clear out. With a Glide List, the platform will need to pull apart the values, find the value that needs to be removed, and then rewrite the values (minus the unwanted one) back to the field. This makes me think the response may be that this is not supported.

Having said that, you are left with records where list field has invalid references, which would need to be cleared out. Have a read of the Interacting with a Glide List field KB article, to understand how you can script against this field type. Then you can create a script, using the above logic, to run in the background (Scheduled Job or in the Scripts - Background module) and then clear out the invalid reference.

Here's a proof of concept script to illustrate:

var incidentGr = new GlideRecord('incident');
incidentGr.addQuery("number", "INC0010006");
incidentGr.query();
if (incidentGr.next()) {
   var newList = [], userRec = new GlideRecord("sys_user");
   var currentList = incidentGr.getValue("watch_list").split(","); 
   gs.print("Before: " + currentList.toString());
   for (var i = 0; i < currentList.length; i++) {
       if (userRec.get(currentList[i])) {
           newList.push(currentList[i]);
       }
       else {
           gs.print(currentList[i] + " is a non-existent user")
       }
   }
   gs.print("After: " + newList.toString());
}

As you can see, the script is only working against one Incident record. Within that record it gets the watch_list field and turns it into an array, so each sys_id is looked up against the reference (the sys_user table). If a record is found, then add it to a new list, leaving out invalid references. Of course, the Glide List field can contain email addresses, which is not included in this script.

I don't want to pretend that I know what tables you are interacting with, but I would like to add something from a good practice and operational point of view. We do usually recommend deactivating records rather than deleting them, so that you can still perform necessary reporting/analytics. For example, if someone left the organisation you would de-active the user record, but can still report on Incidents raised or worked on by the user.

Of course, this is down to your requirements when you do a clean up, i.e. do you want to:

• De-activate the referenced record
• Clear out the reference when the referenced record is delete
• Delete the record that is referencing another record that gets deleted

I hope this helps set you in the right direction

EDIT: Cleaning up some typos and providing a proof of concept script