First tell me how are you going to identify the orphan records.
I tried below query at my end on work info form, but no result. check at your end and share the result.
'Incident Number' = $NULL$
That's the challenge!.....
I have archived some Incident records and forgot to apply archiving on Worklog form because of this I end up leaving orphan records in worklog form.
Did you tried Minus Clause in Query ..
Select IncidentID from Work Info Table
Select Incident_D from HPD:HelpDesk
A Minus B -->In theory It should Give Incident ID only Beloging to A but Not In B.
Set some flag to workinfo records where Incident records exist. Now It will be very easy to search and delete orphan records. (where flag=$NULL$)
You can achieve this using some code.
Take all the INC ID's that are archived and trigger a query on Work Info form for those ID's.
Identify the records and delete it. That's It.
Now you know how to write the code and execute it. You are champ !
Identifying records using workflows and deleting it is efficient way to achieve this as mentioned by experts above
only suggestion if records are huge probably use escalation to delete the records and choose some non working hours to trigger this escalation, also check for time when server is idle means no CMDB jobs or AI jobs which imports data or do any transactions are in progress, this will avoid server performance issues as server load will be less
Data is so huge that workflow is not able to handle it . So, deleting the records from DB directly
I am not sure- Today i archived 80K INC from HPD:Help Desk Form (Copy and Delete). I also added a Flag in HPD:WorkLog form so that these child records are later archived.
When 80K records of HPD:Helpdesk is archived i was not able to find any of the corresponding work Info record.
I am still surprised as My Initial Test with 1-2 INC archival Work Info, record was there and i later archived on basis of flag, Now when chunk is Big i dont know why its deleted. while i see in your case you are trying to delete these work info records.