rmaitem and crmaitem GUIDs are duplicated and null

Problem reported by Stan Paulauskas - 12/30/2022 at 10:26 AM

Resolved

I'm reviewing data quality for rmaitem.ItemGUID and crmaitem.ItemGUID fields. I expect that these fields be unique and not null. I've found

2 instances in rmaitem where the ItemGUID is duplicated for a single RMA Number, and
34 entries in crmaitem where the ItemGUID field is empty.

Here is the base query (run against both rmaitem and crmaitem).

SELECT ItemGUID, Count(*) from rmaitem group by ItemGUID HAVING Count(*) > 1

Question: What is the recommended data remediation for this situation?

4 Replies

Reply to Thread

Terry Swiers Replied

12/30/2022 at 2:42 PM

Employee Post

> 2 instances in rmaitem where the ItemGUID is duplicated for a single RMA Number
Assuming the items are not serialized, simply edit the ItemGUID of one of them and change one of the characters to make it unique.

> 34 entries in crmaitem where the ItemGUID field is empty.
The following query will update the empty ItemGUID fields.

update crmaitem set ItemGUID = CURRENT_GUID where ItemGUID is null

Stan Paulauskas Replied

12/30/2022 at 4:21 PM

Terry, thanks for the fixes. Do you know how this could happen so that I can avoid it in the future?

Terry Swiers Replied

12/30/2022 at 5:53 PM

Employee Post

Nothing comes to mind from within Atrex. The values are assigned at object creation in memory, so it's almost impossible for them to be saved down blank without intentionally clearing the values. Any manual insertions into those tables via the ODBC driver?

Stan Paulauskas Replied

1/2/2023 at 5:10 AM

Not that I can think of. Our devs and ETL pipelines only use an RO connection to the database to prevent issues like this from happening. I'll chalk it up to a mystery, and have the ETL pipelines throw errors if it happens again. Thanks!

Back to Community Threads

Please leave this box unchecked

4 Replies

Reply to Thread

Related Knowledge Base Articles