The moment you have all been waiting for: some real script to demonstrate these features.

REQUIRES "Speaker?" AS $mSpeaker
TEXT "All speakers:"
LOOP FROM "Congregation Speakers" NODATERANGE WHERE "Congregation" IS "$LocalCong" SORTBY "Speaker"
   FIELD "Speaker"

TEXT "Away speakers:"
LOOP FROM "Congregation Speakers" NODATERANGE WHERE "Congregation" IS "$LocalCong", "AwaySpeaker" IS "-1" SORTBY "Speaker"
   FIELD "Speaker"

TEXT "Speaker ($mSpeaker) from home loop:"

LOOP FROM "Home Talks" WHERE "Speaker" IS "$mSpeaker", "Congregation" IS "$LocalCong" SORTBY "Last Given"
   LONGDATE_FIELD "Last Given"

Remember, this script can be pasted into the raw script pane by following the information given during Installing Sample Scripts in the second tutorial.

Hopefully you will see some sensible output. Let's explain where we have used the variable features we've talked about, and then we'll explain about a couple of other things in the sample above.

The First Loop

The first loop lists all speakers from our grand list of congregation speakers, but only for our local congregation. We used the special $LocalCong variable to limit the Congregations returned in the loop to just those matching the one we marked as our own. Thus, we only got our own speakers.

The Second Loop

The second one does something very similar, but you got less names - why? Because we used more than one criteria to narrow the search. If you edit the second LOOP command to bring up the loop window, you will see that there are two rows in the criteria list. The more criteria you add, the further it narrows your search - but be careful not to narrow it so far that you get no results!

The Third Loop

The third loop also uses $LocalCong to limit the search to our own congregation, and also has more than one narrowing criteria; this time, it uses a variable that we have to fill in ($mSpeaker), to return only those rows of the database table that are for the speaker we name. Thus, it lists all of our congregation's home talks given by the named speaker, and no others.

Other Things You Should Be Aware Of

And what are those other odd bits?


NODATERANGE is a special keyword that is used only in loops. It tells the parser to ignore the date range we specified. Thus, even if you specified a narrow set of dates to work from, this loop would ignore that and consider all dates as valid, if everything else on the row is valid. This flag is set by selecting "Ignore date range" on the loop window.

Why not just specify the full range? Because there are times when we do want to narrow the search by date, and times when we don't. Thus, we specify a limited range and selectively tell the parser to ignore it to break out of that limit when necessary. For example, you might be listing the away talks for a particular date range, but what to show the meeting time for each congregation. Since the Congregations table includes a date field, it would also reduce the records returned, which could cause you problems. So, to ensure you do get the meeting time correctly, you would negate the selected date range by using NODATERANGE.


SORTBY is another keyword which you have probably guessed is a flag on the loop window. It tells the parser that when it lists the rows it finds matching the criteria, don't just throw them at us in any order; give them to us so that the contents of the named field are in alphabetical order. There is also a REVERSE_SORTBY that does the opposite of SORTBY, should you want it.

  • Boolean Fields

Finally, what's up with the "-1"? Well, in that table the field called "AwaySpeaker" is a boolean field - that means it is a flag that is either set or clear.

  • If it is clear, it is 0.
  • If it is set, it is -1.

This is how databases see it, and is a quirk of databases. Luckily, if you edit that LOOP command to bring it up in the loop window, you will see that flags are actually shown as Yes and No options to make it easier to use. So you can use that to narrow the criteria based on a flag. Thus, that loop only lists those speakers that are marked as away speakers, as opposed to all the speakers.

We hope you have enjoyed these tutorials. If you have any further questions, or think we could add another tutorial then please get in touch. See  the Contact Information help topic.

Previous Module