- Dynamically updating the data dictionary. For example, your application may allow the user to change the caption for some fields. You probably want the user to see the same captions in Stonefield Query; otherwise, the user might have trouble finding the field they want. In the DataEngine.GetCustomMetaData script, which is called just after Stonefield Query fills the DataEngine collections from the data dictionary, you can change the caption (or any other property) of any field. You can even add or remove fields.
- Defining data sources. If the Allow Multiple Data Sources configuration setting is True, the user can select which of the list of data sets defined to Stonefield Query they want to query on. Stonefield Query has no idea what data sets are available in your application or how to access them, so you can define a GetDataSources script for each database in the data dictionary that adds DataSource objects to the DataSources collection, specifying things like the data source type, location, name of the database, etc.
- Calling stored procedures. For a variety of reasons (security, performance, or because the DBA says so), you may want to access the data in a table via a stored procedure rather than by a SQL statement. Since Stonefield Query doesn't know the name of the stored procedure to call or the parameters to pass to it, you have to put code in the Select script for the table.
- Calculated fields. You may have a calculated field for which a simple expression won't suffice. For example, suppose the commission for a sale is set to a sliding scale, so higher sales amounts receive a higher commission percentage. In this case, you may have to use a control structure such as a CASE statement to determine the amount. The code for this goes into a script called from the Output Expression property of the field.
There are three different types of scripts in Stonefield Query:
- Event: These scripts are called from various places in Stonefield Query. For example, just before the Login dialog is displayed, the Application.BeforeLogin script is called. When the user chooses the About Stonefield Query function from the Help menu, the About dialog calls the About.Settings script to fill its list of settings.
- Data object: These scripts are specific to a database or table, and are preceded by the database or table name; for example, "Northwind.GetDataSources" is the name of the GetDataSources script for the Northwind database.
- User-defined: You can create a user-defined script that performs any task you wish. These are often called from the output expression of a field or from another script.
Stonefield Query supports three languages for scripting: Visual FoxPro, VBScript, and JavaScript. The latter two require that the Microsoft Script Control be installed, both on your system and on the user's system. This is the case with Windows 2000 or later and any Windows 9x version with Internet Explorer 4 or later installed.
The default language for a new script is the same used last time you created a script. When you choose a language for an event or data object script, the code is automatically filled in with a template or sample in the selected language. If you select a different language after editing the code, you are warned that the code will be removed and replaced with template code in the newly selected language.
Stonefield Query supports link actions. In the Link page of the Field Properties dialog, the user can select what action to take when they click the selected field in the Preview window. There are three built-in actions: Email, which launches their email program with an email address filled in; Web site, which navigates their browser to a specific URL; and Report, which runs the specified report. You can also define custom actions. For example, perhaps you want another application to display the record for the customer name the user clicks in the Preview window. In that case, you have to tell Stonefield Query how to perform that action. That's where link action scripts come in. If you turn on the Link action setting for a script, Stonefield Query automatically includes the description contained in the Description setting in an Action drop-down list in the Link page of the Field Properties dialog. So, to continue this example, create a script that calls another application and tells it to display the record for the current customer name. Turn on the Link action setting for this script and set its Description setting to something like "Display customer information." The user can then select the customer name field in a report, select Action as the link type, and choose "Display customer information" from the Action drop-down. When they run the report, clicking the customer name calls your script, which does the necessary tasks to display the selected customer's record.
Note that the code for a link action script must accept two parameters. The first is a field name and the second is the value of that field in the record the user clicked. Your code can do anything it wishes with these parameters, except if the first parameter is blank, this is a "discovery" call from the Field Properties dialog. In that case, the script must return a comma-delimited list of field name that should appear in the Action Parameter drop-down list in that dialog. Here's an example of such a script:
lparameters tcFieldName, tuValue if empty(tcFieldName) return 'Customers.CompanyName' else * do whatever is necessary endif
If you find it difficult editing the code in the edit box, right-click and choose Zoom from the shortcut menu. This displays a larger editing window, which you can resize and set the font to anything desired. Also, IntelliSense is available, which provides quick access to properties and methods of Stonefield Query objects and syntax for built-in functions.
You can test the syntax of your code by clicking the Test Syntax button. Note this doesn't test for logical errors or actually run the code -- it only tests that it compiles correctly. If a syntax error exists, information the compiler returned about the error is displayed (how ever useful that may be).
You can insert a field name or expression into the code by clicking the Expression button to invoke the Expression Builder.