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. Script files are plain text files (created with Edit, StrongEd etc.) and are executed by dragging them to the Powerbase record window. 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 options buttons (e.g. !CASE, !UPPER) the parameter is either ON or OFF.
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.
If the PrintRes directory contains a script file called !Script it will be executed automatically when the database is opened.
Many of the following commands take a filename as a parameter. If you just give a leafname Powerbase will look for it in the database's PrintRes directory. If it is stored anywhere else either the full pathname or a relative pathname must be supplied.
Where a script makes use of Selection, Print options, Query or other files which are relevant to only one database, the best policy is to store all these and the script file itself in the database's PrintRes directory and link the script file 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.
Clicking the Run file button with ADJUST will display the stored pathname.
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.
"Reporting" is used here in its broadest sense for any operation capable of producing a file containing record data. Thus, creating CSV files and exporting single records are included.
!SELECTION is used to select fields for inclusion in a report. If a filename is given as a parameter Powerbase looks for a print 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 note above for the interpretation of filenames.) Alternatively, you may supply a comma-separated list of the tags of the required fields and 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.
!SUBFILES <n>[,<n1>,<n2>...] selects the subfile(s) which will be searched in order to create a report.
!CASE turns the Case button on the Query panel ON and OFF.
!QUERY [filename\]<search formula> is used to print a report. It returns the same result as entering a search formula in the Match window and clicking Print. 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. Any other name causes the file to be created in PrintJobs or in a subdirectory (which must exist) of Print Jobs. Note the backslash between the filename and search formula; this tells Powerbase where the one ends and the other begins. !CASE also affects the behaviour of this command if issued beforehand.
Instead of the search formula you may supply the name of a saved Query file. If this file is in PrintRes 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 Shift SELECT clicked the Print button on the Match window.
!SAVE <filename> 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> 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> is similar to !QUERY but produces a CSV file instead. Once again, the name of a Query file may be supplied instead of a search formula. The CSV option settings are observed. A prior !DESTINATION command is unnecessary in this case.
!INCLUDE, !EXCLUDE, !CLEAR have the same syntax and may be treated together. They have the same effects as the controls on the Mark pane attached to the record window. !INCLUDE <key>[,<key>,<key>...] has exactly the same effect as ticking the check-box on the Mark pane for records with the associated primary keys. !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. As an alternative to a comma-separated list of keys you may supply a filename. The file should be a plain text file with one primary key on each line. (See 12.1.1 for the interpretation of filenames.)
!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 Print 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 above).
!FORMAT <string> where <string> is Horizontal, Vertical, Label or Table sets the print format. If any other parameter is used, or !FORMAT is issued by itself, the Horizontal format is used.
!EXPAND ON selects the button which causes fields to be expanded by reference to a linked validation table. !EXPAND OFF (or just !EXPAND by itself) deselects the button.
!DATE turns date and time stamping ON and OFF.
!UPPER forces upper-case printing ON, normal upper/lower case printing otherwise.
!HEADER, !FOOTER determine whether header/footer information (see 3.10.2) is printed on reports.
!FIRSTPAGE when followed by ON 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 determines whether surplus "white space" is removed from between the columns of reports in Horizontal format.
!HEADINGS D causes descriptors to be used as field identifiers in a report. !HEADINGS T, or without any parameter, causes tags to be used.
!TITLE <string> uses <string> as a title on subsequently printed reports.
!PAGE <n> sets the page length for reports to n lines (see 3.10.2).
!SPACER <string> specifies the string to be used to separate columns of printed data in reports using Horizontal or Table format (see 3.10.2).
!TEXTWIDTH <n> specifies the maximum width in characters for Memo and Text block fields in reports.
!SORT <n> specifies a column on which the report is to be sorted before printing. The tag of a field included in the report may be specified instead of a column number. The !SORT must appear before !QUERY in the script file. It cannot be used when the output destination is File.
!ORIENTATION <orientation> changes the paper orientation for hard-copy printing The default is portrait or upright, the alternative is landscape or sideways.
!PRINTCOLUMNS <n> sets the number of columns used in printing a report when the Destination is Printer. n may be 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 sets the size, in points, of the fonts used for hard-copy printing.
!LMARGIN, !RMARGIN, !TMARGIN, !BMARGIN <n> set the print margins for hard-copy printing. 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, and the number of blank rows at the end of the report, in Table format. 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 !LABEL ,,6. Note the commas.
!LABEL <n>,<w>,<h>,<str1>,<str2>,<s>,<su>,<bl>,ON/OFF determines the setup for label-printing where: n=number of labels across the page, w and h are the width and height of label (units may be specified as for margins and column widths), str1 and str2 are the optional fixed first and last lines and s=number of label at which printing on first page is to start. su and bl are field tags. If field bl is blank then field su will be printed instead. The final parameter specifies whether the primary key is to be printed on the label. As with !TABLE you need only specify the values you wish to change, but watch those commas!
!COPIES <n> specifies the number of copies to be printed.
!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 may be used to move about the current subfile and display records. The meanings of the commands are pretty obvious.
!DELETE <key>[,<key>,<key>...] 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 above for !SCRIPT <filename>. The command can delete records in any or all of the six subfiles, not just the current subfile
!INSERT is the counterpart of !DELETE. Useful in similar circumstances. In this case the command must occupy a line by itself and be followed by the record to insert, one field to a line. It is the user's responsibility to ensure that the lines are not too long for the database fields into which they are placed and that the number of lines following each !INSERT is the same as the number of fields in the record. Don't forget to leave blank lines for empty fields! Records will be inserted into the currently-selected subfile.
!MOVE has the same syntax as !DELETE but moves records from their present subfile to the next in sequence, regardless of the currently-displayed subfile; e.g. a record in subfile 2 will be moved to subfile 3.
!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).
!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.
!FILTEROPEN <search formula> is 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.
!KEY <index name or number> where the specified index appears on the menu of loaded indexes will make that the currently active index. If no parameter is given the primary key index is selected.
!INDEX <tag> constructs an index on the field whose tag is specified. Used in this, simplest, way the number of characters, word-number and position (see 4.5.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:
A further colon may be followed by a string consisting of some or all of the letters COPJ to select the icons for Case specific, Omit null keys, Pad with spaces and Justify numbers.
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:
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.
!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 or MOUSE A as the parameter to !LOOP and placing !WAIT (see below) inside the body of the loop. In the first case the loop will terminate when you click SELECT, in the second 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.
(5) When a preceding !DATA command runs out of data. See !DATA (below)
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. 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:
!LOOP UNTIL stop !FIND Key to look for? !PRINT !SAVE !ENDLOOP
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'.
!PAUSE <n> pauses execution of a script file for <n> centiseconds.
!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.
!DATA <param> [,<param>,...] 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. When a command next 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 following script, based on the Elements database, will clarify the situation.
!DESTINATION FILE !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 print 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 the example below. Note that the !DATA line must precede any command which READs from it.
If you wish to provide your own names for the reports these can also be included in the !DATA list, alternating with the search formulae:
!DESTINATION FILE !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.
!IMPRESSION is used in conjunction with !QUERY to generate a report in text-file format, allows you to insert Impression DDF (Note 1) commands 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 . This can be achieved by placing the script command !IMPRESSION before the first !QUERY. You do not, however, want to begin the first file created, or the Impression document will have a blank frame on its first page. To suppress the effect on the first file use:
!OBEY <string> 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.
!MAILMERGE <filename>[,M,D,P] prepares for a mail-merge with Impression or Ovation Pro by loading the document specified in <filename> and opening the Merge window. Optional parameter M causes the first record in the subfile to be merged as if the Merge button had been clicked. If D is also present the displayed record is merged instead. P causes all records to the end of the subfile to be merged and the pages printed. A !FILTER command, issued before the !MAILMERGE, will restrict the merge to records which match the search formula.
!MESSAGE <string> will display a user-defined message in a small window to keep the user informed of what's happening during execution of a script. A numeral in the range 1-9 attached to the end of the message may be used to specify how long the message remains on screen. If no number is given the message remains until !MESSAGE is issued on its own. This closes the message window.
!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.
This is best illustrated with an example. !FILTER GP=1 will use GP=1 as a search formula. However, !FILTER 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.
This applies to all script commands which take a parameter. Instead of supplying the parameter itself in the script, supply a prompt followed by a question mark and the parameter and 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 (column width is not changed) and the comma after "?". Powerbase checks the parameter string for a concluding "?" which, if found, causes the whole string to be treated as a prompt. This is fine for commands like !TITLE which take only one parameter but those which take multiple parameters need the concluding comma.
When requesting input to the !FORMAT command you need only enter the initial letter H, V, T or L as appropriate.
1. DDF: Document Description Format; the means by which styles and effects are encoded within an Impression document. (back)