CRM Software - Developer Notes

CRM Software - Delete Customer

Download CRM Software Free 30 Day Trial
Download CRM Software Free 30 Day Trial

CRM Developer Notes

These notes are intended to help Software Developers and IT Professionals to Support Amphis Customer CRM. These notes are provided for information only. Amphis Software does not guarantee that these notes are up to date or that they will work for your intended purpose. We recommend that you back up your data before running any scripts or using any of the information provided here. Use of this information is at your own risk.

Delete CRM Database Customer Record

You can manually delete a Customer Record by selecting a Customer in the Search Results and selecting Delete Customer from the Customer menu, but what if you want to delete many customers in one go eg all customers with a particular status ? For that, you need a script which you can load into the Microsoft Access Database customer.mdb and run.

You could run this script if you know what status id you want to delete. This script deletes all Customer records from the CUSTOMER table that have the given Status Id.

DELETE FROM customer WHERE customerstatusid=1

You can find the customer status id to use in lookup_customerstatus - see CRM Database Design Lookups

Customer Tables

But this only deletes the data from one table. Customer data is held in many tables. To delete all the data for a customer you would also need to delete data from at least the following tables.

APPOINTMENT
CONTACT
CREDITNOTE_INVOICE
CUSTOM
CUSTOMERJOB
GROUPCUSTOMER
HISTORY
INVOICE_TRANSACTION
NOTES
REMINDER
TASK

In order to do that, you need to get a list of customer Ids to delete, and then apply the delete to each table.

Get Customer Ids to delete

The following script gets a list of customer ids where the customerstatisid = 1

SELECT customerid FROM customer WHERE customerstatusid=1

Delete Customer Record

You can delete the relevant rows from each table like this :-

DELETE FROM <table> WHERE customerid IN ( SELECT customerid FROM customer WHERE customerstatusid=1 )

Just substitute the name of a table instead of <table>. Make sure you delete from the Customer table last.

Here is an example deleting the relevant rows from the History table.

DELETE FROM history WHERE customerid IN ( SELECT customerid FROM customer WHERE customerstatusid=1 )

To change the customer Ids to delete, just change the Select statement within the brackets.

For example this selects all customers where the business name is TEST and the Customer Status Id is 1.

SELECT customerid FROM customer WHERE companyname='TEST' AND customerstatusid=1

Please make sure you make a backup of the database before deleting any data and test your script thoroughly on some test data before running it against your real data.

Download CRM Software Free 30 Day Trial
Download CRM Software Free 30 Day Trial