One of the key factors to the maintenance of an iMIS Database is purging records that are "Marked for Deletion".  Purging old records is not only best practice, but is also recommended to keep the number of user records down.  This is especially useful if you have an MSDE license or a 50k record limit for iMIS. 

There are however, a few reasons why after running a purge from iMIS that records marked for deletion are still present.  These reasons are:

• The record has children records
• The record has a subscription record
• The record has an open balance
• The record has an open order (usually events)

Below are some SQL scripts to help you identify/check if this is the case with your records that won't purge. 

To identify if a record has children records you can run the following query:

• select * from name where co_id in (select id from name where status = 'D')

To identify if a record has a subscription record you can run:

• select distinct id from subscriptions where id in (select ID from name where status = 'D')

To identify if a record has an open balance on the invoice table you can run:

• select * from invoice where bt_id in (select ID from name where status = 'D') and balance != 0.00

To identify if a recod has any open orders:

• select * from orders where bt_id in (select ID from name where status = 'D') and stage not in ('CLOSED','CANCELED','COMPLETED')

These scripts are for identification purposes only; all clean-up should be done through iMIS. If you need assistance cleaning up your records, please file a support request at http://www.nimbleuser.com/support or email support@nimbleuser.com 

* Remember to Backup your database before purging your records. *