Populating the DNC Table from a Script Run
CallScripter's built in DNC table (dbo.tbl_outbound_DNC) is not populated automatically. When importing call lists you are given the opportunity of checking the "DNC contacts" option under "Additional Import Options" when creating your import template.
In order that records with an outcome of DNC (or any number of reasons, e.g., DNC Wrong Number, DNC Deceased, DNC TPS, etc) , can be added to the table, you will need add a database Get control onto the end page of your script and use an insert query to insert the current call information into tbl_outbound_DNC.
The structure of dbo.tbl_outbound_DNC
In order that the table can be populated, your query will have to insert the information in the correct format. You should consider using validation on your telephone number field to ensure it is entered in the correct format, i.e., no spaces, no preceding country codes, etc.
Once DNC records are in the table, the Call List Import process will check the telephone numbers being imported against those in the DNC table and will NOT import any records where the telephone number being imported matches that already in the table.
All fields except CustomRef1 and SessionID are required, cannot be null.
Populating the CallScripter DNC Table using DatabaseGet
1. Add a Database Get statement to the End page of your script.
2. Go to Advanced Properties
3. In the database Integration window, choose CallScripter as the data source (uncheck the poll network for SQL servers box) Click Next
4. Enter your if and insert statements. (See example below)
5. Click Next
6. Enter Test Values. As this is an insert, rather than a select statement, there is no need to match a value from the table you are selecting from, only to match the parameters set out in your query. In the example below, I have entered a test value of '1' for each field, with the exception of the outcome which must equal "DNC" for the if statement to be true and for a row to be inserted into the database table:
Example Insert Statement
The query below illustrates how to populate the table with data where you only have one DNC value. We don't want every completed call ending up in the DNC table, so we need to use an If statement .
-
[AAA] = the name of your outcome field
-
BBB = whatever value you are using in your outcome field to determine whether the record should go in the DNC table, e.g., "DNC", "Do Not Contact", etc.
-
[CCC] = the name of the script field containing the telephone number
-
[DDD] = the name of the script field containing the customer name
if([AAA]='BBB')
Begin
INSERT INTO [YOUR_DB_NAME].[dbo].[tbl_outbound_DNC](TelephoneNumber, Name, InsertDate, ExpiryDate, Reason, CallListID, Outbound_ID, AgentName, Campaign_id, SessionID)
VALUES ([CCC], [DDD], GetDate(), dateadd(year, 10, GetDate()), [AAA], [var_csCallListID],'0', [var_csAgentName], [var_csCampaignID], [var_csSessionID])
end
Variations on the Insert Statement:
If you have more than one DNC value you can use IF([AAA] IN ('Value1','Value2',…)
If you need to insert a reason into your table, you can use the reason column which is set to '0' in the value list in the above statement.
If you wish to capture anything else, you can use the CustomRef1 field in the table. This should be inserted in the corresponding location in the insert into statement as in the value statement, i.e., as long as you put it on the end of each statement it will work.
