Insert DB Loop buttonClick Database Loop on the CONTENT ribbon panel to display the Insert DB Loop window.

Read more: Command Syntax

Insert DB Loop WindowChoose table nameUse raw SQL for the loopNarrowing criteriaAddEditRemoveEdit criteriaSort rowsFieldReverse sortIgnore date rangeSave row count to variable

Use the DB Loop window 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.

You must press Insert (or Update if editing an existing command) for your changes to be applied to the current script in the editor.

To learn more about the features of this window, glance down the table below or highlight the control you are interested in by clicking on the screenshot above.

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


Use raw SQL for the loop

Press this button to display the Raw SQL window which allows you to perform more complex database queries.


Choose table name

Use the drop-down list 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 using Microsoft Access.


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

Add

This displays the Add / Update Criteria window for you to insert a new clause into the list.

Edit

This displays the Add / Update Criteria window for you to update the selected clause in the list.

Remove

Press the Remove button (or press the Delete key) to delete the selected criteria from the list-box. 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:

Remove Criteria Popup Message

Note IconYou 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 application.


Edit Criteria

The LOOP command supports flexible criteria. In other words, you can group specific clauses together and therefore fine tune which database records you work with. Click the link to learn more about this feature.


Sort Rows

Set this option to optionally sort the records retrieved.


Field

If you chose to sort the records, then this shows a drop-down list of fields for the chosen table. Simply choose the field you want to sort records by.


Reverse Sort

Select this option if you want the records to be sorted in descending order (i.e.: Z to A).


Ignore date range

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 IconThe date range only applies to Away Talks, Congregations, Home Talks and Public Talk Titles tables - it has no effect on any other table, even ones you may have added. It will be ignored for any other table.


Advanced Scripting

Save row count to variable

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 achieved by using a variable.

Set the option Save row count to variable. Then use the drop-down 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.

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

Detect Last Row