07-10-2012 06:40 AM
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.
07-10-2012 06:59 AM
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.
07-10-2012 08:20 AM
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?
07-10-2012 08:59 AM
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.
07-10-2012 04:37 PM
"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...
07-10-2012 05:03 PM