Use the DB
Loop dialogue to insert a LOOP into the
custom report script file. 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
To learn more about the
features of this dialogue, glance down the table below or highlight
the control you are interested in by clicking on the screenshot
above. Please also remember that context help is available to you
by either pressing F1 when the control has focus,
or by using the question mark icon at the top right corner of the
Please note that the
RowNumber field is shared among all loops at this
time. If you have nested loops, RowNumber will be
correct for the outside loop before the
nested loop, but not afterwards; it will change to be correct for
the nested loop, and thus corrupt the outside version. To work
around this, if you need the row number after the nested loop then
simply use VARIABLE_FIELD to save it to a variable
that you can use later.
||Press this button to
display the raw SQL dialogue:
This dialogue allows you to perform more complex database
The example on the left uses table JOIN's to combine the results of
two or more tables together.
You can still save the row count to a
variable, just like you can on regular loops.
It is assumed that you are already familiar with writing SQL
queries and you use this facility at your own risk.
Since we're using raw SQL, standard SQL rules must apply:
* Literal text and variables designed to hold such data (except
= single quotes
* Literal dates and variables designed to hold dates
= no delimiter (assumes date has been SQL formatted - if not,
requires a hash each end)
* Literal numbers (floating, integer or true / false) and variables
designed to hold such data
= no delimiter
* Expression function parameters
= double quotes (our functions require double quotes around
strings, and also around dates (even if SQL formatted)).
- You must specify the sort order yourself within the SQL query (if
- You can use variables within your query.
Don't forget to wrap them with
' ' if they relate to a textual comparison (eg:
- You use CURRENT."Field name" within your query.
- You must not duplicate field names when performing table JOIN's.
Use the SQL ALIAS command to ensure that each field in your
resulting query is unique.
Use the dropdown listbox to
select which table you want to retrieve information from to be used
in the report. You can even select one of your own tables that you
may have personally added into the database.
If you need to narrow down which
records will be retrieved from the database, then you must add one
or more criteria to this listbox. The available information would
already be limited to the date range settings that were in effect
when invoking the script editor. But you can override this by using
the Ignore Date Range option (see below).
You cannot directly edit this
list, but have to use the 3 buttons to the right to
add, edit or
To see a working script
using criteria, please view the Incoming Talk History
Sample script which was installed with the program.
you press the Add New or Edit
buttons the following dialogue is displayed:
Make the required changes and when you
dismiss the dialogue the changes will show in the criteria list.
Here is a summary of the options:
This listbox contains all the fields that
are in the currently selected table. This will include any custom
fields that you might have added to a table in the database.
Select the field that is to be used for
narrowing down the records retrieved.
Use this dropdown listbox to select how you want to find the
relevant records. There are five choices:
Use this if you want all records
that match a value.
MUST NOT BE
Use this if you want all records
that DON'T match this value.
The following 3 choices only work with text and numeric fields.
The Add process will fail if you try to use these choices with any
other type of field.
Use this if you want all
records where the contents partially match a value.
MUST START WITH
MUST END WITH
Use either of these options if you want
all records that start or end with a particular value.
MUST BE LESS THAN
MUST BE, OR BE LESS THAN
These criteria should be used with numeric or date fields.
MUST BE MORE THAN
MUST BE, OR BE MORE THAN
These criteria should be used with numeric or date fields.
MUST BE MULTIPLE OF
This criteria should be used with numerics.
The comparison is case insensitive, so "A Name" and "a name"
would be classed as the same.
This edit box will change depending upon
what type of field is currently displayed. Keep in
mind that the settings Current row and
Variable will replace this edit box with another
listbox. The four types of field that are supported are:
Text / Number / Date / Yes - No
Use the edit box to specify the
value to be used in the
comparison (described above).
The Current row option is
only enabled if you are inside an embedded loop.
In other words, this loop is INSIDE an outer loop.
If this is the case, the row that is currently being accessed in
the OUTER loop is termed the "Current Row", so you can access this
information when narrowing down your records.
When this option is on you will see a list
of available fields from the OUTER loop.
Variable option if you want to use a variable in
the where clause. When this option is on,
you can choose a variable from those available in the dropdown
list. This makes the LOOP command very powerful. Read up on the
REQUIRES command if
you are not sure about variables.
||You can use an expression to represent the value to
Please be aware about how it works for date fields. When you first
select a date field from the drop list on the left, the control on
the right is a standard date picker. If you want to compare with
the result of an expression, click the Expression
button on the right. When you OK the expression
editor dialog, the date picker will be replaced by a normal text
box, as if we were comparing against a string - the expression will
be entered into this box.
If you accept this criteria and then edit it later, it will note
that it is an expression and automatically bring back the
expression and text box instead of the date picker.
If you subsequently select any date field from the drop list on the
left - including re-selecting the same date field - the text box
will revert to the date picker. This is how you can revert to a
simple date. To avoid losing your expression, don't change the
field drop list on the left while an expression is showing on the
The LOOP command supports flexible
criteria. The above options are not all available to use
at the same time because it depends upon what criteria is selected
and the context. We will attempt to explain the purpose of each
component and how to use it.
When two or more
criteria are used for WHERE or IF, by default both criteria must be
TRUE to proceed. This is also accomplished by selecting AND when
you have the second criteria selected. Note that you can chain
multiple criteria together with AND, or a mixture of AND /
By default, in a mixed
expression containing AND / OR, any criteria combined using OR will
be evaluated first, and their result then used in AND combinations.
To give certain parts of the expression priority or simply to
clarify the intended order of evaluation, you can select a pair of
criteria and use brackets around them. Note that you don't have to
use brackets only around two consecutive criteria; you can select
three or more criteria and use brackets. The brackets will be
placed / removed around the first and last criteria selected.
It is *strongly* recommended that you use brackets when mixing AND
/ OR to clarify what you intended and to prevent unexpected
results. For example, (w OR x) AND (y OR z).
When two or
more criteria are used for WHERE or IF, by default both criteria
must be TRUE to proceed. If you want to proceed if *either*
criteria are TRUE, this is accomplished by selecting OR when you
have the second criteria selected. Note that you can chain multiple
criteria together with OR, or a mixture of AND / OR.
Sometimes it is more
convenient to reverse the logic of a test result than to rearrange
the test using different comparisons. This is accomplished by
negating the term (a term is one criteria). Note that if brackets
are present in front of a criteria, NOT will always appear
Note: It is possible by pasting script in directly to achieve (NOT
x AND NOT y). This is perfectly legal, but cannot be accomplished
using the dialog due to keeping it simple to use. If you need this
exact expression, note that it is logically equivalent to NOT (x OR
y), (note that AND has changed to OR) which *is* possible using the
You will learn how to use criteria by
implementing some scripts and viewing the samples provided.
||Press the Remove button
(or press the Delete key) to delete the selected
criteria from the listbox. Note that you cannot remove any criteria
from the list if it includes brackets. If you try to do so it will
show you an error message:
||Press the Edit button to
make changes to the selected criteria. See the help for Add
New for details about setting the criteria.
Set this option to optionally sort the
||If you chose to sort the records, then this
shows a list of fields for the chosen table. Simply choose
the field you want to sort records by.
||Select this option if you want the records
to be sorted in descending order (ie: Z to A).
Setting this option will
prevent the loop from using the date range specified when creating
the report (or before entering the editor, if editing the script).
This ensures all records will be retrieved.
It is good for inner loops, where it is
USUALLY only the outermost loop that you want to apply the date
Note, though, that the date range ONLY
applies to Home Talks, Away Talks, Public Talk Titles and
Congregations tables - it has no effect on any other table, even
ones you may have added. It will be ignored for any other
||This is an advanced scripting feature
and should only be used if you have a good understanding of the
custom report scripting language.
There are situations where you need to
know how many rows (or records) are held in the LOOP and this is
acheived by using a variable.
Set the option Save row count to
variable. Use the drop list to select your variable or
type in a new dynamic variable.
The resulting variable can be used
anywhere inside the LOOP and anywhere after the LOOP.
the last row in a LOOP
Here is an example showing you how to
detect the last row in a LOOP:
|Depending upon whether you are editing an
existing loop or adding a new loop, this button will say Update or
Insert. Press it to commit your changes into the script.