howto transfer contacts and associated calls between employees

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

Hello and thank you in advance.I tried to find an answer to my question with no success.
I have 7 employees=users of suiteCRM and 1 of the recently decided to change a job. I need to distribute accounts and associated data (contacts+calls) between the rest of users.
I know howto bulk update accouts themselves but I cant transfer other data with them.

2

Hi

what do you mean “associated with”? Is it “assigned to”?

You can go in the Contacts List View, filter by assigned to “old-user” and do a mass update reassigning.

3

Thats easy if you have 1 user to 1 user transfer. But I need to transfer first 24 accounts to user 1, second 24 accounts to user 2. And in the same time I need to transfer contacts of first 24 accounts to user 1 and calls of first 24 accounts to user 1 etc.
In this situation I can transfer first 24 accounts to user1 but later I cant filter contacts of that first 24 accounts to transfer them.

4

I would do it from phpMyAdmin, directly in the database (after making good backups).

Maybe you can achieve with a simple logic like this

  • reassign the accounts via the UI
  • go in the database and run SQL to update every contact to be assigned to the same user which has the account
  • same for calls etc
5

Just like I supposed. Thank you for your answer! i was just wondering if this task is common and is there any built-in way to solve it.

6

I managed to do like this. Please tell me if u see some terrible mistake here. Thank you!

UPDATE calls 
SET calls.assigned_user_id = (
    SELECT accounts.assigned_user_id 
    FROM accounts
    WHERE calls.parent_id = accounts.id
);

UPDATE contacts 
SET contacts.assigned_user_id = (

    SELECT accounts_assigned_user_id
	FROM (SELECT accounts.assigned_user_id AS accounts_assigned_user_id,contacts.assigned_user_id AS contacts_assigned_user_id, contacts.id AS contact_id FROM accounts INNER JOIN accounts_contacts ON accounts.id = accounts_contacts.account_id INNER JOIN contacts ON accounts_contacts.contact_id=contacts.id WHERE accounts.assigned_user_id <> contacts.assigned_user_id) AS myalias1
	WHERE contacts.id = contact_id		
) ;
1 Like
7

Eheh did it work? Then it looks just fine to me :slight_smile:

8

It did work, but it wiped assigned user id’s from contacts, where accounts.assigned_user_id = contacts.assigned_user_id, so it turns out, that users became able to see new transfered contacts, but not their own. (Our users have access only to their own contacts or ,say, contacts whith their user_id 's assigned) I will edit query and post the end result here after some tests later.

9

I didnt manage to find out what’s wrong with this query so far. Query simmulation in phpmyadmin goes just fine (judging by count of rows affected) but query itself shows different results. I’ll try loops instead of bulk update-select.
Help is appreciated :wink:

10

I’m sorry, I am no SQL guru, I can only get there by trial and error… good luck and please share your working query when you have one :slight_smile:

11

SQL query that worked for me

UPDATE  accounts a
INNER JOIN calls b
SET b.assigned_user_id = a.assigned_user_id
WHERE b.parent_id = a.id

UPDATE  accounts a
INNER JOIN accounts_contacts b ON a.ID = b.account_id
INNER JOIN contacts c ON b.contact_id = c.id
SET c.assigned_user_id = a.assigned_user_id
1 Like