Outdialque - When Phone Number Is Not Unique

[pdpk]

We have a VoiceGuide application which uses the OutDialQue database to make outgoing calls. The OutDialQue database is being populated using an .ASP page, which extracts phone numbers from our customer database, and puts them into the CallQue table in the OutDialQue database.

 

We have situations where the SAME phone number can legitimately be in the OutDialQue database more than once - i.e. where two different people at the same address both need to be called, and given different information.

 

We have added a field to our database, which "grabs" the number that the OutDialQue database has generated for the ID field in the CallQue table when the ASP page runs. (its an autonumber). and it puts this number into a field in our Customer Database.

 

When the call is made, and the VGS script kicks in, the first thing it does is open an ODBC connection to our customer database, to go fetch the relevant data for the number that has been dialled.

 

Our Select statement goes something along the lines of SELECT * FROM [Customer Database] WHERE CustomerPhoneNumber = '$RV[CALLED_NUMBER]'. (simplified for ease of understanding)

 

This currently returns ALL the records that have that phone number in them. We want to ONLY select the one from the SAME ROW in the CallQue Database that VoiceGuide has ACTUALLY used at runtime. Therefore we want our Select Statement to be like this:

 

SELECT * FROM [Customer Database] WHERE CustomerPhoneNumber = '$RV[CALLED_NUMBER]' AND ID='$RV[iD]'

 

Where '$RV[iD]is the ID number on the SAME ROW as the phone number that VoiceGuide has used to make the call.

 

So, does VoiceGuide have a "built-in" variable, like '$RV[CALLED_NUMBER]' which stores the row number or the ID of the record that has been used in CallQue?. We tried using '$RV[iD]' in the Select statement, but it didn't recognise it.

[SupportTeam]

When adding entries you can for each entry specify a unique Result Variable. This result variable will be stored in the database's "RV" field and you may then search for it in the SQL query.

 

Looking at the OutDialQue database structure you can see that there is also a unique ID assigned to each record when it is created, and once the record you want is identified by searching for the particular Result Variable then you can use just that value in the ID field to easily reference the database record in the following SQL queries.

[pdpk]

My ASP page already populates the RV field with [iD], this puts the value of the ID field into the RV field for each record.

 

But how exactly do I pick up this in my SQL statement in VoiceGuide?

 

What's the correct syntax to use to get the value that is sitting in the RV field, for the number that VoiceGuide has actually dialled?

[SupportTeam]

Have a look at what ends up in the database - just look at the fields and you should be able to see how to phrase the SQL query...

 

eg: if you add a Result Variable : "ID" with a value of "123456" then the field "RV" in the database would contain a string like this:

 

[iD]{123456789}

 

(amongst other possibly defined RVs) so you can use sub-string matching SQL statement

 

SELECT ID FROM CallQue WHERE RV LIKE '%{123456789}%'

 

The ID retrieved is the unique MS Access auto-generated number (have a look at the database table structure).

 

Note how we do not try to include "[iD]" in the search string - the square brackets have special meaning when using the LIKE operator - there is a way to match square brackets as well but we'll not get that far into it here.

 

Here is some more sample VBScript code which should illustrate this better:

const adOpenForwardOnly = 0
const adOpenKeyset = 1
const adOpenDynamic = 2
const adOpenStatic = 3

set cn = CreateObject("ADODB.Connection")
set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\VG\data\OutDialQue.mdb'"
if cn.State = 1 then
 msgbox "start"

 sSQL = "SELECT ID FROM CallQue WHERE RV LIKE '%{123456789}%'"

 rs.Open sSQL, cn, adOpenStatic
 if rs.RecordCount > 0 then
   iAdID = rs.Fields("ID").Value
   msgbox iAdID
 else
   msgbox "nothing"
 end if

 msgbox "end"

else
 msgbox "no DB connection"
end if

cn.Close
set rs = Nothing
set cn = Nothing

 

Question 2:

What's the correct syntax to use to get the value that is sitting in the RV field, for the number that VoiceGuide has actually dialled?

You would use the $RV[iD] to get the value of your RV, so then your VBScript code could be:

const adOpenForwardOnly = 0
const adOpenKeyset = 1
const adOpenDynamic = 2
const adOpenStatic = 3

set cn = CreateObject("ADODB.Connection")
set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\VG\data\OutDialQue.mdb'"
if cn.State = 1 then
 msgbox "start"

 sSQL = "SELECT ID FROM CallQue WHERE RV LIKE '%{$RV[ID]}%'"

 rs.Open sSQL, cn, adOpenStatic
 if rs.RecordCount > 0 then
   iAdID = rs.Fields("ID").Value
   msgbox iAdID
 else
   msgbox "nothing"
 end if

 msgbox "end"

else
 msgbox "no DB connection"
end if

cn.Close
set rs = Nothing
set cn = Nothing

 

(for clarity's sake can you please call your RV something other then "ID"...)

[pdpk]

I had a look at what ends up in the database and the RV field does not

 

"end up with something like: [iD]{123456789}"

 

it ends up with 34 or 124 or 236 or whatever the actual ID number is - it will be different for each record added.

 

So the first thing is that there is no [iD] in the field - should there be???

 

I tried using your example. Here is my complete sql statement: (please bear in mind that in my case, I am using two databases - one with my Customer information and the CallQue database. My database has a join built to the OutDialQue Database, to the CallQue table to link the two tables together.)

 

Select AutomatedCalls.AirlineName, AutomatedCalls.PaxName, AutomatedCalls.ETA, AutomatedCalls.FirstText, AutomatedCalls.SecText, AutomatedCalls.Internal_Comments, AutomatedCalls.Internal_Comments1, AutomatedCalls.Deldate, AutomatedCalls.ID from AutomatedCalls, CallQue WHERE AutomatedCalls.Phone = '$RV_CALLEDNUMBER' AND CallQue.RV LIKE '%{$RV[iD]}%'

 

At runtime, it took the Fail path from the Query Database Module.

 

The log file contained the following:

 

About to execute a Retrieve type operation [sELECT AutomatedCalls.AirlineName, AutomatedCalls.PaxName, AutomatedCalls.ETA, AutomatedCalls.FirstText, AutomatedCalls.SecText, AutomatedCalls.Internal_Comments, AutomatedCalls.Internal_Comments1, AutomatedCalls.Deldate, AutomatedCalls.ID from AutomatedCalls, CallQue WHERE AutomatedCalls.Phone = '01252 1234567' AND RV LIKE '%{}%']

 

No Records Found.

 

Therefore, I conclude that '%{$RV[iD]}%' in the example is not quite correct for my situation.

 

Is there any other way round this please?

 

Basically I need to be able to identify which record VoiceGuide has taken the phone number from, so that I can retrieve the correct customer information for the number that has been dialled. Bearing in mind that the same phone number may be listed for several different customers (in the case of calling a hotel or a business).

[SupportTeam]

I had a look at what ends up in the database and the RV field does not

 

"end up with something like: [iD]{123456789}"

 

it ends up with 34 or 124 or 236 or whatever the actual ID number is - it will be different for each record added.

Please .ZIP up and post your OutDialQue.mdb database here (after you have inserted into it the new record to be dialed).

 

Therefore, I conclude that '%{$RV[ID]}%' in the example is not quite correct for my situation.

it looks like you have not defined an "$RV[iD]" - you need to define and RV (or rely on an RV created by VG) before using it...

 

I'd recommend you just post your entire script here and indicate how you insert the records into the database & provide a sample of what the DB looks like after insertion - then we can tell you where things are not correctly specified.

[pdpk]

I attach a zip file which contains the following:

 

OutDialQue.mdb

Calls.mdb

index.asp

vginterface.asp

pk_logo.jpg

top_banner.jpg

ogs1.vgs

 

The files on my system are placed in the following folders:

 

d:\software\VoiceGuide\data\OutDialQue.mdb

c:\INetPub\WWWRoot\expb\data\calls.mdb

c:\INetPub\WWWRoot\expb\index.asp, pk_logo.jpg, top_banner.jpg, vginterface.asp

d:\software\VoiceGuide\scripts\ogs1.vgs

 

I have set up an Access Database DSN called expbout, which is linked to the calls.mdb database and is used by my VoiceGuide Script: ogs1.vgs.

 

The two ASP Pages handle population of the OutDialQue database for me by basically looking at the records in the calls.mdb database, and seeing if they meet certain criteria (meaning a call needs to be made). If they do, then vginterface.asp opens the CallQue database and adds a new record.

 

When my Outgoing Call VoiceGuide Script (OGS1.vgs) starts, it links to the calls database via odbc (using a Data Query module) to retrieve the information for the customer that has been called.

pkpd.zip

[SupportTeam]

In OutDialQue.mdb the inserted record has just a string of "54" in the RV field.

 

you should place in that field something like this:

 

[MyCallId]{123456789}

 

Then you will be able to use:

 

$RV[MyCallId]

 

in your script (it will be replaced with a value of 123456789)

 

and then when doing SQL queries to find your record in OutDialQue.mdb you can do the search:

 

SELECT * FROM CallQue WHERE RV LIKE '%{123456789}%'

[pdpk]

Do I have to actually specify a fixed number between the curly brackets? like 1234567, I need each record to have its own number....so should I be able to use a variable there instead, like {ID}??

[SupportTeam]

The number in the curly brackets is the actual value of the variable.

(the name of the variable is specified within the square brackets)

 

One step at a time:

 

- You can set the value to be whatever you want

 

- and you should use your database insertion routines to make sure that this value is unique

 

- which will then allow you to easily find the relevant row in the database

 

- as from within the VG script you will be able to find out what is that value by using $RV[MyCallId]

[pdpk]

Thank you for your reply. It is now working!

 

My ASP script now has the following line to add the RV field:

 

objRS_CallQ("RV") = "[MyCallId]" & "{" & objRS_calls("ID") & "}"

 

This populates the the RV field with [MyCallID]{15} (or whatever the current ID happens to be).

 

My SQL statement has ended up with:

 

SELECT * FROM AutomatedCalls WHERE AutomatedCalls.Phone = '$RV_CALLEDNUMBER' AND AutomatedCalls.ID = $RV[MyCallID]

 

Thank you again for all your help on this topic.