Documentation Note: add in the Sample list documentation

Since version 5.3.1, you have the ability to create a list of contacts you don't want to call or email: a Do-Not-Contact list (or DNC list). This DNC list, once created, is associated to any list you want and will automatically block all the contacts when there is a match with a DNC list. This DNC list can apply on CATI or WEB surveys.

To optimise the usage of a DNC list, it's recommended to use an external database (SQL / Access) which can be updated with scheduled task.

1/ The first step is to have your blacklist in SQL with two fields minimum (you can obviously add other fields if wanted): a unique ID field (necessary for scheduled tasks) and the phone or email field which contains the phone number or email addresses to not contact:

Please ensure that your Unique ID field is set as Identity (to allow it to be inserted into a SQL query later on):

2/ On the Supervisor side, make a right-click > "New Do-not-contact list" on the "Sample list" window.

3/ Give a name to your DNC list and select the "External Data" option:

4/ On the "External Data Details" dialog window, select the ODBC source linked to the database of your SQL server where your Blacklist is located and select the relevant table. Then, check your two special fields (here ID and PHONE - or your EMAIL field if it's a DNC list for web).

5/ You can add some scheduling properties in the "update" part of the sample list properties:

6/ Once properties are validated and the list is imported into CCA, the list appears with a specific icon (red cross). 

7/ Then, you need to associate this DNC list to an existing list. Open the properties of the sample list you want to match > "Numbers" tab > Click on the "+" icon on the Do-Not-contact lists and select your DNC list. 

Once the link is made between a list and a DNC list, contacts which match between the two lists will be set as unavailable on the sample list, and will not be called. 

Each time the DNC list is updated (manually or by scheduled task), an automatic check is done on all sample lists associated with this DNC list.

Furthermore, you can fill, during the fieldwork, your external SQL table with contacts who don't want to be called anymore. 

1/ First, add a specific callback result on the properties of your survey (here code 17 - "Do Not Contact Anymore") into the "Call Result Codes" tab:

2/ Then, on the sample list properties, Events tab, create an event on "Interview Finished", select the ODBC DSN for the SQL server which contains your BlackListTable and click on "Edit":

3/ The SQL query used is quite simple and is conditioned by the result code you have defined in the survey properties > Call Result Codes:

IF
[System.ResultCode]=17
BEGIN
INSERT INTO 
[BLACKLIST2] (PHONE) 
VALUES ('[List.Phone]')
;END

Alternatively, when using a MS Access database, the query is slightly different:

INSERT INTO [BLACKLIST2] ([PHONE]) SELECT TOP 1 '[List.Phone]' FROM [MSysResources] WHERE [System.ResultCode]=17

MSysResources is an hidden table in MS Access used for queries. If you get an error about MSysResources table not found when validating the event query, then follow the steps in this article.