Use Phrases to redefine the words used in a query. For instance,
When I Type: active     I Really Mean: not Discontinued
This is a quick shorthand for the opposite of a Yes/No fieldname.
Another "shorthand" use might be to define
When I Type: catalog information
I Really Mean: Category Name, Description, Picture, Product Name, Product ID, Quantity Per Unit, Unit Price
If a term is never ambiguous (that is, it always means just one thing), you can use this method to define synonyms, for instance:
When I Type: salesperson     I Really Mean: employee
Otherwise, you should use the Add synonym button on the Lex Lookup screen, for words that can be used in many different contexts; for instance, to define "order" as a synonym of "purchase". (This might apply if you've already defined "purchase" as the relationship-verb connecting the Customer and Product tables in the Verb Mapper tab of Settings.)

You can also use the special term: <ignore> -- as in:
When I Type: the hell     I Really Mean: <ignore>  
Of course, this may come in more handy as we move to "voice interfaces" in future versions!

If you precede the word in the "When I Type:" column with an exclamation point, you can specify the message that should be used to indicate that this is an inappropriate or unmanageable query. For instance, if the database does not contain gender information on Employees, you can use the following entries:
When I Type: ! male     I Really Mean: No gender information available;Inappropriate query
When I Type: ! female     I Really Mean: No gender information available;Inappropriate query

The text following the semi-colon is optional and is used as the column header for the message. A default column header can also be defined in the Advanced tab of the Settings window.

It's also possible to have Access ELF respond with list in datasheet format, using the values specified in the phrase replacement string. For instance, we might change the above I Really Mean phrase to:

({Human resources;To contact this department:};{555-1234;please call this number}); ({Corporate headquarters};{555-1000});

Use string substitutions to match specific multi-word phrases, or to ensure that these substitutions are performed before any lexical matching. As an example, similar to the "not discontinued" macro above:
When I Type: [still in stock]     I Really Mean: not discontinued

This substitution is necessary because "in stock" is understood as a synonym for "UnitsInStock". If it were entered as a lexical phrase (without the brackets), it would never fire -- since "in stock" becomes "UnitsInStock" before the system "looks over its shoulder" to see whether the current and preceding word (now, still UnitsInStock) form a compound trigger. By bracketing the trigger, we ensure that it fires before any lexical replacements, so "Which products are still in stock?" yields "Which products are not discontinued?" instead of showing the products with non-zero UnitsInStock values.

Simple string replacements can repair errors of understading, like mistakenly using the BirthDate to answer "Show date we hired Davolio."
When I Type: [date we hired]     I Really Mean: hiredate of

This technique can also avoid mismatching overused field names against the wrong source. This might happen in the following case for instance, because customers have no "first" or "last" name entry, only a single [Contact Name] field.
When I Type: [customers first and last names]     I Really Mean: customers contactname

Note that the relative inflexibility of string matching means that this rule would not fire on correctly punctuated input like "Show the customers' first and last names." Here's a case where we might graduate to a more complex formulation, such as:
When I Type: [first and last names]     I Really Mean: [contactnames | surname ] * customer
When I Type: [surname]     I Really Mean: [ lastname | ContactName ] * employee

This changes "show customers first and last names" (or equally well "show customer's first and last names") in one step into "show customers ContactNames". It also changes "show suppliers first and last names", in two steps, into first "show suppliers surname" and finally "show suppliers ContactName". The bracketed alternative phrases combined with the * syntax indicates that the first choice is used if the string following * is matched anywhere in the question; if not, the second choice is applied.

By enclosing two Phrases (the "I Really Mean" part ) inside brackets, separated by the | symbol, we indicate that context-sensitive replacement is to be performed. The phrase-pair must be followed by an operator and a pattern to search for. Legal operators are * (anywhere in question), > (after the trigger) or < (before the trigger). An optional operator/pattern pair can follow the first (required) pair. This second pair indicates a pattern which, if found, will cancel the rule's application.
When I Type: [sales]     I Really Mean: [ ExtendedCharge | Subtotal ] > product > manager

This example says to use "ExtendedChange" as the replacement for "sales" if "product" appears in the question after "sales" -- unless "manager" is also found in the question after "sales".

When I Type: [sales]     I Really Mean: [ Subtotal | sales ] * order > manager
When I Type: [sales]     I Really Mean: [ ExtendedCharge | Subtotal ] > product > manager

In the same way, this means: replace "sales" with "Subtotal" if "order" is anywhere in the question, unless "manager" appears after "sales". If "order" isn't in the query, then replace "sales" with itself (ie, do nothing) and look for another rule to use. The other rule (used if "order" isn't found) says to replace "sales" by "ExtendedCharge" if "product" appears after "sales", unless "manager" appears after "sales".

The words following * < and > can also be lists of words, enclosed in parens and separated by commas. For instance, you could also write:
When I Type: [sales]     I Really Mean: [ ExtendedCharge | Subtotal ] > (product,item) > (manager,supervisor)

As if this isn't complicated enough, there are also a few exceptions. Compounds are automatically ignored, so you don't need to use the "> manager" clause in the above example to prevent "sales manager" from being replaced. And if either a second instance of the trigger word ("sales") or the replacement word appears between the trigger and the blocking word, then blocking is deactivated. Eg. Show the sales for each product manager

With the two definitions above, even though "manager" appears after "sales", the second rule will cause this query to be rewritten as Show the ExtendedCharge for each product manager because "product" appears between "sales" and "manager". Similarly, the query Show the sales for each sales manager will be rewritten as Show the ExtendedCharge for each sales manager because "sales" appears again between "sales" and "manager".

An extended form of context-sensitivity is also available. If the context word is the name of a field, you can enclose it in curly braces { } -- in this case, not the literal string itself, but any data value from the field it names will be the match-pattern deciding between the two alternatives.

Let's say you want the word "status" to mean "Reorder Level" when you're talking about Categories, but "Units In Stock" when you're discussing specific products.
When I Type: "status"
I Really Mean: [ Reorder Level | Units In Stock] * {CategoryName}

Now, Show the status of seafood will mean Show the Reorder Level of seafood -- since seafood is a data value of the field CategoryName. You can combine this with the list capability, to create a definition such as:
When I Type: "status"
I Really Mean: [ Reorder Level | Units In Stock] * ({CategoryName},Category)

This will change Show the status of each category into Show the Reorder Level of each category as well as changing Show the status of seafood into Show the Reorder Level of seafood.

When you type, Show the status of tofu you'll get Show the Units In Stock of tofu
But be careful . . .what happens when you type: Show the status of each product?

If you said Units In Stock, you're wrong! That's because "product" is also a data value of the CategoryName field, as in "Dairy Products". In this case you might want to define
When I Type: item     I Really Mean: product
so that you can ask: Show the status of each item and get Show the Units In Stock of each product

(Of course, you could just change "Dairy Products" to "Dairy" in Northwind itself, making the entry consistent with the other CategoryName entries.)

There may be times when you need to recognize values of a field as part of the trigger. For instance, in a database containing the Oscar Winners & Nominees, "Best Actor" might mean all nominees when we ask something non-specific, like "Show entries for Best Actor." But if we say, "Who was Best Actor?" we probably mean the Winner.
When I Type: [Who was {Category}]
I Really Mean: Who was OscarWinners_ . Artist for {1}
This definition will let ELF recognize "Who was Best Actor", "Who was Best Actress" etc. and substitute in "Who was OscarWinners_ . Artist for Best Actor", or "Who was OscarWinner_ . Artist for Best Actress"
(This assumes that there's a query called OscarWinners which selects out only the winners.) Notice that you must always add a trailing undercore to the names of tables and queries when you use them in phrase definitions.

As of version 4.0, this feature is limited to at most two data-aware triggers per definition. This means that {1} and {2} are the only markers that can be used. Example:
When I Type: [{FirstName} {LastName} sales]
I Really Mean: sum subtotal for {1} {2}

If you need to embed a numeric value in trigger pattern, you can get Access ELF to pass it along from the trigger to the substitution part of a phrase using the {#} marker. One example would be if you wanted to define "top" in various ways depending on what followed. So you could define "top salesmen" using some query representing their sales, and "top customers" with a query based on their purchases. But you'd mostly be using this in questions like "Show the top 10 customers".
When I Type: [top {#} customers]
I Really Mean: top {#} TotalPurchases_ . CustomerID sorting by value
Assuming you've written a query called TotalPurchases which adds up the number you're interested in (total expenditures, or total number of order dates, say), then this phrase definition will make it easy to get the answer using a short "key phrase", but will still let you vary the number -- top 10, top 20, or any number you pick.

Since "TOP N" queries need to have a sort order specified, you do have to add one; but notice that you don't need to give the exact name of the column you defined in the TotalPurchases query. Using the phrase "by value" will convince ELF to guess which value you're interested in (in this case, the one that's calculated). When defining phrases, though, it's often a good idea to be as specific as you can.

Data-aware patterns and numeric patterns can be combined in a single trigger, as in:
When I Type: [<= {#} {TimeToPrepare}]     I Really Mean: at most {#} {1}

This kind of macro is sometimes auto-generated during the analysis. It's useful when text fields are used to store both numbers and units-of-measure (a recipe database might have a TimeToPrepare column containing entries like "90 minutes").

Phrase scripts allow you to trigger a customized VBScript or JScript function whenever a specific string appears in a question. You can define as many of these scripts as you like, each one attached to as many string triggers as necessary.

Let's start with a simple example, before we see how to generalize the technique. In the Northwind database, let's ask the reasonable question: "What is the price of shrimp?" The answer that comes back is pretty poor:

SELECT DISTINCT Products.UnitPrice , Products.ProductName FROM Products ;

This SQL shows the price of every product in our inventory, none of which happen to be shrimp. The problem is that to the extent that Access ELF "understands" anything, it understands only those things mentioned in the database. If an item (like "shrimp") doesn't appear, the odds are slim that ELF will connect it to a related topic (like Seafood).

In general, the stategy of ignoring what it doesn't understand works pretty well for Access ELF. But there are cases where we want to recognize something, and react in a certain way, even to words that have no meaning in our database context. The Phrase script gives us this capability, because it can react to any arbitrary string.

The mechanics of Phrase scripts are as follows. Phrase scripts are written and named just like the other types of script, by adding one into the script library accessible from the Scripts tab of Settings. The name of this script is typed into the Script Name column, while a function with the same name must appear somewhere within the script itself. Once this script is registered in the script library, it can be made active by entering its name in the Script Name column of the Phrases window. Because Phrase scripts aren't checked-by-name at the time they're registered, you can also accomplish this in the opposite order. First enter a name on the Phrase window. then paste an identically-named script into the script panel.

We can add a script to handle the specific pattern "shrimp" in a question. Notice that we can't rely on a Spell script to intercept references to this unknown item, because "shrimp" appears in our large dictionary of general-use words. All we know about "shrimp", though, is that it's not a misspelling of something else, and that it's a noun.

With this Phrase definition, we can catch any question that contains the pattern "shrimp".
When I Type: [shrimp]     I Really Mean:       Script Name:     GotShrimp

We'll define the GotShrimp Phrase script as follows:
function GotShrimp
   Resource="shrimp"
end function

Finally, we'll add an Answer script which checks the Resource variable, and reacts when "shrimp" is detected.
function LastMinuteChanges
   if Resource="shrimp" then
      QueryResult=-8
      ErrorMessage="Sorry, we don't currently stock any shrimp products"
   end if
end function

This is just one way we could have coded around the problem. An alternate way of recording the "shrinp-request" condition would be to set the PrivateErrorCode variable to some constant (we might include some code with definitions like UNKNOWN_ITEM_SHRIMP = 1001). Then we'd check the value of PrivateErrorCode in our AnswerScript, and branch on our various unstocked items.

Another plausible way to respond would be to simply display a dataset showing the seafood items, using the WarningString variable to pass back an explanation:

function LastMinuteChanges
   if Resource="shrimp" then
      QueryResult=1
      SQL= "SELECT DISTINCTROW Products.* FROM Categories , Products , Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE ( Categories.CategoryName = 'Seafood' ) ; "
      WarningCode=1; ' any non-zero value
      WarningString="Sorry, we don't currently stock any shrimp products"
   end if
end function

Now, "shrimp" is not likely to be a completely unique case. Users unfamiliar with our business or product line might often ask questions about places or things that aren't in the database. A specialty food retailer (like Northwind) might get some questions about Icelandic customers (they do have customers in Sweden, Finland and Norway). It may be impossible to predict ahead of time which "unknowns" will appear in questions, but by keeping careful logs one should be able, over time, to collect good intelligence on what users ask about, and which of them (in the context of the database, at least) simply doesn't exist. If that's the case, we can substantially improve our database interface by creating a "table of unknowns" that can be used by our Phrase and Answer scripts to decide how to handle unrecognized input.

Consider this variation of the above code:

function GotUnknown
   Resource=PhraseTrigger
end function

To make this fly, all we'd need is a table (Unknowns) in which to look up the type of unknown we've just encountered. It might look something like this:

Unknown	UnkType
shrimp	Unstocked
Iceland	NoBiz

Functions are new in version 4.0, and solve some of the earlier problems related to applying Access Basic code to SQL column data. A function definition typically looks something like this:
When I Type: [age]
I Really Mean: Int(DateDiff('d',[Employees].[BirthDate],Now())/365.25)
Script Name: Function

Since the first releases of Access ELF, we've emphasized that although it's possible to use Phrase substitutions to apply Access Basic functions, it was generally better practice to embed calls to these functions into queries, and include those queries in the analysis. This tied the details of function-calling (parameters, types etc) into easily-tested packages (ie., the queries), and allowed our natural language interfaces to treat such calculated results just like any other field.

There were, and continue to be, many good reasons to embed function definitions into SQL queries. The classic example is the ever-popular "age question". Since the BirthDates of Northwind employees are available, nearly every test program includes a question such as "How old are the employees?"

Previously, we recommended that the user first create a query (named, for instance, Ages) which defined the meaning of Age for this application. Age can mean different things when applied to people say, or to video tapes. In this case, someone might try a query like:
Select Year(Now) - Year(BirthDate) as Age, EmployeeID from Employees;

This would be a good first try. It's not especially important how accurate it is, because it's easy to test and modify. Pretty soon we'd see that the difference between the years is not exactly the same thing as what we usually mean by "age" (it changes for everyone on New Year's Day). So we would work our way toward a more perfect formulation, such as:
Select Int(DateDiff('d',[Employees].[BirthDate],Now())/365.25) as Age, EmployeeID from Employees

The point is that this definition of "age" is independent of the natural language interface. Once the query is defined, we simply include it in the View, and it acts just like any other dataset. The calculations aren't even visible in the SQL translations, which look something like this:

SELECT DISTINCTROW Ages.Age , Employees.EmployeeID , Employees.LastName FROM Employees , Ages , Employees INNER JOIN Ages ON Employees.EmployeeID = Ages.EmployeeID ;

Armed with the Ages query and a few trivial Phrase macros (such as older than => with age greater than), we could handle highly complex questions, like "which employees older than 35 have customers in Norway?"

SELECT DISTINCTROW Employees.EmployeeID , Customers.CustomerID , Ages.Age , Orders.ShipName , Customers.CompanyName , Employees.LastName FROM Ages , Employees , Orders , Customers , Ages INNER JOIN Employees ON Ages.EmployeeID = Employees.EmployeeID , Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID , Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE ( ( Ages.Age > 35 and Customers.Country = "Norway" ) ) ;