Check for duplicates using xref

From Starfish ETL
Jump to navigation Jump to search

I needed to check for duplicate attachments in a system and remove the duplicates. I created a Job that used the same origin and destination and pulled all records from the Attachments table. I had a before operation that would read and write to the same xref list, thanks to the use of the newer SQLite xref list functionality, In this code, I check to see if the record I'm reading already exists in the XREF. If it exists, then I know it is a duplicate and can proceed to the Delete Stage. If it does not exist, then it is not a duplicate and I write the data to the XREF and skip the Delete Stage.

Sub VBScriptProcedure
	dim strExists
	strExists = xrefread("sf-attachments","@@ORG:ParentId@@@@ORG:NAME@@")
	If strExists = "Exists" Then
		LogMessage "Duplicate of: @@ORG:NAME@@"
		XrefWrite "sf-attachments","@@ORG:ParentId@@@@ORG:NAME@@","Exists"
	End if
End Sub