Best way to see all records assigned to a user?

Hi peep´s

Ok, you know how Sugar asks

Would you like to reassign all of this user's records?

 when a user is made inactive. Well, what is the best way, beforehand, to find out what records are in question (and if there are any at all!)?

An advanced query maybe?

TIA,

KGM

Parents
  • Hi Kristjan Geir Mathiesen,

    An advanced report would be one way to find this information. For example, a simple query such as the following could be used:

    SELECT * FROM accounts
    WHERE assigned_user_id = 'user_id_here' AND deleted = '0';

    Another option is to create a row and columns report for each module you wish to check, setting the define filter show records assigned to the given user.

    Best wishes,
    Nathaniel

  • Thanks Nathaniel Ray for this. I´m looking for all records assigned to a certain user to the row and columns report per module isn´t the nice overlook I want (tried it and it´s too cumbersome).

    I can´t get this query to return any results. Not sure what I am doing wrong but also, how would a query for all modules look like (i.e. all records, regardless of module, assigned to a specific user)?

    And is there a way to have a "run-time filter" function (like that of regular Reports) in Advanced Queries?

    Thanks again,

    KGM

  • Hi Kristjan Geir Mathiesen,

    Are you sure that you are entering the user ID in the query? I apologize for any confusion.

    To find the user ID, go to Administration > Users > Select the requested username. The ID will be present near the end of the URL, after the = sign. If you are hosted on-site, then you can check the users table to find the ID in the corresponding row.

    Please note the above example does not include data from custom fields, as it is only meant to give an idea of what records are assigned to a given user. You may also need a more complex query if you want to retrieve all of the requested data in a single report.

    The easiest method would be to edit the existing query according to which module you want to check, swapping "accounts" out with the name of the given module, e.g.:

    SELECT * FROM contacts
    WHERE assigned_user_id = 'user_id_here' AND deleted = '0';

    Best wishes,
    Nathaniel

  • Hi again Nathaniel Ray

    That was totally my bad - I insered the user name, not user ID. Duh on me...  Thanks.

    One more, can I join two queries together? Like if I wanted to look for all accounts AND all contacts:

    SELECT * FROM contacts
    WHERE assigned_user_id = 'user_id_here' AND deleted = '0';

    SELECT * FROM accounts
    WHERE assigned_user_id = 'user_id_here' AND deleted = '0';

    Thanks,

    KGM

  • Hi Kristjan Geir Mathiesen,

    I'm glad you got it working! At this time it is not possible to include more than one custom query on a single advanced report. We have enhancement request #79361 open to add this functionality into the application. I apologize for the inconvenience.

    Best wishes,

    Nathaniel

  • Hi Nathaniel Ray

    Thanks for clarifying that. I was trying to combine the two with a join but thought I was just not clever enough. Good to know for fact that it´s not possible but at the same time sad that can´t be done presently. 

    Thanks for creating the enhancement request.

    KGM

Reply Children
No Data