Page 1 of 1
How can I move all old user groups to the new _Default_
Posted: Thu Dec 18, 2014 4:41 am
by mlarsen
I have a lot of users who are part of a one person group with the group name of their email address. Is there a bulk way of converting all of them to the new _Default_ Group?
I tried the following
update ac_UserGroups
set groupid = 383 ' _Default_ group
where groupid <> 383
and groupid > 9 ' all my admin groups are below nine.
I get this error
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.ac_UserGroups' with unique index 'ac_UserGroups_CK'.
The statement has been terminated.
I don't see a way to do this in the admin either other than one at a time and that is WAY to many...
Thanks
Re: How can I move all old user groups to the new _Default_
Posted: Fri Dec 19, 2014 3:19 am
by jmestep
It looks like you might need to add a criteria that the userid is not already in group 383. It looks like Able adds users to the default group at upgrade time.
Re: How can I move all old user groups to the new _Default_
Posted: Fri Dec 19, 2014 5:18 am
by Naveed
Use this query, it will add new user -> group relation for all users except admin users (groupId > 9) and anonymous users and users which are already not assigned to default group.
Code: Select all
INSERT INTO ac_UserGroups(UserId, GroupId)
SELECT UserId, 383
FROM ac_Users WHERE UserId NOT IN
(SELECT DISTINCT U.UserId FROM ac_Users U LEFT JOIN ac_UserGroups UG ON U.UserId = UG.UserId
WHERE (UG.GroupId = 383 OR UG.UserId IS NOT NULL OR UG.GroupId < 9))
AND IsAnonymous = 0
Re: How can I move all old user groups to the new _Default_
Posted: Fri Dec 19, 2014 6:35 am
by mlarsen
Thanks for the script but it returns 0 rows affected and in fact did not affect any of the user group assignments. Any other ideas?
Re: How can I move all old user groups to the new _Default_
Posted: Fri Dec 19, 2014 2:13 pm
by rmaweb
Are you going to use the old groups anymore? If not just delete them and if there are users in that group, the admin page will go to another that will allow you to select the group you want those users to be moved to.
Re: How can I move all old user groups to the new _Default_
Posted: Fri Dec 19, 2014 4:22 pm
by mlarsen
Yes I can do that but each user is in their own group. so I have LOTS of groups each with 1 member. I don't know if that was the original design of Able or a result of my accounting system integration with Able but that is where it is and I don't have the time to change the group of each user one at a time. I was hoping for a script to move them all to the _Default_ Group and then I can just delete the unused groups.
Re: How can I move all old user groups to the new _Default_
Posted: Fri Dec 19, 2014 7:28 pm
by rmaweb
Ah. Well if you don't have a time emergency , I might be able to have a solution available after Christmas that you can use.
Re: How can I move all old user groups to the new _Default_
Posted: Mon Dec 22, 2014 4:12 am
by jmestep
Did you look at some of the users in the admin to make sure they are not in the default group already? Or do you have some custom code to create users that overrode the upgrade?
Re: How can I move all old user groups to the new _Default_
Posted: Tue Dec 23, 2014 3:03 pm
by mlarsen
I resorted to brute force and got everyone in the office removing the people for the groups and assigning them to the default. tedious but done.