Maintain Databases

Script Editor

Overview

The script editor is provided as a way for you to create and modify custom report script files.  Such files are needed for you to be able to generate your own custom reports.  There are three tutorials to go along with this help topic. These will help you understand how to write scripts using the editor.

The script editor is invoked from the custom reports maintenance page when you create, copy or modify a script file.  The title of the script file being edited is displayed in the title bar of the editor.

This script editor is page aware.  You should set the printer settings before using the editor.  It will detect if you are using A4 or LETTER paper sizes and what orientation.  If you select any other paper size, it will default to A4.  The main thing is to remember to set the paper size to A4 or LETTER and the orientation BEFORE invoking the script editor.  This will ensure that your report displays correctly.  It will attempt to use a margin of 1cm all around the page, but this might be larger depending upon your printer's limitations.

You can maximise the editor to take up the full screen.  Doing so will give you the widest possible raw script list box to work with.  The program will remember whether it was maximized or minimized when last closed, and if neither, remembers its left and top position when last closed. It will come back in this state / position when next opened. The same applies to the Control Panel and Required Variables dialogues (but see section about the  Required Variables topic below).

What is a custom reports script file?  It is a special file with the file extension .CRS and it contains a series of commands.  These commands determine not only how the custom report will look, but also what information gets displayed.  If you can get familiar with the right way of using script files, you should be able to generate virtually any type of custom report that you like.  Several custom reports are already provided.  Please view these reports as well.  Using both the samples and the help topics, you shouldn't have too many problems.

The script editor can be logically divided into 6 separate components.  All of these are discussed below.  So to get the best understanding of the editor, please read the whole help topic.  Otherwise, you may click on the links to jump straight to the component you are interested in.

The six components:

If you are an advanced user, you may view the script command syntax help topic for further information.

Options Menu and buttons

OK Press the OK button if you want to save the changes you have made to the current script file being edited.
Cancel Press the Cancel button to abort any changes made to the script file being edited.  If the script file has been altered at the time you press Cancel, you will be asked to confirm that you want to lose the changes made to the script.
Options menu ScaleView
If this setting is on, then the view of the page in the editor is scaled to fit the width of the page, the height takes on the same scale and thus may be shorter than the window.  In other words, the editor's view shows the whole page at once.

If it is off, then the editor will show the view at 1:1.  It will be necessary for you to scroll left and right in order to view the whole width of the page.

Print Raw Script
Select the Print Raw Script menu item from the Options menu if you want to print out the raw script file.  This is useful if you want to have a hard copy of the script as a backup.

Help menu Select the Help menu item from the Help menu to invoke this help topic.

Select the Tutorial menu item from the Help menu to display the first of three tutorials about custom report scripting.

Raw script list box

Raw script

The raw script list box displays the actual contents of the custom report script.  Commands can be added, deleted or modified.  It is these commands that determine how the custom report will look, and what information it will display.

It uses different colours to clearly show you what are keywords, functions, variables, comments, errors or normal script syntax lines. Such colour highlighting should aid you in understanding your script and identifying errors (see next section for more information about identifying errors). Errors can be avoided by creating and modifying scripts within the editor.  However they might creep in if you paste in a script from another source.  The colours can be changed on the Preferences dialogue.

Any lines of text in the list box that are not displayed completely (because they are longer than the width of the list box) will display in their entirety as a tooltip if you hover the mouse over the line of text. This saves you having to use the horizontal scroll bar to scroll left and right to view the rest of the text.

To add a new command into the script list box, either use the control panel or paste the text in from the clipboard (yes, you can create the script file in your own editor and paste it in - but for this to work you must ensure the script command syntax is valid).  Place the solid cursor (blue on this screenshot) at the location where you want the new command to be inserted.  The next command inserted will push the selected line down, and thus goes BEFORE that line.

To delete one or more commands from the list box, use standard window selection methods and press the delete key.

To modify a list box command, just double click it and make the changes in the control panel.

Adding, modifying and deleting commands can also be done from the list box popup menu.  This menu contains additional features as well.

When a script has been fully refreshed, some pieces may render as if there is a problem in the script. The problem can fall into one of three main categories:

- an error in the way the script has been written (e.g. an incorrect field name).

- the use of a variable whose value has not been previously set, or is currently of a type that can't be combined with the rest of the expression (string vs. numeric).

- or the use of a variable whose type is compatible, but its contents are invalid for what it is being used for (e.g. a string variable with an invalid field name, or a numeric variable way outside the length of a string you are trying to manipulate).

When an <ERROR> ends up on the rendered side of the script, on the script listing side the source line that produced that piece of text will now be underlined with a red squiggle, similar to when Word detects a spelling mistake. See Example 1.

Example script error
Example 1.

Note that since commands within loops can spawn multiple copies of text and some may render correctly and some may not (depending on the state of variables per loop), if *any* rendered text from that command generates an error, the command will be underlined.

If you right-click the marked line, the usual context menu will appear. You should now see, however, additional menu entries marked with a red exclamation mark icon on the left. These are how the script engine reports back to you what it encountered, that caused the error. See Example 2.

Script error description
Example 2.

You cannot do anything with these menu entries; they are for information only. At this point you should try to correct the line of script, or add additional script around it to ensure that variables don't go out of range; once the script has re-rendered without problems, the squiggles will go away.

Note: Due to the way script is currently inserted and attempts to re-render only what is necessary for speed, you may need to use <Ctrl + F5> to perform a full refresh before trying to address any errors. When script is first pasted in it will often result in false alarms until fully refreshed.

Sample page view

Page view

The sample page view shows you what the generated custom report would look like.  This screenshot was taken at 1:1 scale and shows part of the lower area of the view.  The sample page view also shows you the tab stop positions that are available (more about tabs later in the control panel section).  It displays one page at a time.  Move to other pages by using the page toolbar which you can see here.  As previously mentioned, the page view is based upon the current printer settings.

It is important to remember that the information that gets displayed here is determined by the date range settings that were in place at the time you invoked the script editor.  So if you expected information to display, and it doesn't, it is quite possible that the date range was not right.  To change it, you must click OK (to save script) and change the dates on the Custom Reports maintenance page.

 

Control panel dialogue

Control Panel Ribbon Content Control Panel Ribbon System 
The control panel is always visible. But you can minimise it if you want to by using the minimise button on the title bar. The control panel is very powerful. It allows you to add new script commands into the script and update existing ones. Getting familiar with how the control panel works is advised, as it will save you having to understand the script command syntax.

Change Font Change Font

Used to adjust a variety of font properties including:

- Font name, size and style (bold, italic, underline)
- Text alignment
- Banner colour
- Highlight colour
Insert Text Insert Text

Used to insert text, including variables and expressions into the report.
Insert Date Insert Date

Used to insert either today's date or the range start/end dates in a variety of formats.
End of Line End of Line
Command syntax

There are no options for this command. Causes all pieces of text issued since the last EOL (or the beginning of the script) to be output in the colours and fonts selected.

Note that if multiple ALIGN commands have been issued since the last EOL (or script start), only the LATEST issued will be in effect.

You MUST use EOL to output text, it will not show otherwise. Note that xxxFIELD and xxxDATE commands are also treated as pieces of text in this way.
Insert Image Insert Image

Used to insert an image into your report. The image formats supported are:

BMP
GIF
JPG
PNG

Let me know if you need support for other image formats.
Database Loop Database Loop

This is the most powerful script command that you will need to use at least once in your custom reports.  It displays the loop dialogue for you to create / modify a loop command.

A LOOP is used to walk through a list of rows in a specified database table one at a time until there are no more. This allows you to embed FIELD commands to print out contents of that row repeatedly.

There are quite a few options on the
loop dialogue
, so there is a separate help topic.  It is important that you understand how to use the loop script command correctly so please click on the link if you have not already done so.
Insert Field Insert Field

Used to insert the contents of a field into the report.
Lookup Field Lookup Field

Used to save the value of a field from one database row into a variable.
Meet Time Meet Time

Used to insert the congregation meeting time into the report.
Insert Tab Insert Tab

Used to insert a tab stop at a specific location in the report.
Blank Lines Blank Lines

Used to insert blank lines into the report.
Insert Separator Insert Separator

This draws a horizontal line across the page in a certain style and for a certain percentage of the page. The line drawn is always centred and uses the current colour (font and banner) settings.
Line Spacing Line Spacing

Used to change the active line spacing.
End of Page End of Page
Command syntax

This issues an end of page command. There are no options for this command. A possible reason for using this command is during a LOOP. Within the loop you could put an end of page command at the bottom. That would ensure that each iteration of the loop would start on a new page. The start of a new page is indicated by a dashed line in the editor.
Request (User) Request (User)

Used to make your scripts flexible. This introduces variables into your scripting, thus allowing you to get the user to provide details later on.
Set New Value Set New Value

Used to make your scripts flexible. This introduces variables into your scripting. But these variables differ from the REQUIRE's command because they are only used internally within the script. No prompt is provided to the user asking for values.
If Condition If Condition

This is the second most powerful command that you may like to use in your custom reports. It displays the IF dialogue for you to add conditional processing to your reports.

An IF is used to allow the schedule to show different details based on different results. Also, they can only be used inside an existing LOOP.

Under some circumstances you may find that the IF command does not appear to behave correctly. If this happens, try to refresh the script, as this usually resolves most problems. When you are creating an actual document based on this script, the IF command does not suffer this anomaly.

An IF command can now contain expressions on either side of a comparator, by embedding them within { and }. Such expressions will be evaluated first and the result fit back into the line, before the script engine tries to evaluate the IF. For example:

IF ("$variable_name" > "{ $iRowCount / 2 }" )
IF ( "{ $variable_name + 1 }" > "5")

Plain / Option Loop Plain / Option Loop

Used to loop through a fixed set of values or number sequence.
Insert Comment Insert Comment

Used to add comments into your script so that you can describe what your script is doing.
Script INCLUDE Script INCLUDE

Used to include another script file at the selected location. This means you can re-use other script files and save on duplication.
Script Spacer Script Spacer

There are no options for this command. It has no effect on the actual report generated. It inserts a blank line into the script file. Like this:

Insert Script Spacer

Use script spaces to break up your script into sensible sections. It will help make your script file easier to read and understand. You can also insert a script space using CTRL + Space.
   

Update variables dialogue

Required VariablesThe update variables dialogue is self explanatory.  It will only be of use to you if you have included one or more REQUIRES script commands. The require command is discussed earlier in this help topic.

If you have any variables referenced in your script within the editor, it will show the Required Variables dialogue until you have set all the values. Also, if you add a new REQUIRES command to the script, it will do the same.

All of the prompts that you have assigned to variables are shown in a list. Just select the prompt and then use the edit box (combo) to update the data for the variable.

Hit the Update button after you have specified all variables to commit the change(s) into the editor.

Remember that AwaySpeaker and Congregation are special variables. These will present a droplist of data for you to select an appropriate value from. Otherwise you can enter the text directly. In addition, any variable that you supplied default options for, will show them in the drop list too.

To use the variable in your script, you must place a $ before the variable name. Several of the sample scripts included use variables. These should give you a good idea on how to use them effectively.

For detailed information about using variables in your script file, look up the script command syntax for the TEXT command.