How do I remove a deleted user from the cases filter list?

Hi,
I am searching already for some time, so maybe somebody here can help me:
I removed a user/employee that worked in support team. Then it still had cases assigned to him that were closed.
I reassigned those cases to some other account but now the user is still showing in the cases filter list.

How can I remove him from this list? the account is deletet, so what am I missing?

thanks and kind regards,
Thomas.

I’m not sure I understand exactly where it’s appearing, maybe a screenshot would help.

But if it’s appearing, what happens when you search for Cases with that name? Do you get any results? The filter might be showing you that option because there actually are records on the database pointing to that user, even if he no longer exists… you’d have to clean that up one by one, or even go into the database to clean it up.

ok, to clarify attached the picture of the quick filter in cases view that still shows me the removed user.

If I search for cases with that user, the list is completely empty. How could I see something else that’s still hanging on account that is already deleted?

How would I clean the database?

regards,
Thomas.

If you have phpMyAdmin installed on your server, you can have a look at how things are in the database. But please be extra-careful if you decide to change anything from within MySQL, you could break things or delete data. Make sure you have proper backups.

You would have to look at the ‘users’ table, search for that name, and make a note of the ‘id’ of that user.

Then go into the ‘cases’ table and search for cases that have field ‘assigned_user_id’ equal to that id.

If these rows all have the ‘deleted’ field set to ‘1’, then they won’t appear on the application. Eventually they will be purged by one of the Scheduler jobs. But you can try deleting rows with ‘deleted’ field set to ‘1’ for that user and see if that is enough to clear the user from the filter.

Hi , thank you for the quick reply.

I checked the database tables you mentioned, and the concerning user is definitely assigned some cases that do not have the deleted field set to “1”, but it shows “0”.

Hm. The cases are all closed. I’d like to reassign them to another account but when I search for closed cases with the one user that was deletet (and still shows) then I get an empty result.

Is there a way out? does the filter not show items assigned to a missing user?

You can just change it from within MySQL, after doing all the necessary backups,

I can’t go and check field names now, but it will look similar to

UPDATE cases SET assigned_user=‘very-long-new-usr-id’ WHERE assigned_user=‘very-long-old-usr-id’;

And next time remember to reassign everything before deleting the user, it will be easier… good luck.

Ok, thank you that pointed me in the right direction. But it’s still not done.

I found that the old ID was used in “assigned-to” field for deleted cases, thats why it never showed them When is this cleanup process supposed to run? I think it never does on my installation. I found so many old users…

To get rid of my problem I also replaced the ID in the field “created_by” and “modified_by”.

Those were the SQL commands I used:
UPDATE suitecrm.cases SET created_by = ‘xxxnew-user-id-to-setxxx’ WHERE created_by = ‘xxxold-user-idxxx’;
UPDATE suitecrm.cases SET modified_user_id = ‘xxxnew-user-id-to-setxxx’ WHERE modified_user_id = ‘xxxold-user-idxxx’;

Then I did “repair and rebuild” in SuiteCRM, the ID is not visible in cases table anymore, but the old name still shows in the Filter for cases!

And now? :blink:

How should I replace this without going into the database directly? It seems to me that SuiteCRM should really check into this with a proper cleanup the moment a user gets deleted. Or is just nobody other than us using the cases module? :unsure:

ok here is what I did to finally fix it: I edited the User SQL Entry and set the value “deleted” to “0”.

Then in SuiteCRM I deleted it again and now it doesnt show anymore in the filter list in cases…

Finally! B)

Hope this can help some others. But I really think the SuiteCRM team should look into this for some automatic update and the cleanup - at least some wizard would help that one can run manually.

kind regards and thanks again,
Thomas.

I am glad you sorted things out.

It’s very possible that the process of deleting users leaves some loose ends. It’s probably complicated to get everything right because the user id’s are spread all over the database.

I just wonder if something went wrong, or didn’t complete, in your initial user deletion (it seems to have worked better the second time).

But if you think this is a bug the right procedure would be to try and reproduce it on a standard install (like the online SuiteCRM demo) and then file an Issue in SuiteCRM’s Github explaining the detailed steps to reproduce it.

As an aside, you might want to check if your cron jobs are running, and specifically if the “Prune database” job is finishing without errors.

Thank you again.
I checked for the scheduled jobs and there the "prune database " job is set to inactive, most of the aothers are active and running.
Why is this the default? I would never mess around with stuff I don’t understand, so you say it is best to turn this job to active?

kind regards,
Thomas.

I thought it would be on by default, but you’re right, it defaults to “inactive”. I guess that’s because they want sysadmins to make conscious decisions about when to definitively remove deleted data from their databases.

When SuiteCRM deletes stuff, it doesn’t really delete rows from the database. It simply sets a “deleted” field to 1, and these records are ignored by the rest of the app. You can undelete records by going into the database and setting “deleted” to 0.

What the “Prune database in the 1st of month” does is go through a bunch of tables and really delete rows that have that field set to 1. I’d say it’s pretty safe to delete, but it’s your call after considering what kind of usage you have, and what kind (and frequency) of backups you have.

Leaving it off has a disadvantage: some tables tend to really fill up a lot. Occasionally you see people here in the forums that have systems almost unusable due to some performance problem, and when they diagnose it they find some overgrown table on the database is causing all the problems.