Script Editor

Insert DB Loop Dialogue

You get to the Insert DB Loop dialogue by clicking the Insert DB Loop button on the script editor's control panel.

Insert DB Loop Dialogue

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 repeatedly.

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 dialogue.

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:

raw sql dialogue

This dialogue allows you to perform more complex database queries.

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 dates)
= 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)).

Notes:

- You must specify the sort order yourself within the SQL query (if required).

- You can use variables within your query.
Don't forget to wrap them with ' ' if they relate to a textual comparison (eg: '$LocalCong').

- 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.

Choose table name

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.

Narrowing criteria

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 remove criteria.

To see a working script using criteria, please view the Incoming Talk History Sample script which was installed with the program.

When you press the Add New or Edit buttons the following dialogue is displayed:

Add / Update criteria

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: MUST BE 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. MUST CONTAIN 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.
	Set the 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 compare against. 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 right.

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.

	AND 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 / OR.		Use brackets 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).
	OR 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.		Negate the term 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 outside. 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 dialog.

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:

Delete criteria with brackets

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 records retrieved.

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 range to.

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 table.

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.

Detecting the last row in a LOOP
Here is an example showing you how to detect the last row in a LOOP:

Detect Last Row

Update

Insert

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.