Identifying CTPS registered numbers on Raiser’s Edge

Published: 15th February 2018

Background & Aims and Objectives

The Corporate Telephone Preference Service (CTPS) is a central opt out register whereby companies can register their wish not to receive unsolicited sales and marketing telephone calls. It is a legal requirement that companies do not make such calls to numbers registered on the CTPS.

DDAR at The University of Manchester stores phone numbers of students, alumni and staff on a SQL based database.

In order to comply with CTPS; registered numbers recorded on the DDAR database need to be identified in such a way that prevents calling. To this end, a MS Access database has been created that stores the CTPS register and identifies these numbers on the Raiser’s Edge database.

Some constituents have consented for us to ring them on numbers that have been registered on the CTPS. A further process is required to ensure that these numbers have their CTPS status removed to allow us to continue telephone contact.

Step 1

From the desktop, load Microsoft Office Access.

In this case it is found under the Start menu – Microsoft Office – Microsoft Access 2010.

Select File from the main menu and then scroll to Open.

Go to the database location (O Drive/TPS)

And open the database: CTPS_Register.accdb

Step 2

Updating the CTPS table

The record of which number are CTPS registered needs refreshing occasionally.

Currently this has been agreed to being done twice a year in August and December. If this is not being done on this occasion, please move on to the section Using The Utilities on page 7 of this manual.

The register is located here

Username: UOM01

Password: Check with Mark Lay

If the message “You can no longer download the CTPS file.” appears, then please ask the Deputy Director, Development Services (Judith Alldred as of 10/12/2020) or the Head of Operations (Tom Jirat as of 10/12/2020) to ensure appropriate license payments have been made.

Use the Quick Download tab and download without spaces.

Click GO and download the file to O:\TPS

Step 3

Once the file from the TPS website is downloaded, right click on the downloaded folder and click Extract All. Save to the location O:\TPS\ with the filename ctps_ns_ddmmyyyy where ddmmyyyy is the day, month and year of download

Delete the table Ctps_ns from the database

Click on the File tab at the top of the database window and click the Compact and Repair Database button as shown below.

This should take about 1 minute

Click on the External Data tab of the database and select the Text File option.

Navigate to the folder just extracted above i.e. O:\TPS\ ctps_ns_ddmmyyyy and select the file tps_ns then click OK

The Import Text Wizard will open and select Next and Next again until the following screen appears:

The Field Name needs changing to Phone and Data Type needs changing to Text. Press Next.

On the next screen, change the selection to No primary key and then click Finish.

Step 4

Using the utilities

Open the form frm_dummy. This will run a series of processes that take almost an hour to run. Go and have lunch. At the end of this process, a query will be open that contains the records needing editing

The data produced needs exporting to a csv file.

This CSV should then be saved to \\\share$\h\pss\DDAR\1 Data Team\TPS\Imports

Step 5

Editing the data in Raiser’s Edge

This involves importing the csv created above into Raiser’s Edge

Open Raisers Edge, go to the import tab and find the import TPS_Update

Select the CSV file created and click Update Now. This will update the majority of the CTPS numbers. There will, however, be some exceptions. These will show in a report that should be printed out. It will refer to row numbers in the CSV that have not been imported (edited) correctly

Once the import has been closed, the CSV should be reopened and the exception rows highlighted. These records need to be opened in Raiser’s Edge and the reason for the failure to update investigated.