Database Loop
|
Read more: Command Syntax |
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.
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 window which allows you to perform more complex database queries.
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.
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.
|
This displays the Add / Update Criteria window for you to insert a new clause into the list. |
|
|
This displays the Add / Update Criteria window for you to update the selected clause in the list. |
|
|
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:
|
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 application.
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.
Set this option to optionally sort the records retrieved.
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.
Select this option if you want the records to be sorted in descending order (i.e.: 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.
The 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
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:
|
