Hello,
I am using a Fix Script designed to act as a template for whenever I need to delete a large amount of records off a table at once. I have included it here:
var table = "name_of_table";
var recGR = new GlideRecord(table);
recGR.query();
recGR.deleteMultiple();
-Purpose: The use-case I have is to delete all records off the cmn_location table. To accomplish this, I added cmn_location to my var table in the previously mentioned script:
var table = "cmn_location";
var recGR = new GlideRecord(table);
recGR.query();
recGR.deleteMultiple();
-Problem : When I run the Fix Script, only a few records are deleted at a time rather than all at once. For example, I start off with 47 locations on my cmn_location table. When I run the fix script, I then have 35 records remaining. When I then run the fix script for the second time, I have 22 records remaining. I have to run the script four times to finally delete all records in the table. Additionally, I tried to run the script using a different table such as "incident" and it deletes all records at once- unlike the cmn_location table.
-Question: Why does the Fix Script run as expected on the incident table, but not the cmn_location table? Is this a typical response for the cmn_location table or is there something wrong with my method?
-Additional Resources: I have attached a screenshot for reference. These logs appear every time I run my delete Fix Script against the cmn_location table.
Thank you to everyone who helped me out with this situation!
I did more searching on this topic to figure out why not all records were deleting at once and finally came across the source of the problem. I will write my findings/solutions below to help the next person who runs into same situation:
*Note for this use-case, I am working with my Personal Developer Instance (PDI) and using Out of Box (OOB) data*
As stated above in my initial post, every time I either ran the Fix Script written above or clicked the "Delete All Records" UI Action, as suggested by Brian Dailey, only a selected amount of records would be deleted at a time. I noticed that when I would manually delete records on the cmn_location table these error messages would always appear:
When "servicenow 'delete of' AND 'not allowed because of a reference in record'" Googled, these Community posts come up:
The two take-aways from these Community posts for this use-case are:
1) One cannot delete a "parent" record that has "child" records attached to it. For example, "Escondido" cannot be deleted since "243 South Escondido Boulevard, Escondido, CA" is referenced to it (Cannot delete the "parent" record (Escondido) when there are "child" records referencing it- in this case, "243 South Escondido Boulevard, Escondido, CA"). In this specific use-case, not all records were being deleted by the Fix Script or the UI Actions due this fact. To bypass these errors, all "child" records most be deleted first before deleting the "parent" records.
2) However, after this is done. Seven records still remained. Some records (in this case, records found on the cmn_location table) have what is called Cascade Delete Rules. These rules prevent a user from deleting a record in one table that references another record on a different table. This directly ties into take-away Number 1. With these rules, seven OOB location records cannot be deleted due to them being referenced within the "Stockroom File".
In order to bypass these rules to delete the remaining seven records, these Cascade Delete Rules need to be set to "-None-"
Instructions how to do this:
- Go to the Navigation Panel and search for "Stockrooms". Select "Stookrooms" under the Inventory module.
*This is what should appear by default if you are working in a PDI*
2. Select and open the first record (In this case "San Diego Border - Pickup/Dropoff").
3. Right-click the header of the reference field "Location" and select "Configure Dictionary"
4. Once the record is opened in it's From View, select "Advanced View" under "Related Links"
5. in the "Dictionary Entry - Location [Advanced view*]" select "Reference Specification" in the related lists.
6. Under the header "Reference Specification - Additional Customization" locate the field named "Reference cascade rule". By default this will be set to "Restrict".
7. Set the select box to "-- None --" and save the from. **Note: Do not select "None" but "-- None --". They are different. Please reference the "Configure cascade delete rules" ServiceNow documentation for reference of the differences.**
8. (Optional) If you are trying to delete all records on the cmn_location table, the "mandatory" checkbox will need to be unchecked. If this box remains to be selected, a location will need to be referenced. Meaning that some location data will need to remain in the "location" reference box on the form. However, if the unchecked, the "location" field can be left empty and all location records can be successfully deleted on the cmn_location table.
9. This process will need to be repeated for all records located in the "Stockrooms" table which was navigated to in Step 1.
After all these steps are completed, all records on the cmn_location table can successfully be deleted- whether it be with the Fix Script written above or using the UI Action Bryan Dailey mentioned!
Thank you to everyone who helped me out with this situation!
I did more searching on this topic to figure out why not all records were deleting at once and finally came across the source of the problem. I will write my findings/solutions below to help the next person who runs into same situation:
*Note for this use-case, I am working with my Personal Developer Instance (PDI) and using Out of Box (OOB) data*
As stated above in my initial post, every time I either ran the Fix Script written above or clicked the "Delete All Records" UI Action, as suggested by Brian Dailey, only a selected amount of records would be deleted at a time. I noticed that when I would manually delete records on the cmn_location table these error messages would always appear:
When "servicenow 'delete of' AND 'not allowed because of a reference in record'" Googled, these Community posts come up:
The two take-aways from these Community posts for this use-case are:
1) One cannot delete a "parent" record that has "child" records attached to it. For example, "Escondido" cannot be deleted since "243 South Escondido Boulevard, Escondido, CA" is referenced to it (Cannot delete the "parent" record (Escondido) when there are "child" records referencing it- in this case, "243 South Escondido Boulevard, Escondido, CA"). In this specific use-case, not all records were being deleted by the Fix Script or the UI Actions due this fact. To bypass these errors, all "child" records most be deleted first before deleting the "parent" records.
2) However, after this is done. Seven records still remained. Some records (in this case, records found on the cmn_location table) have what is called Cascade Delete Rules. These rules prevent a user from deleting a record in one table that references another record on a different table. This directly ties into take-away Number 1. With these rules, seven OOB location records cannot be deleted due to them being referenced within the "Stockroom File".
In order to bypass these rules to delete the remaining seven records, these Cascade Delete Rules need to be set to "-None-"
Instructions how to do this:
- Go to the Navigation Panel and search for "Stockrooms". Select "Stookrooms" under the Inventory module.
*This is what should appear by default if you are working in a PDI*
2. Select and open the first record (In this case "San Diego Border - Pickup/Dropoff").
3. Right-click the header of the reference field "Location" and select "Configure Dictionary"
4. Once the record is opened in it's From View, select "Advanced View" under "Related Links"
5. in the "Dictionary Entry - Location [Advanced view*]" select "Reference Specification" in the related lists.
6. Under the header "Reference Specification - Additional Customization" locate the field named "Reference cascade rule". By default this will be set to "Restrict".
7. Set the select box to "-- None --" and save the from. **Note: Do not select "None" but "-- None --". They are different. Please reference the "Configure cascade delete rules" ServiceNow documentation for reference of the differences.**
8. (Optional) If you are trying to delete all records on the cmn_location table, the "mandatory" checkbox will need to be unchecked. If this box remains to be selected, a location will need to be referenced. Meaning that some location data will need to remain in the "location" reference box on the form. However, if the unchecked, the "location" field can be left empty and all location records can be successfully deleted on the cmn_location table.
9. This process will need to be repeated for all records located in the "Stockrooms" table which was navigated to in Step 1.
After all these steps are completed, all records on the cmn_location table can successfully be deleted- whether it be with the Fix Script written above or using the UI Action Bryan Dailey mentioned!
