Verb Phrasing
If you can use verbs to describe a relationship between entities, a verb phrasing will let English Query properly understand the relationship. You'll probably use verb phrasings frequently. Such phrasings as
- Employees sell products (to customers)
- Students study courses
let English Query answer the following questions:
- "Who sells Chai to Belk's?"
- "What does Bill Mason sell?"
- "What is sold to The General Store?"
- "What does Mary study?"
- "Who studied English?"
As with other phrasings, you can add as many as three prepositional phrases with verb phrasings. Verb phrasings can be of five types, as Figure 10 shows. The Northwind demo model contains many good examples of verb phrasings.
Here's a tip: Use the active voice instead of the passive voice when defining the verb phrase. If you're entering a verb phrasing that has one or more prepositions and is missing a subject or object, you might be writing (or thinking) in the passive voice. For example, you could better word the passive phrasing "Products are sold (to customers) (by employees)" actively as "Employees sell products (to customers)." When you use active voice to implement verb phrasings, English Query can answer questions issued in either the active or passive voice.
Command Relationships and Command Phrasings
Usually, English Query converts English statements into SQL statements that it uses to select information from the database. However, you might need to let users make requests other than requests to display information. These requests might include any of the normal transactional operations your system allows, such as changing data or requesting a form to allow data additions or changes. Command relationships let you create such special instructions as:
- Raise Bob Smith's salary by $5000.
- Run the nightly batch at 10 p.m.
- Add an employee. (This request might return the Add employee dialog box.)
A command is a type of phrasing, so you can add it the same way you add any other phrasing. You must give command relationships names, which English Query will return when the command relationships are invoked. Your application can then respond however you want it to—for example, by calling a stored procedure or running a program. Let's say you want to let a user give the command "Send 10 boxes of Alice Mutton to ALFKI." English Query needs to understand this command and return the information necessary for your application to send the product Alice Mutton to the customer whose customer ID is ALFKI.
To create the appropriate command relationship, open the Add relationship dialog box by right-clicking the Product entity, then add the Customer entity to the relationship. Next, select Add Phrasing, and complete the Command Phrasing dialog box, which Figure 11 shows. Notice the Sentence type drop-down list box. Figure 12 lists the three sentence types English Query allows as Command Phrasings.
Now you need to account for the units—in this case, 10. Double-clicking the entity you want to provide a quantity for—in this case, products—produces the role dialog box that Figure 13 shows. Selecting the first check box requires you to provide the product being purchased. Selecting the second check box lets you specify the quantity of product to send to the customer. You can also enter a prompt for English Query to display if the user's request doesn't specify an amount.
Finally, in the Command tab that Figure 14, page 62, shows, you can give the command a name and see the parameter names and IDs. Parameters come in two types: fields and values. Complete the form as Figure 14 shows, and click OK. In this example, parameter IDs 1 and 2 are fields. English Query returns a SQL query for each field. Executing query 1 returns the product ID, and query 2 returns the customer ID. Parameter ID 0 in this example is a value and is the quantity associated with the user's command. If a user requests "Send 10 of Alice Mutton to ALFKI," the value of parameter ID 0 is 10. As Figure 15, page 62, shows, your application now has the name of the command (purchase), the quantity parameter, and two SQL statements that will return the product and customer ID, which is enough information to call a stored procedure or business object to complete the sale.
Command phrasings let you add custom functionality, but the English Query Help file contains a rather long list of verbs to avoid when using command relationships. Before you add a command relationship, make sure you check this list. (Search for "verbs to avoid" to find the information.)
The Suggestion Wizard
Now that you know how to do things the hard way, you'll be pleased to know that English Query gives you an easier way to add new relationships while testing questions. When you're testing an English Query model, Microsoft gives you access to a tool—the Suggestion Wizard—that can help you add relationships. In the screen in Figure 15, the fifth icon on the toolbar looks like a magician's wand. If you ask a question that the English Query semantic model doesn't understand, select this icon. The Suggestion Wizard prompts you for enough information to create the proper phrasing. The wizard isn't 100 percent accurate, but it is a great help when you're getting started.
For example, I asked, "Who bought Chai?" in a small test model. When English Query couldn't answer the question, the Suggestion Wizard displayed the dialog box that Figure 16 shows. After I added the appropriate relationships in response to the wizard's suggestions, English Query could answer my question.
You can even pass questions to the Suggestion Wizard in batches. You might capture all unanswerable questions in your production environment, pass them to the Suggestion Wizard, answer the questions it asks, and constantly improve your model over time.
English Query is an amazingly flexible technology that can bring great power and potential to your applications. With a small amount of work, you can get wonderful results. Often, when English Query doesn't understand a question, you merely need to add a missing relationship to remedy the problem. Both inhouse and vertical applications can benefit from this technology, which will grow in functionality and popularity as time progresses.
Let me offer some advice: Start using English Query now. Don't wait another week. I believe English Query might be one of those important technologies that could change the landscape. English Query is just turning the corner to big usability. Soon, lots of companies will be jumping on the English Query bandwagon—why don't you be the leader? Get on now, and when the rest show up, give them a helping hand! If you're already using English Query, please share your experiences. You can contact me at wsnyder@ikon.com. I look forward to hearing from you.