Question Script (Settings/Views tab)

Question Script (Settings/Views tab)

The use of the Question and Answer scripts is probably the most complex topic that will be tackled by Access ELF users and administrators. With this version we hope to provide complete step-by-step documentation of the features of this powerful tool, to help you use it to the best advantage. Before we start, here's a definition of all properties and functions that can be used within scripts.

The are many different ways to use Question scripts (also called ViewScripts, in Access ELF documentation and script debugger displays). Let's first talk about one of the simplest but most important uses. This is setting up a Question script to read each question as it's presented to the system, and -- based on that question -- select an appropriate View for translating and answering that question.

First let's talk about the mechanics of a Question script. Question scripts are attached to a View by first defining and naming a script within the script library (accessible from the Scripts tab of Settings). The View should be given a single-word name (underscores permitted), which is entered into the Script Name column of the script record. The second step in defining a script is to include a function of the same name in the corresponding Script column. For this example, we'll call our Question script ViewPicker. To start with an empty, but legal Question script, we can enter ViewPicker into the Script Name column and this empty function stub into the Script column.

function ViewPicker

end function

Now that there's a legal script, we can activate it by typing ViewPicker into the Script Name textbox under the Question checkbox (to the right of Triggers Script) on the Views tab of Settings. Checking the Question box activates this script.

Although the script doesn't do anything yet, we can verify that it is being triggered by setting the Debug Scripts checkbox on the Preferences tab. Type in a simple question like "List the employees." and you'll see two message boxes in succession, one titled ViewPicker Script Entry Parameters, and one called ViewPicker Script Results. This utility helps verify and debug the code you'll be writing. For instance, if you change the last line of the ViewPicker script to read: stop function you'll see an error report instead of the normal parameter report ("stop" is not a legal way to close a VBScript function).

The ViewPicker script won't have much use unless there are several Views to choose from. Make sure that you have several Views available, by running the default View for Northwind (called Northwind), as well as at least one additional View. For this example, we'll assume that the extra View is called Catalog, and includes nothing but the Products and Suppliers tables.

Now, we promised that the ViewPicker routine would be used to choose between Views based on the question, but for Northwind it's a bit hard to find an example where simple yes/no tests on the question can help decide which View to use. This is not an argument against splitting your database into Views! For instance, you might also decide which View to use based on which form of your application is active, or who the logged-in user happens to be. In fact, let's use this last case to illustrate how Question scripts work.

Let's say that the default View of Northwind which includes all tables is to be used only by the Administrator, and that everyone else is entitled only to look at the Catalog View. Now we can make our ViewPicker fully-functional by adding code as follows:

function ViewPicker
   if UserID="Administrator" then
      ViewPicker="Northwind"
   else
      ViewPicker="Catalog"
   end if
end function

We may want to add an additional check that asks the logged-in user to prove his credentials:

function ViewPicker
   if UserID="Administrator" then
      if InputBox("Enter password:","Validator")="XYZ" then
         ViewPicker="Northwind"
      else
         ViewPicker="Catalog"
      end if
   else
      ViewPicker="Catalog"
   end if
end function

The problem with this script is that it keeps pestering the Administrator to input his password. Wouldn't it be nicer to ask the first time, and use any accepted passwords from then on?

function ViewPicker
   if UserID="Administrator" then
      if Password<>"XYZ" then
         Password=InputBox("Enter password:","Validator")
         if Password="XYZ" then
            ViewPicker="Northwind"
         else
            ViewPicker="Catalog"
         end if
      end if
   else
      ViewPicker="Catalog"
   end if
end function

This illustrates two points; one is that the Password variable is "static"; unlike all the other variables, it retains its value between questions. The other point is that, once we switch the non-Administrative user into the Catalog View, it doesn't matter if there's no script to continue checking his UserID and/or Password -- as long as there's no Question script which could flip him back to the full Northwind View!

Remember that any Question script associated with a newly-selected View will also fire, so make sure you don't get into an endless loop (by having scripts pass control back and forth between each other). Why not simply avoid this problem by preventing the second View from changing Views again? The reason is that Question scripts have many more uses than simply selecting the View to use, so it may be an integral part of the operation of the selected View. You'll see some of these uses later in the section on how Question and Answer scripts can work together.

Finally, let's look at one more refinement to this simple script. It's pretty wrongheaded to have the "XYZ" password displayed right there in a script panel for anyone to see. Let's use the new #include directive to import the Administrator's password from a file (OK, this isn't iron-clad either, but it's better than nothing.)

The text file will consist of a single line:
Const AdminPassword="XYZ"

#include file = "c:\secret\password.txt"

function ViewPicker
   if UserID="Administrator" then
      if Password<>AdminPassword then
         Password=InputBox("Enter password:","Validator")
         if Password=AdminPassword then
            ViewPicker="Northwind"
         else
            ViewPicker="Catalog"
         end if
      end if
   else
      ViewPicker="Catalog"
   end if
end function

Notice that the #include directive can be used to include text, not just from disk files, but from other script panels as well. This makes it easy to break up our scripts into modular sections, for instance by placing utility functions within a shared script library. To include the text of a script library panel within an active script, use this syntax:
#include name = "SomePanelName"

Since we're on the topic of security, you might want to know how to make this security feature even tighter. VB ELF, for instance, has an additional script type, which lets administrators associate a script with the Script tab button itself, allowing them to block access to scripts. We decided that it would really be simpler all around to let you handle the security in the way that suits you best. For instance, one quite easy solution is to modify the applications you distribute to your users, setting the Enabled property of the Scripts button on the Settings form to False. (See this note on redistributing modified libraries.)

Now let's look at a case where it really does pay to examine the user's question and choose a View based on what the question is likely to imply. For this example, we're going to move away from Northwinds for once, and look at another demo database available from the ELF Software web site. This database contains Oscar (Academy Award) information on the winners of the major categories from its origins to the present (congratulations Halle Berry!) Basically, the database is used to answer questions of two distinct types: questions about who was nominated for an award and questions about who took home the Oscar.

The trick here is that winners and nominees are not mututally exclusive -- each winner was, of course, also nominated. The way this is represented in the database is that a Status field shows either "Nominee" or "Winner". Now, we could try to have Access ELF do all the heavy lifting, and write macros to define nominee in terms of (Status="Nominee") or (Status="Winner"). But for something this basic to the way the interface is used, it pays to create queries representing each type of information. At the same time, we can include all the required JOINs explicitly. For instance, let's write two queries called FilmArtists and OscarWinners. They're defined this way:

SELECT DISTINCTROW Movies.MovieName, Artists.Artist AS ArtistName, AWARDS.AwardYear, AWARDS.Category, AWARDS.Status, Movies.Director FROM (Artists RIGHT JOIN AWARDS ON Artists.[Artist ID] = AWARDS.[Artist ID]) LEFT JOIN Movies ON AWARDS.[Movie ID] = Movies.[Movie ID];

The only difference between them is that OscarWinners has an extra condition (checking for the "Winner").

The reason that alternate Views turn out to be so useful in this case is that Access ELF relies very heavily on a database's predefined relationships when answering questions. In contrast, it often pays less attention to the verbs. It's relatively hard to express the concept that when verbs related to "nomination" are used, conditions like (Status="Nominee") or (Status="Winner") should be applied. [It is possible -- we're trying to illustrate the easiest way to do this job.]

But if we take a step back, it's clear that deciding whether the question refers to winners or nominees couldn't be easier. When people say "Who got the Oscar?" they mean winners. "Who was Best Actor in 1955?" means winner. In fact, it's always about winning -- unless someone mentions "nominate". That's really the only way to convey that you want to see the full list of nominees.

This yes/no quality fits in perfectly with our system of selecting Views. We can easily write a Question script to check for the presence of "nominate" or "nominee" (or, more simply, "nomin"). If present, we'll switch to the Nominees View; if not, we'll continue on with the Oscars View.

function ViewPicker
   if InStr(Question,"nomin") then
      ViewPicker="Nominees"
   else
      ViewPicker="Oscars"
   end if
end function

This means that the Oscars View can include only one query, the OscarWinners query. Similarly, the Nominees View can include only the FilmArtists query. Remember that we don't need to include the tables referenced by the queries to make sure that Access ELF understands all about the data. So we don't need to include any of the tables at all -- just a single query in each interface. Access ELF still understands that Ernest Borgnine is an entry for the ArtistName field, so we can ask either "What year was Borgnine nominated?" or "What year did Borgnine win?" Notice that the translations (one by the Nominees View and one by the Oscars View) will be nearly identical -- they'll just refer to the appropriate query. No need to go near those confusing Status conditions!

SELECT DISTINCTROW FilmArtists.AwardYear , FilmArtists.ArtistName , FilmArtists.MovieName FROM FilmArtists WHERE ( FilmArtists.ArtistName LIKE "*Borgnine*" ) ;

SELECT DISTINCTROW OscarWinners.AwardYear , OscarWinners.ArtistName , OscarWinners.MovieName FROM OscarWinners WHERE ( OscarWinners.ArtistName LIKE "*Borgnine*" ) ;

This leaves us with one small problem. Assume we start out in Oscars View. What happens if we ask these two questions in sequence:
Who was nominated for Best Actor in 1955?
Who won for Best Actor in 1955?

See the problem? The first question triggers the Question script associated with the Oscars View and switches us into Nominees View. So far so good. The question "Who was nominated for Best Actor in 1955?" is then answered correctly. Now we ask the second question. But there's no script defined here, so the question is passed through to the translator. This View can't distinguish between winners and nominees, it assumes everything asked refers to nominees -- that's the point of having a separate Nominees View! So what should we do? One solution would be to place the same ViewPicker script into each View. If the scripts are exactly the same, you're fairly safe, because it's unlikely that the same script will switch you into one View, then switch you back to the original View when run again from View #2. (Of course, Murphy's Law implies that it's possible.)

A safer alternative when dealing with complicated script hand-offs is to ensure that each question winds up back where it started, in some "home" view (in this case Oscars). To do this, we need to make sure that the last action performed by an Answer script is to reset the View to the home view.

function GoHome
result = OpenView("Oscars")
end function

This Answer script could be attached to the Nominees View so that after the question had been translated, a "reset" operation restores the original View. Notice that this is not part of a Question script -- not unless you want to set off an infinite chain of View exchanges! This method of resetting the View uses the built-in OpenView() function; notice that the OpenView function is performing the swap directly, it's not happening as a result of returning the View name as the GoHome function result. (Since GoHome is an Answer script -- not a Question script -- its function result is ignored.)

In general, the Answer script for a given View might serve many more purposes than to simply re-home a set of related interfaces. For instance, it could read internal variables set by its corresponding Question script, and use them to insert parameters into pre-defined SQL queries, or construct custom SQL statements on-the-fly. We'll see examples of each of these uses later. Note that it's good practice to invoke any re-homing statement after the Answer script does all other jobs for which it's designed.

Let's switch gears and talk about how a Question script can be used even in a single View situation. For this, we'll go back to our old standby, Northwind. Because Question scripts can fire before the question is translated into SQL, this is a great time to make any needed changes to the question, or even to replace the question entirely. For example, let's assume we've added the EmployeeID-to-ReportsTo mapping which lets Access ELF generate hierarchy displays for Northwind. (See the Verb Mapper or Analysis Script topics for more on this.)

Access ELF requires a pretty specific syntax to trigger hierarchical displays; for instance "Show the employees as a hierarchy." You might want to make it possible for your users to get this information without knowing this special phrase. For this, we can write a script which tests for certain word combinations in a question, combinations like "Northwind" paired with "hierarchy", "executive", "senior" or "junior." Let's assume that whenever these conditions are met, we'll guess that the user really wants to see the organizational structure of our company.

Function Contains(v)
   Contains = InStr(UCase(Question),UCase(v)) > 0
End Function

function SpecialCases
   if (contains("Northwind") and (contains("hierarchy") or contains("executive") or contains("senior") or contains("junior"))) then
      Question = "show employees, hire dates as a hierarchy"
   end if
end function

Notice that the utility function Contains is a good candidate for placing in a script library panel. For instance, we could name that library MyLibrary and reference it by adding #include name = "MyLibrary" to the SpecialCases panel, rather than inserting the Contains function itself.

Now a wide variety of questions, such as "Who are the senior people at Northwind?", and "Show Northwind's executive structure." will all be replaced by the question "show employees, hire dates as a hierarchy"

Since questions can be modified by Question scripts, you might wonder whether these scripts can be used in place of Spelling scripts to correct simple misspellings. The answer is yes; Question scripts fire before any registered Spelling script, so it's a way of handling the errors even before any spell-checkers are activated. For example, you could add the following to the beginning of SpecialCases

Question = Replace(Question,"heirarchy","hierarchy")

Or maybe this is another way of saying that Question scripts need their own spell-check code for any keywords that are crucial to the way the script behaves.

What if we wanted to augment our View to handle questions which Access ELF doesn't seem capable of understanding, even with careful phrasing of questions? For that we'll need to combine Question scripts and Answer scripts; so this topic is continued under the AnswerScript heading.

Last Updated: March, 2002