Click here to download the flowchart, which accompanies this tutorial topic.

i.  What is Caller ID?
Caller ID is the ability to automatically detect a caller's phone number (normally before the call is answered).

ii.  Making Caller ID work
For Caller ID to work, you must have the right ingredients:

1. Your phone line must have Caller ID facility. Check with your phone company.
2. Your voice modem must support Caller ID. Check your modem specification.
3. Items 1 and 2 above must be compatible.

1.  Your database
Let's assume you have a database table containing a record for each of your users. It might look like this:

Table : Users
Field Name	Data Type	Description
ID	Number	User primary key
Phone	Text	User phone number
Name	Text	User name

The Phone field should only contain numbers. The field data type is Text (rather than Number), so that leading zeros can be stored. Phone numbers should be stored without space or other punctuation characters, so they can be directly compared with Caller ID data (which is normally just numeric digits, without spaces).

2.  Begin a new flowchart
Run the SQLphone Designer program, to begin a new flowchart. Remember to select Options from the Prompt menu, and setup the prompt directory. You can leave all the standard prompt options unticked. Also, select Database from the Connect menu, and establish a connection to your database. Click here to recall the "New flowchart checklist" topic.

3.  Add memories
Press the <New Memory> button on the toolbar, to create two memories. These will store the ID and greeting message read from the database. See figures 1 and 2.

	Figure 1
	Figure 2

4.  Add prompts
Press the <New Prompt> button on the toolbar, to create two voice prompts. One is a message asking the user to enter their phone number (in the event that Caller ID is not available). The other is a message which gets played when an unknown phone number is detected. See figures 3 and 4.

When you create new prompts, you can press the <Sound recorder> button to record using your own voice, or you can press the <Generate...> button to record using a synthesized voice.

	Figure 3
	Figure 4

5.  Add a box to check the Caller ID
Add a new SQL Box to the flowchart. Name the box Check Caller, then click the SQL tab. Setup as shown in figure 5.

• In the SQL statement, $Caller means the value of the standard Caller memory, i.e. the user's phone number
• The database Phone field is a Text data type, so the Caller memory value must be enclosed in single quotes
• The database ID field will be fetched from the database, and stored in the ID memory
• The Message memory will contain the word "welcome", followed by the value of the database Name field

Figure 5

6.  Add a box to greet the user
Add a new Play Box to the flowchart. Name the box Welcome, then click the Play tab. Setup as shown in figure 6.

• The value of the Message memory (i.e. "welcome" plus the user's name) will get played

	Figure 6
	Figure 7

7.  Add a box to get the phone number
If Caller ID is not available, the user should be asked to enter their phone number. Add a new Digits Box to the flowchart. Name the box Get Caller, then click the Digits tab. Setup as shown in figure 8.

• The EnterPhoneNumb ("... please enter your phone number") prompt gets played to the user
• Digits entered by the user get stored in the Caller memory
• The maximum digits should be set to the maximum number of digits you expect to get for a phone number

Figure 8

8.  Add a box to report unknown phone number
Add a new Play Box to the flowchart. Name the box No Caller, then click the Play tab. Setup as shown in figure 9.

• The UnknownPhoneNumb prompt will get played

Figure 9

9.  Allow one attempt to check the phone number
If Caller ID is not available, the user will be given one attempt to enter a valid phone number using their phone keypad. This is achieved by setting a retry counter. Double click the Get Caller box, then click the Conditions tab. Setup the retry counter, see figure 10.

Figure 10

10.  Join the boxes
Join up the flowchart box conditions, and setup the Check Caller box as the flowchart starting point. See figure 11.

Figure 11

11.  Test the flowchart

1. Double click on the Caller memory, in the tree window. Enter a valid phone number (one that exists in the database) as the initial memory value. This will be used as the Caller ID. Then press F5 to run the flowchart. SQLphone should speak the user's name as a greeting.
2. Repeat the test, but this time setup an invalid phone number for the Caller memory. SQLphone should ask for a phone number to be entered. Enter a valid phone number. SQLphone should speak the user's name as a greeting.
3. Repeat the test, using an invalid phone number for the Caller memory. SQLphone should ask for a phone number to be entered. Enter an invalid phone number. SQLphone should speak an error message.