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	Text	Unique user identification number
PIN	Text	User security PIN
Name	Text	User name

A user will enter their ID and PIN using a phone keypad. Therefore these fields should only contain numbers. The field data type is Text (rather than Number), so that leading zeros can be stored.

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 a couple of new memories. One will store the ID number entered by the caller. The other will store the user's name read from the database. We'll ignore the PIN for now. See figures 1 and 2.

	Figure 1
	Figure 2

4.  Add prompts
Press the <New Prompt> button on the toolbar, to create a couple of new voice prompts. One is a message asking the user to enter their ID. The other is a message which gets played when an invalid ID is entered. 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 get the ID
Add a new Digits Box to the flowchart. Name the box Get ID, then click the Digits tab. Setup as shown in figure 5.

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

Figure 5

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

• In the SQL statement, $ID means the value of the ID memory, i.e. the ID number entered by the user
• The database ID field is a Text data type, so the ID memory value must be enclosed in single quotes
• The database Name field will be fetched from the database, and stored in the Name memory

Figure 6

7.  Add a box to report correct ID
Add a new Play Box to the flowchart. Name the box OK ID, then click the Play tab. Setup as shown in figure 7.

• The value of the Name memory (i.e. the user's name) will get played

	Figure 7
	Figure 8

8.  Add a box to report invalid ID
Add a new Play Box to the flowchart. Name the box Bad ID, then click the Play tab. Setup as shown in figure 9.

• The UnknownID prompt will get played

	Figure 9
	Figure 10

9.  Allow the user to make a mistake
If the user enters an incorrect ID, we'll allow them to have another go. This is achieved by setting a retry counter. Double click the Get ID box, then click the Conditions tab. Setup the retry counter, see figure 11.

Figure 11

10.  Join the boxes
Join up the flowchart box conditions, and setup the Get ID box as the flowchart starting point. See figure 12.

• The Tries > 2 condition does not join to another box. This causes SQLphone to hang-up after 2 unsuccessful attempts to enter ID
• The NoDigits condition loops back to the same box. So the user gets another chance to enter ID, if they do not press any digits within the configured timeout (4 seconds, see figure 5)
• The NoData condition occurs when the SQL statement fails to fetch a record from the database, i.e. when the ID is invalid
• The Get ID, Check ID and Bad ID boxes form a loop

Figure 12

11.  Test the flowchart
Press F5 to run the flowchart. The output window displays the call progress. See example figure 13.

• The EnterID prompt will be played through your sound card
• Use your keyboard to enter an invalid ID. This causes the UnknownID prompt to be played
• Use your keyboard to enter a valid ID. This causes the user's name to be spoken

Figure 13

12.  Add elements to check the PIN
Requesting and checking the user's PIN is very similar to checking the ID.

Add a new memory to store the PIN:

• Memory name = PIN, Description = "Stores the PIN entered by the user"

Add a couple of new prompts:

• Prompt name = EnterPIN, Description = "Please enter your security PIN"
• Prompt name = UnknownPIN, Description = "Sorry, that PIN is incorrect"

Add new boxes:

• Digits Box name = Get PIN. Plays the EnterPin prompt and stores digits in the PIN memory. Maximum digits = 4
• SQL Box name = Check PIN. SQL statement uses both ID and PIN to fetch a record. See figure 14
• Play Box name = Bad PIN. Plays the UnknownPIN prompt
• Play Box name = OK PIN. Speaks the user's name

Join the boxes together. See figure 15.

Figure 14