Delete a constituent record and what generates message

Bruce Boling

How does CRM know so fast that a record has revenue and cannot be deleted and sends back the message →

This constituent has associated revenue and therefore cannot be deleted.

I would like to know how and what is being checked so I can also use this check myself in sql code. When I get this message I look in all revenue related tables/screens and often find nothing. I have heard about revenue being moved and the person needing deleted still hanging around in a table somewhere causing the above message. If I could find where he is hiding I would remove this so I could delete this bad constituent record. Any info is appreciated. Thanks.

Aaron Thompson

Hi @Bruce Boling -

That error message is thrown by any of three constraint violations.

FK_REVENUE_CONSTITUENTID
FK_FINANCIALTRANSACTION_CONSTITUENTID
FK_FINANCIALTRANSACTIONLINEITEMADJUSTMENT_CONSTITUENTID

To check to see which is being hit, you can check those three tables for the constituent system record ID that you are trying to delete. If you cannot see the revenue from the front end, there may be some orphaned data in the database that needs to be cleaned up.

Bruce Boling

Aaron,

I found it in FINANCIALTRANSACTIONLINEITEMADJUSTMENT. I wonder if just removing the row in this table would be OK, in order to delete the constituent record. This was a duplicate process move from one person to another. I can try in our develop instance and see if it messes up the revenue adjustment history tab. This adjustment info is also on the new person adjustment history tab. It would be nice to delete these constituent records instead of just marking them inactive. You can't get to the adjustment history tab on the old person because it is based on having a gift and he has none now, they were moved to the new person. Are you marking them inactive? I would have to create a global change for my user to delete the row with a few checks like constituent is inactive and no other revenue. Thanks.

Aaron Thompson

@Bruce Boling I would definitely recommend testing in a non-production environment. It is possible that simply deleting the offending row will work. But there tend to be a lot of connected foreign keys, so you may not even be able to. You could also check to see if you can update the ConstituentID field on that table to match the ID of the constituent record you are keeping.

Typically when the merge is done, all of those GUIDs should be updated across the system.

I am personally a fan of deleting duplicate records, rather than marking them inactive, if at all possible. There may be cases where keeping the record is necessary or preferable. But deletion is preferable for me, if both are options.

Bruce Boling

Aaron,

When I look at the rows in FINANCIALTRANSACTIONLINEITEMADJUSTMENT I see the exact same row in the table already for the new person as well as the old person. It would appear the duplicate process copied the row and changed the constituentid to new person, so now there are two rows with all the same info except for the constituentid being different of course and the one-up id being different. When I go to the revenue on screen for new person the adjustment history tab (for specific gift) shows the money being moved between the two people by name. I would have to dig into the logic behind the screen to see where the two names are coming from whether by guid or by name (no guid needed). If by guid, then deleting the bad constituent row just might prevent the name from being found or the entire audit being displayed on new person. I know this is hard to follow. If I want to pursue deleting the constituent record I would have to find all this out. I might just flip the guid on the old person to my guid and see how that changes things being displayed and then to a made-up guid that does not exist. I will respond to this thread after doing this and tell you what happens. Thanks again for your knowledge and help.