Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
Posts: 2
Registered: ‎07-10-2012

Mass Delete of Accounts

Our Sales Logix Database is synchronized with a legacy system.  We are about to purge dormant accounts on the legacy system which will remove thousands of old accounts.  I would like to clean up the sales logix database as well.

 

I know I can sign on as administrator and delete the account and it will automatically delete records in all the related tables, but this is impractical for thousands of accounts.

 

I can create a SQL table with the ACCOUNTID of each account to be deleted.  Is there a way to call a sales logix function that will do the deletes in all the related tables for each ACCOUNTID in the table.

Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Mass Delete of Accounts

To do it that way.. you will have to build a (very) custom VBScript.

 

Why don't you use:

    Tools -> Maintenance -> Purge Records? .. it's been there since the beginning of SLX.. (13+ years).

 

It will take a LONG time for it to run.. a very long time.. do it overnite and have no other apps touching the db from SQL or anywhere else. Shut down any SQL maint/backup operations, etc.

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
New Member
Posts: 2
Registered: ‎07-10-2012

Re: Mass Delete of Accounts

If I understand correctly, I can - for example - put some value in USERFIELD1, and then use that as the criteria for the purge.

 

When it says to delete 'ACCOUNT AND RELATED RECORDS' what are the RELATED RECORDS that get deleted?

Highlighted
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Mass Delete of Accounts

Every record in the db that has that AccountID as an FK and is linked via global join.. and it's a "cascade delete" setup.

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: Mass Delete of Accounts

"Every record in the db that has that AccountID as an FK and is linked via global join.. and it's a "cascade delete" setup."

 

Well.....it's a little bit more than that.....

1. "Every record in the db that has that AccountID as an FK and\OR is linked via global join. (TOID, FROMID in Association, EntityID in Address.....).

2. Records in tables that are subordinate to #1 are in turn deleted.....so when Opportunity's are KO'd then Opp Products, Contacts, Users, Competitors, etc, are all deleted for that specific Opportunity, even though there are zero AccountID's in that record.....

3. Parent\reverse Cascade deletes are programmed in......AccountSummary is deleted (technically it's a Parent or Left Join to Account, not a 1:1 table)......the Account linked by a Global Join to Account.ParentId is not deleted (even though it's a FK to AccountID and Global Join (see your original rule)).

 

When they mean Purge, they mean complete flush...

 

 

RJ Samp
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Mass Delete of Accounts

"Royal Flush" ;-)

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html