Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Thursday, July 2, 2020 
 
slxdeveloper.com Community Forums  
   
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
 Administration Forums - Database Administration
Forum to discuss SQL Server or Oracle database administration related to SalesLogix databases. View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to Database Administration | New ThreadView:  Search:  
 Author  Thread: Delete all ad-hoc groups for Accounts and Contacts
Marcos Orfila
Posts: 40
 
Delete all ad-hoc groups for Accounts and ContactsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Oct 11 10:04 PM
fiogf49gjkf0d

Hi.

I need to delete all ad-hoc groups for Accounts and Contacts.
I was thinking on this:


delete from sysdba.ADHOCGROUP
where ENTITYID in (select CONTACTID from sysdba.CONTACT)
or ENTITYID in (select ACCOUNTID from sysdba.ACCOUNT);

delete from sysdba.PLUGIN
where TYPE = 8
and (FAMILY like 'Contact' or FAMILY like 'Account')
and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP);



The strange thing (at least to me) is that when I get the count for both statements, the numbers are very different:

select COUNT(*)
from sysdba.PLUGIN
where TYPE = 8
and (FAMILY like 'Contact' or FAMILY like 'Account')
and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP);
==> Result: 132

select COUNT(*)
from sysdba.ADHOCGROUP
where ENTITYID in (select CONTACTID from sysdba.CONTACT)
or ENTITYID in (select ACCOUNTID from sysdba.ACCOUNT);
==> Result: 13688


Do you think the two delete sentences above will correctly delete the ad-hoc groups for Accounts and Contacts?

Regards,

   Marcos

[Reply][Quote]
John Paul Welther
Posts: 27
 
Re: Delete all ad-hoc groups for Accounts and ContactsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 Jan 12 1:21 PM
fiogf49gjkf0d

sysdba.PLUGIN contains one record for each adhocgroup 


sysdba,adhacgroup contains one record per member per adhac group. If I create a group with 5 members there will be one record in sysdba.plugin and 5 in sysdba.adhacgroup. so the numbers you are seeing are accurate. 


If you are going to use these 2 statements just make sure to run 


delete from sysdba.PLUGIN 
where TYPE = 8
and (FAMILY like 'Contact' or FAMILY like 'Account')
and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP);


Before you run the other one else you will not remove any plug-ins.  

[Reply][Quote]
RJ Samp
Posts: 973
Top 10 forum poster: 973 posts
 
Re: Delete all ad-hoc groups for Accounts and ContactsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 Jan 12 2:18 PM
fiogf49gjkf0d

Excellent Mr. Welther, that's how we've been doing it.....better safe than sorry....

[Reply][Quote]
Raul A. Chavez
Posts: 1300
Top 10 forum poster: 1300 posts
 
Re: Delete all ad-hoc groups for Accounts and ContactsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 Jan 12 2:37 PM
fiogf49gjkf0d

The other item to key off is DATACODE.


Otherwise you won't remove Releases of these groups.


 


The best way to determine ADHOCGROUPS (not just the ones with Associated Data):


SELECT * FROM PLUGIN WHERE TYPE = 8 AND DATACODE IS NOT NULL AND DATACODE <> ''


 


When an Adhoc Group is created, it sets the DATACODE to the PLUGINID.


When releases are created, the releases set the BASEDON to match the PLUGINID of the Original Group (this is the same for all PLUGINS), but in addition, on ADHOC groups the DATACODE is set to the PLUGINID of the Original group.


Also, I do recall that when you Copy ADHOC groups, the BASEDON may not be set (as this is a new group path), but if it is an ADHOC group, the DATACODE field gets populated (again, with the original PLUGINID).


Any entities added to the Groups get added to the ADHOC group table with the DATACODE as the GROUPID.


 


 

[Reply][Quote]
John Paul Welther
Posts: 27
 
Re: Delete all ad-hoc groups for Accounts and ContactsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 Jan 12 2:44 PM
fiogf49gjkf0d

Thanks RJ and Raul


 


While we are on the subject of extra groups you can run the 2 scripts below to remove all the unreleased groups from retired users. Then remove all the adhacgroup records that no longer match a group. This is a good thing to do every once in a while as eventually these 2 tables will acquire a lot of unused data. (the SLX provider will parse these)


Delete from sysdba.PLUGIN
Where PLUGINID in (
Select PLUGINID from sysdba.PLUGIN p
INNER JOIN sysdba.USERSECURITY us on p.USERID = us.USERID
where p.TYPE = 8
and us.TYPE = 'R')
-- you can add “and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP)” if you only want to remove the adhac groups


Delete from sysdba.ADHOCGROUP
Where GROUPID not in
(select GROUPID from sysdba.ADHOCGROUP
Where GROUPID not in (select PLUGINID
from sysdba.PLUGIN p));


 

[Reply][Quote]
 Page 1 of 1 
  You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

   Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
 

 
 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2020 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 7/2/2020 6:53:24 AM