(Starts a new "MoreDatabase" script)

The following commands are used in this module:

Tutorial 2 - Module 17 - Date RangeBefore we start getting clever, let's talk criteria. Banging out every row is all very well, but you are most likely interested in a selection of rows only.

If you're only interested in selected dates, then this can be achieved more easily by specifying the date range on the Custom Scripts Management window. The rows returned in a loop will automatically be filtered to on or within these dates for you, unless you switch on the Ignore date range option, which will negate that filter and just do them all again.

Tutorial 2 - Module 17 - Script (1)If you want to perform other filters, you need to add criteria to your loop.

  • Start a new script called "MoreDatabase" (no quotes).
  • Insert a LOOP command on the "Brothers" table.
  • Switch off the Sort by option.
  • Within this loop, insert a FIELD name on the "BrotherName" field, no interpretation (of course).
  • Insert an EOL to end the line for each row.

You should end up with a list of brothers (assuming you have added their names into the database).

Using Criteria With Loops

The Narrowing criteria box lists the criteria in effect to filter the rows being given to you. Below that are the controls for entering new criteria, or updating them. A fuller explanation of how to use this is given elsewhere in the help.

As an example, we will now modify the LOOP by adding criteria to limit the names listed to just those who are assigned as chairmen:

Tutorial 2 - Module 17 - Add Criteria (1)

  • Choose "BrotherChairman" on the left.
  • Choose "MUST BE" in the middle.
  • Choose "YES" on the right.
  • Click Add New.

Tutorial 2 - Module 17 - Add Criteria (2)You should now see your criteria entered into the box, in a slightly different form but recognisable.

  • Click Update, and observe your output.

See how anyone who is not assigned as Chairmen are no longer listed? Any rows where the  BrotherChairman field is -1 were eliminated, so you only got given what was left.

Criteria are a powerful way to get almost exactly what you want, and are quite flexible. More details of how to use the criteria are given elsewhere in the help, so we won't go into too much detail.

Using Loops Within Loops

Tutorial 2 - Module 17 - TaskSo we have a list of brothers... now what? What if we want to print out the dates of all assignments that this brother is earmarked for? 

For each row we get (thus, each brother), we want to:

  • Examine another table.
  • Produce not one but a list of items for that brother.
  • When that list is done, we go back and get another brother.

How do we do this?

Answer: We insert another loop, but this time it will be nested inside the first one. Thus, this inner loop will be executed for every single row given to us by the outer loop. This will give us a list of items for every chairman; so far, so good. Let's try it.

Note IconBefore we try this, remember that it is possible that not all brothers in your Brothers table will be assigned within your selected date range, and so the inner list will only be seen against certain names; this is not an error, it is simply that for brothers who have no assignments within your selected date range, the list of dates against their name will be empty - exactly what you see.

Tutorial 2 - Module 17 - Script (2)

  • Put the highlight (insert point) on your END_LOOP (since we still want the chairman's name printed so we can see the outer loop doing its work).
  • Insert another LOOP. Make this one on the "Home Talks" table, no criteria for now.
  • Switch off the Ignore date range option.
  • Switch off the Sort by option.
  • Click Insert.
  • Highlight the inner END_LOOP and insert a TAB command, absolute, value 6.
  • Insert a FIELD command on the "Last Given" field (format as you wish).
  • Click Insert.
  • Don't forget to tie it off with an EOL.

No doubt you have a barrage of dates for each name. But hold on, that's not right? It's blindly printing out all dates on all rows of the Home Talks table regardless of the name. Aha! We need to filter the inner loop by brothers name.

Tutorial 2 - Module 17 - Script (3)Edit your inner loop and add criteria as follows:

  • Chairman (left)
  • MUST BE (middle)
  • And the first name listed on the output since that brother 's the chairman for the row (right). Add the new criteria, then Update.

Sometimes you end up seeing commands you have inserted into inner LOOPs with squiggles. Under normal circumstances all you need to do is right-click the script and select Refresh or press <CTRL+F5>

Now, if alarm bells haven't already started ringing, you may need to think about that one. From the new output you might realise something is wrong.

  • You will either get a bunch of dates against every name again (but less this time).
  • Or no dates against any name.

Trust me, this is wrong, since it is not what we wanted.

Go back a couple of lines and read how we added the criteria for that inner loop. "... and the first name listed on the output since that's the chairman for the row...". Oh dear. It's the chairman for one of the rows, but we must remember that this inner loop gets executed for every single row given to us by the outer loop. So the chairman name will be changing each time we hit our inner loop - we can't use a literal name, otherwise we'll always be retrieving dates for the same chairman.

Cast your mind back to the emphasis we placed on saying the current row in a previous section. This is the key here: we need to use the chairman for the current row as the criteria for this inner loop, to make sure it always narrows the dates down to the right chairman for every new chairman.

Edit the inner LOOP command again:

  • Double-click the criteria you added, since we need to update it somehow.

Look at a couple of the controls we haven't mentioned so far: Current row, and Use variables. We are not doing variables here as they are beyond the scope of tutorial 2. There's that word again - 'current'.

If you click this option to switch it on, what happens? Our chosen name is replaced by a list, and that list contains fields. But not just any old fields. Compare this list to the one just above it, where we chose "Last Given". It's not the same list. That's because it's the list of fields from the current row of the outer loop.

Tutorial 2 - Module 17 - Script (4)Now, finally, we have the chance to make sure our Chairman on this inner row is the same name as the Chairman from the outer row, thus ensuring that only rows belonging to that person are given to us on this inner loop.

  • Select BrotherName in the list on the right.
  • Click the Update button.

This changes our saved criteria to use our new information.

  • Click Update on the LOOP window to put the new LOOP back in the script.

Note IconIf you see <ERROR> showing up (for the brothers who had no assignments as Chairman during the date range) then please save the script and open it again. The <ERROR> text should no longer be there.

  • CURRENT refers to the current row being worked on from the outer loop.
  • The field name before IS is a field (from each row being worked on) from the inner loop.
  • The field name following CURRENT is a field (from the row being worked on) from the outer loop.
  • Thus, you put CURRENT. in front of a field name to make it get the database contents from the outer loop's current row, not the inner loop's.

Now, finally, you should see the small list of dates against certain names, and that list of dates should be different for each unique chairman name.

In Conclusion

We do realise that these last couple of sections are quite daunting, and we don't expect you to suddenly be able to create masterpieces. But with a bit of practice, and a certain amount of trial and error, you should be able to gradually figure out what we are talking about, and you will start to benefit from the flexibility this scripting engine provides to customise your reports.

Tutorial 2 - Module 17 - AnimationAs one final attempt to help you understand how nested loops are parsed, we include a little animation opposite - the highlighted line represents the line the parser deals with next. The script shown in the animation is not the one we have been working on, but is simply a sample for the purpose of showing how nested loops are parsed.

Well done on completing tutorial 2! We hope it has been useful and helped you to get a good grasp on some of the fundamental concepts of scripting and the flexibility that you have at your disposal.

Since you have got this far and everything is making good sense to you then... what are you waiting for?! Go and read Tutorial 3 - Advanced Script Features.

Previous Module