Home   Index   list of chapters    previous chapter   next chapter  
 

Ch 12 - Script Files

top of page 
 
To 3.1.4 (The text file destination)
To 4.2.13 (Button fields - Run file)
To 4.2.13 (Button fields - Scripts menu)
To 8.4.3 (Importing data from plain text files)
To 9.2.2 (Merging the data)

Powerbase incorporates a script language which enables you to control many features from a script file. Using script files you can automate jobs which need to be done frequently, print jobs and mail-merges being the most obvious examples. They are even more useful for jobs which need doing regularly but infrequently — the kind where you forget exactly how you went about them last time! Script files are plain text files (created with Edit, StrongEd, Zap etc.) and are executed by dragging them onto the record window or by choosing them from a special script menu. All commands in a script file must be in upper-case, preceded by "!" and may be followed by parameters (which are not case-sensitive) separated from the command by a single space. A description of all the commands follows. Parameters in square brackets are optional; in actual use the brackets are omitted. Where commands simply control the status of option buttons (e.g. !CASE, !REVERSE, !UPPER) the parameter is either ON or OFF and omitting the parameter altogether has the same effect as OFF.

12.1 The !SCRIPT command

Since scripts are just text files there has to be a way for Powerbase to recognise them. All script files must therefore start with a line beginning with the command !SCRIPT. (or !SCRIPT POWERBASE if you want to be more specific but !SCRIPT by itself is sufficient). You may use !SCRIPT END as the last line of the file but this is optional since the script will terminate anyway when the end of the file is reached. You can make the command "chain" another script file by following it with the name of the new file, i.e. by using the form !SCRIPT <filename> (see 12.1.1). When a script file is dropped onto the record window, or started by means of a button on the record window, its name will be displayed with a request for confirmation that the file is to be executed. To suppress this request use !SCRIPT QUIET as the first line.top of page

12.1.1 Filenames in script commands
To !DELETE
To !SELECTION
To !QUERY
To !CSV
To !INCLUDE etc.
To !MAILMERGE
Many of the following commands can take a filename as a parameter and read information from that file. Selection, Print options and Query files, and plain text files containing lists of record keys can be used in this way. If such files are stored in the database's PrintRes directory you only need to use their leafnames when specifying them in script commands and Powerbase will be able to find them. If you make frequent use of just one or two scripts the best policy is to store the scripts themselves, together with any files they call, in PrintRes and link each script to a Run file button (see 4.2.13) on the record window. Dropping the script file onto the button will store the leafname of the file (if it is in PrintRes) or either the absolute or relative pathname (see 4.2.9) if it is elsewhere.

If you use scripts extensively there is a more convenient way to store and access them. Every database contains a directory called Scripts which is opened by choosing Scripts from the Report menu. This is the best place for the scripts themselves and also for any PrintOpts, Selection or Query files on which they depend. Provided the Scripts directory isn't empty you will find on opening the Report menu that Scripts has its own submenu, headed Run scripts, listing the scripts (but not their associated files). The scripts can be run by choosing their names from the menu and the files they use can be identified from their leafnames alone, in fact this is the first place Powerbase will look for them. You can't, of course, store a file here if it has the same name as the script itself, but you can create subdirectories (to one level only) and put associated files with the same name in them. You might, for example, find it useful to have subdirectories called Selections, Queries, and Options. If you have a script called, say, Anniversaries which makes use of a saved print selection, print options file and query file you could call all these files Anniversaries and store each in the relevant subdirectory. This is a tidy scheme which keeps clear the relationship between scripts and other files. Powerbase will be able to find the files from their leafnames alone provided the relevant subdirectories are only one level down from the Scripts directory itself.

Files stored anywhere except in either PrintRes or the Scripts directory must be referenced by their full pathnames.

There's a very easy way to store a new script in the Scripts directory, without needing to open the directory at all, using the !SAVESCRIPT command.

You can store scripts in the Scripts directory without their appearing on the associated menu. If there are scripts which are never called directly but only chained by other scripts (see 12.1) you might not want them cluttering up the menu. The trick here is to end the name of the script file with a tilde (~). Such scripts will work just like any others but won't appear on the menu.

When you click SELECT on a Run file button to which a text file is attached, Powerbase examines the first line of the file. If this begins with !SCRIPT the file is executed as a script. If not the file is loaded for editing. To edit a script file linked to such a button you should click with Shift-SELECT. Also note that clicking the Run file button with ADJUST will display the stored filename. If only the leafname is displayed it means the file is in PrintRes; if it's anywhere else either the full or relative pathname is shown.

12.1.2 Auto-running a script file
Another useful way of executing a script file is to give it the filename !Script (or one beginning with !Script) and place it in the database's PrintRes directory. Such a file will then be run automatically when the database is opened. Only one script file may be run in this way. (See also 12.7.)top of page
 

12.2 Commands used for reporting

To 12.6 (Repeating operations using a loop)

"Reporting" is used here in its broadest sense to include any operation capable of producing a file containing record data. Thus, creating CSV files and exporting single records are included.

!SELECTION (or simply !SELECT)
Used to select fields for inclusion in a report. If a filename is given as a parameter Powerbase looks for a Field Selection file of that name and loads it, just as if you had double-clicked on it or dragged it to the record window. (See 12.1.1 for the interpretation of filenames.) Alternatively, you may supply a comma-separated list of the tags or descriptors of the required fields. Prefer tags rather than descriptors. All data fields must have a tag and tags are unique; this is not necessarily true of descriptors. You may include the record number, key and subfile number by placing the words RECORD, KEY and SUBFILE, respectively, in the list. If you supply no parameter at all the current selection will be cleared.

If the selected report format is Field-per-line you may use field-concatenation to group short fields on the same line. This can be achieved in a comma-separated list of fields by separating the tags of fields to be concatenated with + instead of a comma. Thus, in the !Elements database, the command !SELECT NAME+SYM,ORIG,Z+M+GP will (with GP=1 as the search formula) produce output identical to that in the right-hand illustration in 3.2.2 where the manual method of selecting field-concatenation is also described.

When using a comma-separated list you may employ an additional feature by enclosing a tag in brackets. When the field appears in a report it will be enclosed between the brackets used to surround the tag. All the following bracket types may be used: < >, ( ), [ ], and { }. You may also use « », » «, and single or double quotation marks in the same way. This effect can be achieved only with a !SELECT(ION) command in a script and is of particular use if you are creating an email distribution list. Each item in such a list must be in a form acceptable to mail-readers. The list may consist of the email addresses alone but these are often somewhat cryptic and you would probably want to add the real name of the intended recipient. There are two ways of doing this:

Such a list, human-readable yet acceptable to mail-readers, may be created with a Powerbase script by printing to a text file, turning off headers and footers, and making use of the bracketing feature described above. You could even force the file to be created in the appropriate directory of MessengerPro or Pluto for direct use in sending emails to multiple recipients.

Besides enclosing printed fields in brackets using !SELECTION as above you may also enclose them between certain other pairs of characters such as single and double quotes (both plain and smart quotes) and the « » character pair which may be used either way round.

!SUBFILES <n1>[,<n2>,<n3>...]
Selects the subfile(s) which will be searched in order to create a report.top of page

!CASE ON/OFF
Turns the Case button on the Query panel ON and OFF.

!REVERSE ON/OFF
Turns the Reverse button on the Query panel ON and OFF.

!ONCEONLY ON/OFF
Turns the Ignore repetitions of key button on the Match window ON and OFF.

!QUERY [filename\]<search formula>
Used to create a report. It returns the same result as entering a search formula in the Match window and clicking Report. If you first issue a !DESTINATION FILE command you can use a list of !QUERY commands to create a batch of reports as text files. If you omit the optional filename each file will be created in PrintJobs using the search formula (or part of it) as the filename. If you supply a name containing $ it is assumed to be a full pathname and will be used to create the file. A leafname by itself causes the file to be created in PrintJobs. If you are using a menu button to run scripts (see 12.1.1) you may provide a partial pathname e.g. SCRscript.Report, where SCR is the tag of the menu button. The file will then be saved in the SCRscript directory under the name Report. Note the backslash between the filename and search formula; this tells Powerbase where the one ends and the other begins. !CASE and !REVERSE also affect the behaviour of this command if issued beforehand.

If you issue a !QUERY command without a search formula the report will contain the currently displayed record only, i.e. it is equivalent to holding down Shift while clicking Report on the Match window or typing the Print key.

An extended version of this command enables reporting within a set of records previously marked with an !INCLUDE command (see also 3.8.1). All you have to do is replace !QUERY with !QUERYM. The search formula will then act as though the marked records were the only ones in the subfile, the rest being ignored.

Instead of the search formula you may supply the name of a saved Query file. If this file is in PrintRes or in a subdirectory linked to a script menu button only the leafname need be given as noted in 12.1.1.

An error message is produced if an attempt is made to execute !QUERY with the format set to Table or Label when Destination has not been set to Printer.

!PRINT
Prints the displayed record as a single-record report, just as if you typed Shift-Print or clicked the Report button on the Match window with Shift-SELECT. It would normally be used with a prior !DESTINATION command.top of page

!SAVE [<filename> or R]
This may be used to save a file which has been created in a window by a !QUERY command. When issued without a filename it uses whatever name the !QUERY command would use if the destination were File instead of Window, i.e. a name given in the !QUERY command or one based on the search formula, but any supplied name overrides this. !SAVE is also used to save single-record reports created with !PRINT. If no filename is given the file will be saved in PrintJobs under a name derived from the primary key or, if R is supplied as a parameter, as RecXXX, where XXX is the record number.

!DUMP [<filename> or R]
This has an effect similar to that of Ctrl-dragging with SELECT on the record window. It saves all exportable fields of the record as a text-file in PrintJobs. Filenames are as for !SAVE, i.e derived from primary key if no parameter is given and RecXXX if parameter is R.

!CSV [filename\]<search formula>
Similar to !QUERY but produces a CSV file instead. Once again, the name of a Query file (see 12.1.1) may be supplied instead of a search formula. The CSV option settings are observed. A prior !DESTINATION command is unnecessary in this case. As in the case of !QUERY you may report within a marked set by using !CSVM instead of !CSV.

!INCLUDE, !EXCLUDE, !CLEAR
These all have the same syntax and may be treated together. They are followed by a comma-separated list of either keys or record numbers and have the same effects as the controls on the Mark pane attached to the record window. e.g. !INCLUDE <key1>[,<key2>,<key3>...] has exactly the same effect as ticking the check-box on the Mark pane when each of the relevant records is displayed in turn. If record numbers are used rather than keys each must be preceded by a hash (#). If a key in the list does not exist in the currently-selected index it will simply be ignored, i.e. it doesn't cause an error. It follows that you should issue a !KEY command (see below) to ensure that the correct index is selected before using any of the above.

The !PREPROCESS command (see !PREPROCESS) may be used in conjunction with the above commands to create keys by processing more user-friendly phrases.

!EXCLUDE has the same effect as marking with a cross, which is what happens when the alternative option from the pane's pop-up menu is chosen. !CLEAR selectively clears marks or, issued with no parameters, clears all marks.

Instead of a comma-separated list of keys or record numbers you may supply a search formula or a filename (See 12.1.1). If a filename is given the file should be a plain text file with one key or record number on each line. It may be appropriate to make Powerbase itself create such a file, e.g. where the records involved do have a queriable connection and the list is to be used by more than one script.top of page

!KEY <index name or number>
Selects an index and makes it the currently active one. Names and numbers of indexes may be viewed on the Indexes=>Show Files submenu. If no parameter is given the primary key index is selected.

!FILE <filename>
Opens a text file for output. If only the leafname is given the file will be created in PrintJobs; otherwise the full pathname must be specified. The file may be closed by issuing a !FILE command with no parameter. That will also open the directory containing the file. If you omit the closing !FILE command the file will be closed at the end of script execution anyway but the directory will not be opened. This command is meant to be used in conjunction with !LINE (see below) and !QUERY.

!LINE
Writes a line of data to an open text file in response to a !QUERY command. At its simplest !LINE is followed by a comma-separated list of field tags which, during a !QUERY search, will be replaced by the actual field contents of the matching records and written to the text file. Used in that way all field values will be strung together with no space between them, which is almost certainly not what you want! You may use anything you like to separate the data items from one another provided you enclose it in double quotes. Such literal strings form part of the comma-separated list, just like the tags. It's sometimes also useful to include a blank line in the file and this can be done by a !LINE command with nothing following it.

There are two variants of the !LINE command. !LINED (D for "Direct") will write its parameter string directly to the open file independently of any !QUERY command. It is intended as a once-only means of writing, for example, a title as the first line of the report. Without a parameter it inserts a blank line. !LINEC (C for "Count") is also a direct command whose only function is to insert at the end of the report the number of records printed. You may supply an explanatory parameter string; without it just the bare number is printed.

Example
!SCRIPT QUIET
!FILE MailingList
!LINED "MAILING LIST"
!LINED
!LINE "Name: ",NAME
!LINE "Address: ",ADD1,", ",ADD2,", ",ADD3
!LINE
!QUERY NAME>Ford
!LINEC "Total: "
!FILE
!SCRIPT END
The example opposite shows a script using !FILE and !LINE with the !Friends database. The script opens a file called MailingList in the database's PrintJobs directory and the following !LINED writes the string MAILING LIST as its first line. !QUERY selects all the records with surnames later in the alphabet than Ford, writing data to the file for each record in accordance with the !LINE commands (which must come before the !QUERY). When the !QUERY has finished executing, !LINEC inserts the number of records printed. !FILE then closes the text file and the PrintJobs directory is opened to reveal it. Note how the different address fields are separated by a comma and a space; also the use of an empty !LINE command to put a blank line after each record.The report looks like this:

MAILING LIST

Name: Jones Mary
Address: 31 Bridge Street, Fernylands, Little Farnham

Name: Soap Joe
Address: 17A Coppice Close, Greenfields, Anytown

Name: Sunningdale Samantha
Address: The Old Manor House, Grindlethorpe, Exeter

Name: Turnip Charles
Address: "The Cedars", High Woodhead, Springville

Total: 4

Creating a report by this method gives you a little more freedom than the standard Column and Line per field formats. You may label the lines in any way you like and have free choice of what order the fields appear in, which fields go on each line, and how you separate fields on the same line. Note that you are (at present) limited to a maximum of ten !LINE commands in a script, not counting !LINEDs and !LINEC. top of page

12.3 Setting report options

Report options may be specified in a Print options file as described below or set individually by means of the separate commands which follow. Further information about many of the following commands can be found at 3.10.2

!PRINTOPTS <filename>
Loads a Print options file, setting the options just as if the file had been double-clicked or dragged to the record window. Remember that a Print options file contains all the settings for both the Report options and Printer setup windows. Without a filename it sets the default options.

!DESTINATION <string> (where <string> is Window, File or Printer)
Sets the destination for report printing (see 3.1). If File is used the report is saved in PrintJobs unless a subsequent !QUERY command supplies an alternative pathname (see 12.2). You may shorten the parameter to its initial letter only (W, F or P) and anything else (or no parameter at all) defaults to Window.

!FORMAT <string> (where <string> is Columns, Field-per-line, Label or Table)
Sets the print format. As with the !DESTINATION command you may use the initial letters C, F, L or T and if any other parameter is used, or !FORMAT is issued by itself, the Columns format is used.

!RULES ON/OFF
Selects/deselects the display of inter-line dotted rules in Columns format

!EXPAND ON/OFF
Selects/deselects the button which causes fields to be expanded by reference to a linked validation table.

!DATE ON/OFF
Turns date and time stamping ON and OFF.

!UPPER ON/OFF
Forces upper-case printing ON and OFF, the latter producing normal upper/lower case printing.

!HEADER, !FOOTER ON/OFF
These determine whether header/footer information is printed on reports.

!FIRSTPAGE ON/OFF
When followed by ON this causes the header to appear on the first page only. (Footer information only appears on the final page anyway.) N.B. This command was formerly called !FIRST, a name now used for a totally different command.

!SHRINK ON/OFF
Determines whether surplus "white space" is removed from between the columns of reports in Columns format. ON removes all unnecessary space, OFF leaves columns at their maximum allowed width.

!HEADINGS D/T
Determines whether descriptors (D) or tags (T) are to be used as field identifiers in a report. Any other parameter, or none at all, causes headings to be omitted.

!TITLE <string>
Uses <string> as a title on subsequently printed reports.

!PAGE <n>
Sets the page length for reports to <n> lines.

!SPACER <string>
Specifies the string to be used to separate columns of printed data in reports using Columns or Table format (see 3.10.2).

!TEXTWIDTH <n>
Specifies the maximum width in characters for Text and Text block fields in reports.

!SORT <n>/<tag>
Specifies a column on which the report is to be sorted before reporting. The column may be specified either by column number or the tag of the relevant field. The !SORT command must appear before the !QUERY command in the script file.top of page

12.4 Setting printer setup options

!PDRIVER
Tests whether a printer driver is loaded. If not it aborts the script with a suitable error message.

!ORIENTATION <orientation>
Changes the paper orientation. The default is Portrait or Upright, the alternative is Landscape or Sideways. Only the initial letter is necessary and case is unimportant.

!PRINTCOLUMNS <n>
Sets the number of columns used in printing a report when the Destination is Printer. <n> may be 1, 2, 3 or 4. Anything else (including 0) is interpreted as 1.

!HEADERFONT, !BODYFONT <fontname>
Set the fonts for headers and footers and for body text in hard-copy printing. Names should be of the form Homerton.Medium, Trinity.Bold.Italic etc. The font must, of course, be known to the font-manager.

!FONTSIZE <n>[+<ex>]
Sets the size of the fonts used to n points. The optional parameter ex allows you to specify an addition to this size to make headers and footers print at a bigger size than the report body. e.g. !FONTSIZE 10+2 would produce 12-point header/footer on a 10-point report.

!LMARGIN, !RMARGIN, !TMARGIN !BMARGIN <n>
Set the left, right, top and bottom print margins respectively. The units default to mm unless specified by appending mm, in or pt to the number.

!PMARGINS
Cancels the margin settings in the Printer Setup window and uses the printer driver's default margins instead.

!LINESPACE <n>
Sets the spacing between the baseline of one line of printed text and the next as a percentage of the font size. The default is 120%. For Table format 150% looks neater.

!TABLE <columns>,<width>,<rows>
Sets up the number and width of blank columns in Table format, and the number of blank rows at the end of the report. Column width is in mm but may be changed as for margins (see above). You need not specify all the parameters; current values will be used if any are omitted. You may, for example, specify 6 blank rows and nothing else by using !TABLE ,,6. Note the commas.

!LABEL <n>,<w>,<h>,<str1>,<str2>,<s>,<su>,<bl>,key/nokey,num/nonum,<ind>
This complicated command determines the setup for label-printing where:

  1. <n> is the number of labels across the page
  2. <w> and <h> are the width and height of label
  3. <str1> and <str2> are the optional fixed first and last lines
  4. <s> is the number of the label at which printing on first page is to start.
  5. <su> and <bl> are field tags. If field bl is blank then field su will be printed instead.
  6. key/nokey and num/nonum specify whether the primary key and label number are to be printed on the label.
  7. <ind> specifies the amount by which each line on the label is to be indented with respect to the previous line.
For 1 and 7 units may be specified as for margins and column widths (see above). As with !TABLE you need only specify the values you wish to change, but the correct number of commas must precede the parameters you are changing.

!LABCOPIES <n>
Specifies the number of copies of each label to be printed.

!COPIES <n>
Specifies the number of copies of an entire report to be printed.top of page

!PAUSE ON/OFF
If ON pauses and asks for confirmation before printing each page (including the first).

12.5 Commands used mainly for editing

!STARTAT <tag>
Sets the field at which editing begins and places the caret in that field. If no parameter is given, or if the designated field doesn't exist or isn't writable, editing starts at the first writable field.

!FIRST, !LAST, !NEXT, !PREVIOUS
These commands may be used to move about the current subfile and display records. The meanings of the commands are pretty obvious.

!DELETE <key1>[,<key2>,<key3>...]
Deletes record(s) having the supplied primary key(s). As an alternative to the key or key-list you may supply the name of a text-file containing the required keys, one to a line. The filename will be interpreted as described in 12.1.1. The command can delete records in any or all of the active subfiles, not just the current subfile

!INSERT
The counterpart of !DELETE. In this case the command must occupy a line by itself and be followed by a list of the fields to be inserted and the data to go in them. This information is in the form <tag>:<data>, a separate line being used for each field. Note the colon separating the tag (which identifies the field) from its required contents (the data). Insertion is terminated by a blank line. This command will only insert data into simple, editable fields; it can not be used to populate Scrollable lists or to set Check boxes. Records will be inserted into the currently-selected subfile.

!MOVE [<sub>;]
This is similar to !DELETE. Followed by either a comma-separated list of keys or the name of a file containing a list of keys it moves records from their present subfile to the next in sequence, regardless of the currently subfile; e.g. a record in subfile 2 will be moved to subfile 3. It can also be made to move records to any subfile by appending to the command the required subfile number followed by a semicolon. Records will be found regardless of which subfile they are in and moved to the specified subfile. There must be no space between the semicolon and the following key list or filename.

!CHANGE <field tag>,<old contents>,<new contents>[,<search formula>]
Allows global changes to be made. The command functions like the Global changes menu choice (see 2.5.5). <field tag> specifies which field to change. <old contents> and <new contents> are the strings which would be placed in the Replace and with icons.

!FILTER <search formula>
Has the same effect as entering a search formula in the Filter window. The Filter window isn't opened but the Filter button on the tool-pane (or on the record screen if there's a Filter button there) is turned on and the first matching record is displayed. !FILTER on its own cancels the filter. !FILTERM resembles !QUERYM and !CSVM in that it causes the filter to operate only within a set of records marked for inclusion.

!FILTEROPEN <search formula>
Identical to the above except that the filter window is opened.

!FIND <key>
Locates and displays a record using the key in the currently-selected index.

!INDEX <tag>
Constructs an index on the field whose tag is specified and selects it as the current index. If you don't want the new index to become selected place a tilde (~) after the tag. Used in this, the simplest, way the number of characters, word-number and position (see 7.1) default to maximum field-length, 1 and L respectively. You may specify these extra details by placing them after the tag and separating them with colons, i.e. <tag>:<chars>:<word>:<pos> specifies all four parameters. A key based on more than one field (or using the same field more than once) may also be defined, each individual field-specification being separated from the next by a semicolon. e.g. the key based on surname and forename described in section 4.5.2 would be defined by !INDEX NAME:4:1:L;NAME:1:2:L
C Keys in index are case specific
P Case of letters to be preserved when keys are displayed
J Numbers are to be justified using leading zeroes
S Key segements are to be padded to their full length with spaces
A Accented letters in keys to be replaced by unaccented ones
O Null keys are to be omitted
A further colon may be followed by a string consisting of some or all of the letters CPJSAO the interpretation of which is as shown in the adjoining table. In other words, these letters set the option buttons in the key definition window. Letters omitted from the string cause the relevant options to be deselected. Suppose you're indexing a short, one-word field which requires only the tag to specify the key structure, but want to set or clear some of the six option buttons? To enable Powerbase to identify the options string you need to place four colons after the tag, followed by the options string e.g. !INDEX ORIG::::CAP. What you're effectively doing here is specifying null entries for the characters, word and position parts of the key so that the whole of field ORIG will be used, setting three of the options and clearing the other three. In the unlikely event of your wanting to define such a key and turn off all the six option switches just follow the tag with four colons and nothing else, i.e. enter a null string.

A single !INDEX command may create several indexes, each in turn becoming the currently-active index. Commas are used to separate individual index specifications. Using the Elements database, for example, (and specifying the indexes in the simplest possible way) the command !INDEX Z,GP would index first on the Atomic number (Z), then on the Group field (GP) with the Z index active. The database is left with GP as the active index and a report of the whole database shows the records ordered by Group but, within each group, the records are in order of Atomic number; a sort within a sort.

Powerbase "remembers" the names of the indexes created by the most recent !INDEX command and will delete them if you issue !INDEX with no parameters — very useful for temporary indexes.top of page

12.6 Repeating operations using a loop

!LOOP <loop-counter or terminating condition>
Initiates a loop. If a positive number is entered as the parameter it is assumed to be the initial value of the loop-counter. The value is decremented every time !ENDLOOP is reached.

!ENDLOOP
Marks the end of a loop. The terminating condition or loop-counter is tested whenever this command is reached and the loop terminates if either the condition is TRUE or the counter is zero. A loop may be terminated in five different ways:

  1. When the loop-counter becomes zero.
  2. By supplying MOUSE S, MOUSE M, or MOUSE A as the parameter to !LOOP. In the first case the loop will terminate when you click SELECT, in the second when you click MENU, in the third when you click ADJUST.
  3. You may supply any single word of your choice as a parameter and include an input-requesting command (see 12.8) in the loop body. Typing the specified word into the input icon will terminate the loop. The word must consist of letters only — spaces, numerals and punctuation are not allowed — but it isn't case-sensitive.
  4. By supplying a search formula as the parameter. This is only useful if records are being scanned within the loop, e.g. by a !NEXT command. The first record to match the search formula will terminate the loop. However, since the condition isn't tested until the end of the loop processing will have continued one record beyond the first match. Following the !ENDLOOP with a !PREVIOUS command will ensure that the record being sought is actually on the screen.
  5. When a preceding !DATA command runs out of data. See !DATA (below)
  6. When the Escape key is typed
If no parameter is given the terminating condition is set to TRUE so that the loop terminates after one iteration (except when the !DATA command is in use). This reduces the likelihood of the program being trapped in an endless loop, although this is still possible.

Ex.1
!LOOP UNTIL stop
  !FIND Key to look for?
  !PRINT
  !SAVE R
!ENDLOOP
You may use the wording: !LOOP UNTIL <terminating condition> to make the script more understandable but the word UNTIL is purely optional and Powerbase will ignore it. By using the input-requesting facility (see 12.8) inside a loop you can now include command sequences such as the one illustrated in Ex 1. The script will keep requesting keys, looking up the records, creating single-record reports and saving them as RecXXX, RecYYY etc. until the user types 'stop'.

!WAIT
Changes the pointer to a representation of the mouse with its SELECT button highlighted. Execution will continue only when a mouse button is clicked or a key pressed on the keyboard. This may be used on its own to hold up script execution (e.g. while a message is displayed using !MESSAGE). Clicking any mouse button, not just SELECT, will allow execution to continue. If you want to force the user to click a specific mouse button to continue you should place the !WAIT inside a loop whose terminating condition is the clicking of the required button.

!DATA <param1> [,<param2>,...]
This is especially useful inside a loop. If, in a subsequent command, READ is supplied as a parameter it will be replaced by the first parameter in the comma-separated list following !DATA. The next time a command has READ as a parameter it will be replaced by the next item in the !DATA list and so on until the list is exhausted. This is sufficient to terminate the loop; there is no need to supply an explicit terminating condition. The script shown in Ex.2, based on the Elements database, will clarify the situation.

Ex.2
!DESTINATION FILE
!SELECTION NAME,Z,M,GP
!DATA GP=1,GP=2,GP=3
!LOOP
  !QUERY READ
!ENDLOOP
The first time around the loop the !QUERY READ command will be changed to !QUERY GP=1. Since the output Destination is File the resulting report will be created in PrintJobs under the default name GP=1. The second time round the command becomes !QUERY GP=2 and the third time it becomes !QUERY GP=3. The data list is then exhausted and the loop terminates. A search formula may contain commas in which case the formula must be in quotes as in Ex 3. Note that the !DATA line must precede any command which READs from it. Ex 3 also shows how you can provide your own names for the reports included in the !DATA list, alternating with the search formulae.

Ex.3
!DESTINATION FILE
!SELECTION NAME,Z,M,GP
!DATA MainGps,"GP=0,1,2,3,4,5,6,7",Transition,GP=T
!LOOP
  !QUERY READ\READ
!ENDLOOP
On the first cycle of the loop the READ before the backslash is replaced by MainGps and the READ after the backslash by "GP=0,1,2,3,4,5,6,7". See syntax of !QUERY (12.2) regarding inclusion of a filename with the command. Transition and GP=T replace the READS the next time.

Be careful not to make lines too long when using !DATA. The maximum allowed length of the whole line is 254 characters. You may, however, use a block of up to ten consecutive !DATA lines and read them all in one loop. If you need more than this you must place them in a separate block after the !ENDLOOP and then initiate another loop to read them.top of page

12.7 Other commands

!IMPRESSION
This is used in conjunction with !QUERY to generate a report in text-file format. It enables you to insert Impression DDF commands (Note 1) at the start of the text-file. A script may, for example, execute a series of !QUERY commands and the resulting files are to be all selected together and dragged into an Impression document. To make each file go into a new frame you need to make each begin with {nextframe}. This can be achieved by placing the script command !IMPRESSION {nextframe} before the first !QUERY. You do not, however, want the Impression document to have a blank frame on its first page. To suppress the effect on importing the first file use !IMPRESSION {nextframe} Not first. There must be a space between {nextframe} and Not first, but the latter isn't case sensitive.

!OBEY <string>
This can be made to do anything which a *command can do. e.g. !OBEY Delete <filename> will delete the named file. !OBEY <filename> will attempt to *Run the file. By supplying the pathname of a Powerbase database you can close the existing database and open a new one. (But see below for a command specific to this operation.)

!OPEN <database>
Opens the database specified in <database>. A script file containing the command can be dropped on the Powerbase icon on the iconbar to make it open a database then continue executing the remainder of the script. It also works if you already have another database open and drop the script on the record window.

Where does Powerbase look for the database? The following four possibilities are tested in order:

  1. The database is referenced by a full pathname.
  2. The directory containing the script is a subdirectory of the one containing the database. This meets the situation where you maintain a subdirectory to hold all the scripts which function with a particular database.
  3. The script and the database are in the same directory.
  4. The database is in the same directory as !Powerbase itself.
If all these tests fail Powerbase complains that it can't find the database.

You can also place an auto-run script file (i.e. one whose name is, or begins with, !Scriptsee 12.1.2) inside the !Powerbase application directory itself. If the first line after the initial !SCRIPT is a !OPEN command Powerbase will open the designated database and continue execution of the script.

!MAILMERGE <filename>[,M,D,P]
M Causes the first matching record in the subfile to be merged as if the Merge button had been clicked.
D Used with M causes the displayed record instead of the first to be merged.
P Used with M causes all matching records to the end of the subfile to be merged and the pages printed.
Prepares for a mail-merge with Impression or Ovation Pro by loading the document specified in <filename> (See 12.1.1) and opening the Merge window. Without the optional M parameter this is as far as it goes. The adjoining table explains what the optional parameters do. A !FILTER command, issued before the !MAILMERGE, will restrict the merge to records which match the search formula.

!COMMENT <string>
May be used to insert comments in a script file. The rest of the line is ignored.

!MESSAGE <string>[,t,x,y]
Will display a user-defined message in a small window to keep the user informed of what's happening during execution of a script. The optional parameters function as follows:

If the command is issued with only the message string the window appears in the middle of the screen and remains on view until execution of the script ends or another !MESSAGE command is issued with no parameters.

!DISPLAY <tag>,<num>
Enables auto-display of External fields and initiates auto-scan of the subfile as if the Play button had been pressed (see 4.2.10). The second parameter determines the interval between records: the bigger the number the longer the delay. It is not, however, an actual time. A value of 10 will flip through the records quite rapidly; 100 is much slower. If the numeric parameter is omitted the auto-scan is stopped and the auto-display feature is disabled.

!SLIDESHOW, !SLIDESHOWS and !SLIDESHOWR
This command has the same syntax as INCLUDE etc., i.e. it takes as a parameter a comma-separated list of keys or record numbers, a filename, or a search formula. When executed the pointer turns red and the first matching record is displayed — the one corresponding to the first key (or record number) in the list, or the first which matches the search formula. If the command is issued with no parameter it works through the entire current subfile. Clicking SELECT displays the next matching record, clicking ADJUST goes back to the previous one. Typing the Escape key terminates the command and the pointer reverts to its normal colours. Attempting to move beyond the beginning or end of the defined record sequence also terminates the command but some users might not care for falling off the ends of the list like this so alternatives are implemented:

If either of these options is used you will need to type the Escape key to terminate the command.

!POINTER ON/OFF
It is appreciated that, during a slide show, you might want to turn the pointer off altogether. The !POINTER command is provided for that purpose.

!PREPROCESS ON/OFF
Complex keys may be entered in the Search window in the more user-friendly form of two or more words separated by a space. e.g. in the !Music database a search for the complex key MOZARHORCON003 may be effected by typing Mozart Horn Concerto 3 (see 2.3.1.1). The phrase entered by the user is pre-processed to convert it to the actual key before carrying out the search.

!PREPROCESS ON allows any script command which acts on a list of keys to take advantage of this feature. The command must, of course, precede the one which uses the keys.

!CLICK <mouse button>,<window>,<button icon>
This command allows you to simulate mouse-clicks on button icons on some windows. The three parameters are as follows:

top of page

!SAVESCRIPT <filename>
When you write a new script you will probably save it in a temporary place in order to test (and debug!) it, dropping it on the record window for each run. When it's ready to save in the database's Scripts directory you can add the command !SAVESCRIPT at the end of the file immediately before the !SCRIPT END if you're using that. The next time you drop the file on the record window it will be copied into the Scripts directory. Add the required filename (leaf-name only) as a parameter to the command. If you leave it out the original name will be used; not very useful if it was simply called Textfile. Once the file is in its desired place you don't really want the !SAVESCRIPT command in it, do you? Load the file from Scripts into your text editor and you'll find the unwanted command has gone. There's just a !SCRIPT END as the last line — even if you didn't put one in! Don't put !SAVESCRIPT anywhere else in the file or your script will be truncated at that point.

12.8 Requesting user input from script files

To 12.6 (Repeating operations using a loop)

This is best illustrated with an example. !QUERY GP=1 will use GP=1 as a search formula. However, !QUERY Search formula? behaves quite differently. A small window pops up saying "Search formula?" with a writable icon into which you enter the formula then click OK or type Return.

input/png This applies to all script commands which take a parameter. Instead of supplying the parameter itself in the script, supply a prompt ending with a question mark and the parameter will be requested and used with the command. Why would you want to do this? You might use a script to print a report in Table format but not always want the same number of blank columns and rows. You can request these with !TABLE Columns?,,Extra rows?. Note the double comma; we're requesting the first and third parameters but not the second which is the default column width. Powerbase checks each parameter string for a concluding "?" which, if found, causes the whole string to be treated as a prompt. The command will first prompt for Columns? as in the illustration. When you enter a number and click OK you will be prompted for Extra rows?

There are likely to be occasions when you don't wan't the entire string to be treated as a prompt. Suppose, in the first example, you want the search formula to be of the form GP=<group> and just supply the group number. So you try !QUERY GP=Group? and it doesn't work because everything up to the question mark is treated as prompt text. We need some means of telling Powerbase where the prompt begins and this is done by including either a colon or a vertical bar (|) as a marker. The script command then becomes !QUERY GP=:Group?. This will prompt you with Group? (rather than GP=Group?) and entering the appropriate number or letter produces the required report.

This capability extends to multiple user inputs in the same parameter string. So if you wanted to request all elements in a chosen group (e.g. T) whose names had a specified ending (e.g. IUM) you could do so with !QUERY GP=:Group? AND NAME=*:Name ending?. You would first of all be prompted with Group? then, having entered that and clicked OK the prompt Name ending? would appear. Note the use of the asterisk as a multi-character wild card to limit the search to the specified ending only. Using the given example inputs Powerbase would construct the script command !QUERY GP=T AND NAME=*IUM.

When requesting input to the !FORMAT command you need only enter the initial letter C(olumns), F(ield-per-line), T(able) or L(abel) as appropriate.

There are two commands which are meant always to be used to request user input:

!GETNUMBER and !GETSTRING
The first of these will place the user-entered value in the numeric variable Var. This variable may then be used with the calc() function (see 3.5.5.4) in search formulae and in "fieldless" calculations performed on the fly. (See 6.5.2). Where might you use this? In any situation where the same user-entered data is needed more than once and would otherwise have to be re-entered each time it was required. It's not easy to provide an example using Elements or Friends so we'll use the Music database for this. The following script will request a year and then list music by composers who have a centenary, bicentenary, tricentenary of their birth or death in the entered year:

!SCRIPT QUIET
!GETNUMBER Year?
!QUERY calc(Var-[COMP]/born),calc(Var-[COMP]/died)=100,200,300
!SCRIPT END

Open the Music database and drop the script on the record window. When prompted enter 2001. The works of Rodrigo (b.1901) are reported. 2004 gives the works of Dvorak (d.1904) while 1985 reports the works of J.S.Bach (b.1685) and Alban Berg (b.1885). The !GETNUMBER command places the entered year in Var, which is then used twice in the following calculation. The birth and death years are subtracted from the value of Var and the results tested to see if any of them is 100, 200 or 300. Note that the calculation refers to two columns of the validation table linked to the Composer field, and that the tag of that field (COMP) is enclosed in square brackets to force the numeric value of the column contents to be used.

!GETSTRING is similar to !GETNUMBER but places the user input in a string variable Var$. As an example consider a hypothetical database of members of a club. There's a field called NAME and a number of check-boxes called 2000, 2001, 2002... which are ticked if a person was a member in that year. The idea is to be able to keep track of membership, seeing who's recently joined, who's lapsed, who's been a loyal member over the years and so on. A useful script for this purpose could look like this:

!SCRIPT QUIET
!SELECT NAME
!GETSTRING Year(s)?
!SELECT Var$
!QUERY Var$=Y
!SCRIPT END

The !GETSTRING command captures the user input, which might be a single year or a comma-separated list of years, in the variable Var$ and !SELECT then adds the relevant check-boxes to the field-selection which begins with NAME. The script then uses Var$ a second time to generate the report using a !QUERY command. If we didn't have !GETSTRING the !QUERY command would be !QUERY Years?=Y and the user would have to enter the year(s) again; tedious and prone to error if the list of years was a long one.

It would be very nice if we could input data not just to one numeric or string variable but to several, and Powerbase allows this using array variables Var() and Var$(). The first !GETNUMBER command in a script places the entered value in Var(0), the second into Var(1) and so on up to Var(9). !GETSTRING uses Var$() in the same way. Since users will often require only one variable and might have scripts which use Var and Var$ these are still supported. top of page

 

Notes

1. DDF: Document Description Format; the means by which styles and effects are encoded within an Impression document. (back)


Home   Index   list of chapters    previous chapter   next chapter