Public Talks

The following commands are used in this module:

• EOL
• LOOP

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

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

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

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

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

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.

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.

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

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