Custom Reports

Scripting Tutorial - How To Write Scripts

These are the sections of this tutorial.  They must be followed in sequence in order to build on the knowledge gained in the previous sections.  So please don't skip a section.  If you are returning to the tutorial and want to carry on from a particular section of the tutorial, then you can use the hyperlinks provided below to jump to the right place.

The Basics
Hello, World!
Multiple Text Blocks
It's A Date, Then
Oh, I'm Blushing!
This Rainbow Has No Gold At The End
The Font Of All Knowledge
Queue To The Right, Please
I've Got A Memory Like An Elephant
May I Squeeze In There?
Move Over, Please
You Move Over Here, Queue To The Left; You Move Over There, Queue To The Right
Is My Back A Funny Colour?
I'm Going Loopy Here!
Fields Of Golden... Text
Man, That Looks Weird
Inner Peace, Inner Loop

1. The Basics

If you have never written a script before, then it is strongly recommended that you learn about how to manage your scripts, and the basics of using the script editor dialogue and custom reports maintenance page. It will make it much easier to work your way through this part of the tutorial. To learn the fundamentals of using the script editor, see Using The Script Editor.

To begin this tutorial, let's create a new script. From the custom reports maintenance page, click New, enter the name "HelloWorld" (without the quotes), and click OK. The editor window itself will open up.

 

2. Hello, World!
(Starts from the new "HelloWorld" script)

Huh? What does that mean? Well, it's a term used by programmers to embrace the concept of the very first program they write in any new programming language they learn. It represents probably the simplest thing they can get the code to do, and they then work their magic from there. Why that phrase in particular? Well, if you were a new-born baby and you could talk as soon as you were born, wouldn't your first words be "Hello, world - here I am!". Thus, to a programmer it represents the birth of their new set of skills in the new programming language.

Since scripting is a form of programming, we are going to follow that tradition. Our first script is going to do the same.

Assuming your editor is open and the Control Panel is visible, click Insert Text, enter "Hello, World!" (without quotes) in the 'Variable prompt / text to add' box, and click Insert. You should see the command go into the box on the right as TEXT "Hello, World!", and on the page to the left...

Disappointed? Nothing appears. What's gone wrong? Answer: nothing - we just haven't given the parser enough information to do what we want.

On the control panel, click Insert End Of Line, and click Insert (from now onwards, the phrase "Click xxx" implies on the control panel). Now are you happy? As long as you haven't accidentally clicked in the box to the right and changed where the solid bar was, you should now see your masterpiece rendered on the left.

So, why did we need that end of line ("EOL" in the script)? Why didn't it print it out straight away? Answer: how can it know you meant that that was the entire line you wanted? What if you wanted more text on that same line? You could argue that if you had wanted more, you'd have put more into the box when you entered the text the first time; but there ARE good reasons for it, which you will see later. For now, understand that in order to render a line of text, the parser needs to see an EOL at some point to know when the current line is complete. Think of it like having to use the lever to push the typewriter's barrel back to the start position after each line.

For practice, exit the script editor using the OK button to save this script, and then start a new script called "TextChunk" (no quotes). When you're back with the editor open and waiting, read on.

 

3. Multiple Text Blocks
(Starts from the new "TextChunk" script)

As you may have realised after learning about the need for EOL, you can bundle more than one TEXT command into the script before the EOL and they will all be rendered on the same physical line.

Let's try it, eh? Click Insert Text and set "Hello" (no quotes and no comma for now) as the item, then click Insert. Then do the same again, using "World!" as the text. Finally insert an end of line (EOL) as before, and you should see your efforts rendered.

If you have followed the instructions carefully, you should be a touch mystified... your text came out as HelloWorld!, no spaces, right? Correct. The reason is, the parser does not try and guess where you want spaces; if you want spaces, you must put them in explicitly yourself.

For practice, exit the script editor using the Cancel button and do NOT save the script (you want to abandon changes). Then start a new script called "TextChunk" (no quotes) again, since the last one was not saved.

This time, when you type in the first piece of text, type "Hello " (no quotes, but note the space at the end). Then do "World!" as before, no spaces. We've now put a space after Hello, so it should not come out crunched up. Go ahead and insert the EOL, and check it's working.

Fine so far? Have a go at inserting a bunch of new bits of text, followed by an EOL for each physical line you want, and familiarise yourself with how this works. When you're done, you can either save or discard your script; the choice is yours. Either way exit the editor and start a new script called "Paragraph" (no quotes), so that we start with a blank script.
You might be wondering what happens if the text chunks, when put together, spill over the edge of the paper. Let's try it. In this new blank script, enter the following in a TEXT command (cut and paste from here into the box on the control panel if you know how, it's easier):

"This is a rather long piece of text that should extend well beyond the normal single line we are used to. It will be used to examine how the paragraph attribute works." (no quotes).

Now enter another TEXT command, say "Hello", and an EOL. You should see just one physical line, and it just disappears off the right edge. The Hello is bundled right up alongside it, but you just can't see it - but you expected that, since we didn't issue an EOL between the two TEXT commands. Since it shows the text going off the edge of the page on-screen, that is what it will do in the proper report and on the printer. Not very useful, is it? You could, of course, break it down manually into chunks and issue EOLs after each one to split it over lines, but that is largely down to trial and error where to split the lines, and it's quite tedious.

Repeat exactly the same pair of TEXT commands and EOL, but this time on the first TEXT command only, switch the paragraph option on before clicking Insert. Leave it off for the second TEXT command. Now how does it look? You should notice three things: first, the text automatically wrapped onto the next line at the correct place (don't be fooled by the gridlines - it breaks so that the last word doesn't extend beyond the paper margin, but that's not necessarily where the last gridline is). Second, the Hello now appears on a new line automatically. Third, the TEXT command is actually a PARAGRAPH command. The PARAGRAPH command is actually a special form of the TEXT command which automatically wraps long lines of text as necessary. Because it is a paragraph, it is implicit that it is a complete TEXT command and is thus automatically followed by an EOL.

OK or Cancel this script, and start a new script called "Dates" (no quotes)..

 

4. It's A Date, Then
(Starts from the new "Dates" script)

From the control panel, click Insert Date. Select Short Date and you will probably have no choice but Today's Date on the second row. Click Insert. Now insert an EOL command.

Look familiar? The DATE command inserts either today's date, or the start or end date of the range chosen on the custom reports maintenance page before you came into the script editor, and formats it according to your choice. The actual command inserted into the script won't be DATE, it will be something of the form xxxDATE_yyy. xxx will be SHORT, LONG or CUSTOM, corresponding to the formatting, and yyy will be TODAY, STARTDATE or ENDDATE indicating which date is being displayed.

(Actual date format may differ from yours, since it matches whatever is normal for your locale - this is an example of UK format)

In order to use the start or end dates of the date range used in the scripting, you would need to use specific date limits rather than All Entries on the outer page.

Note that the DATE commands require an EOL to be rendered (drawn). This is because they are simply TEXT chunks, we just use a special command to get a certain date into text form instead of supplying the text literally. Since it is nothing more than a TEXT command, everything you can do with TEXT you can do with these. You will learn exactly what you can do with TEXT if you read on.

OK or Cancel this script and start a new script called "TextAttribs" (no quotes - attribs is short for attributes, if you're wondering).

 

5. Oh, I'm Blushing!
(Starts from the new "TextAttribs" script)

Before entering our next piece of text, let's explore something new: colour. The colour text is written in is considered a font option, so click Font Change. A number of controls come up, but for now we're only interested in the one labelled Colour. Choose Light Red, then click Insert.

Nothing rendered so far, right? Phew!

Now insert a TEXT command and EOL as we've learned above, using "Hello, RED World!" as the text (insert it in a single TEXT block for now). If you've got your EOL in correctly, you should now see your glorious text in light red (colourblind users may wish to choose a substitute colour and imagine it's red, since people don't usually blush cyan!).

Is that cool or what?

Not impressed, huh?

 

6. This Rainbow Has No Gold At The End
(Continues on from the "TextAttribs" script - requires you to follow the previous section first)

For each of the following words, click Font Change, choose the specified colour, click Insert. Click Insert Text, enter the word (without quotes), click Insert. When you've done all the words, click Insert End Of Line and click Insert. Be careful to note where there are spaces and where there are not.

"Hello, " (Dark Green)
"Multi-" (Light Red)
"Coloured " (Orange)
"World!" (Light Blue)

Now tell me that doesn't get you excited! (You at the back there - stop shaking your head!)

This is one of the reasons why we have to tell the parser explicitly where the line ends. If we did not, because we would have to put all text in one TEXT chunk, we couldn't change colours for some of the words in the sentence. Not very flexible. With explicit end of line, we can mix and match as many of these chunks as we can fit in the width of the page, and you can thus dazzle all your friends with a report or newsletter that looks like a Dulux paint colour swatch.

On the downside, though, if you want to use the PARAGRAPH command instead of the TEXT command to get long lines wrapping, then you can't mix and match. This is a limitation of how PARAGRAPH works, one that we may overcome later, but for now the restriction is there.

Rather than start a new script next, we'll start building up a whole page. Exit the editor with OK to save your script. Then, rather than start a new script, select "TextAttribs" and click Modify (or double-click "TextAttribs" in the list) to bring our script back up.

Notice that it renders immediately, since the EOL is in the script already. (If something went wrong and you ended up with a blank script now, don't panic; it really doesn't matter, and it WILL get better with practice. Just select "TextAttribs" from the list of scripts (if it's there) and click Delete, and then start a new script called "TextAttribs" (no quotes) again.)

 

7. The Font OF All Knowledge
(Continues on from the "TextAttribs" script - requires you to follow the previous section first)

Insert a COLOUR command (Font Change) to get you back to black. Insert a TEXT command to put "Hello, " (no quotes). Do not insert an EOL yet.

When you went to pick the colour, you no doubt recognised something else; the font name, hopefully Arial. Insert a new Font Change, and this time change the font to Courier (or Courier New) instead of changing the colour. Don't forget to click Insert to get the FONT command into the script.

Now insert a TEXT command saying "Courier " (no quotes). Insert another FONT command to put you back to whatever you started at (Arial will be fine if you can't remember), then insert a third TEXT command saying "World!", and finally insert an EOL. It should now render.

What you expected? You should see the phrase "Hello, Courier World!", but with the word Courier actually in the Courier font (the rest should be in Arial). You can mix and match font name changes just like you can mix colour changes. Go ahead and repeat the sentence, this time changing the font size as well as its name. You probably wouldn't mix font sizes on the same line, but you can if you want. Try one more sentence, this time changing to italic or underline (or both, if the fancy takes you) in the font change.

Like it?

 

8. Queue To The Right, Please
(Continues on from the "TextAttribs" script - requires you to follow the previous section first)

What if you wanted to write your address on the right at the top, as in traditional letter writing (at least it's traditional in English, it may be different for other cultures but let's imagine it's valid in your region)? So far, you may have noticed that everything came out to the left. You probably also noticed the Alignment controls in amongst the Font Change settings.

Continuing on from your existing script, insert a font change but this time choose Right from the Alignment section (leave colours and font as they are for now; if you have a rather large font size, insert a separate font change before this one to correct it).

Insert the phrase of your choice, including the EOL.

How does it look? Aligned as you expected, to the right of the page? Good.

Now something more tricky: Insert a Left ALIGN command (using Font Change), then insert a TEXT command using "Hello". Insert a Centre ALIGN command, then a TEXT command using "Again". Insert a Right ALIGN command, and insert a TEXT command using "World" followed by an EOL.

Rendered how you expected? Nope. Did you do anything wrong? Nope. Is it because the text has no spaces, so it can't separate them? Nope (go ahead and do it with spaces, if you want to prove it). It's because the ALIGN command has a restriction; like the alignment buttons in a Word document, it affects the whole line. Even though you can apparently insert an ALIGN command between each TEXT chunk, only the last one issued before EOL takes effect. Thus, because we issued a Right ALIGN command last, that's the way it aligns.

Technically it IS possible to get multiple alignments on the same physical line, but that uses a different feature. The Alignment available in the Font Change section (the ALIGN script command) works exactly like Word's alignment buttons, so try to remember that. You would normally simply issue one before each line of text you build up.

Note that the PARAGRAPH command introduced above also obeys line alignment - in this case the whole wrapped paragraph is aligned to the left, to the right, or centrally.

 

9. I've Got A Memory Like An Elephant
(Continues on from the "TextAttribs" script - requires you to follow the previous section first)

Continuing on from your current script, insert a new TEXT command, anything you like, and insert the required EOL.

Did it come out the way you expected? Some of you are probably nodding, some are probably shaking their heads (some are probably staring wild-eyed at the screen, wondering what this is all about).

If you have been following instructions in sequence and carefully, it should have come out right-aligned again. But we didn't issue a Right ALIGN command this time! What gives?

The parser remembered the current state, as the previous script commands left it. This is fairly important to remember: a change in text state (colour, font, alignment, effects, banner, highlight, size) continues indefinitely until you explicitly change it back. This saves you constantly issuing commands to get a block of lines in the same state, which would be tedious and would generate a cluttered script.

Continuing with this script, try inserting another Right ALIGN command. When you click Insert, observe the script. It didn't put in "ALIGN RIGHT", did it? Why not?

Because we are already right-aligned; it's still in effect from when we last set alignment, as proved by the previous line going to the right of its own accord. The editor / parser will NOT insert a script command if it has no effect on the current state, since that too would be pointless and cluttered. This one may take some time to get used to, but you will understand how useful it is eventually.

 

10. May I Squeeze In There?
(Requires any existing script that has some commands in it)

Undoubtedly a good percentage of you will by now have made a mistake and had to exit the editor and come back. It's not this difficult in reality; we're just trying to avoid bombarding you with lots of techniques. But soon we will need to do insertions into the script, so perhaps now is the best time to do a little fancy footwork in the script box.

In the box to the right you should see a solid bar across the box, below all of your script. This will be the same colour as you have set your system's highlight colour to (or what it is by default). It indicates where the next script command will go. Thus, since so far it has always been beyond any existing script, all new commands have gone on the end of our script, as intended.

Left-click an existing line of script once. Notice how the bar moves up to and highlights that line. If we were to click Insert from the control panel now, the command would go where the highlight is. In other words, what's there now would shift down one line to make room for the command being inserted. Thus, commands are inserted at the highlighted line. The highlight always moves just beyond the command just inserted. If you've finished making corrections and want to add new lines to the end of the script, you would simply click the blank line below your script to put the highlight there, and off we go.

Try it now: click on the blank row beyond any script (you should only be able to click one or two rows below the last used line) so that the highlight moves there (if it doesn't move, you clicked too far down). Then Insert an EOL, to prove that it appears at the highlight and the highlight moves to the next free line.

Wait... we just inserted an EOL with no TEXT? Correct. An EOL with no text in front has no effect, but WILL be inserted (an EOL does not change the state of the text - it marks the end of it).

Go ahead, insert a TEXT command and EOL command below that and confirm that the text appears right beneath the previous text on the rendered output.

 

11. Move Over, Please
(Starts a new "MoreTextAttribs" script)

There are various reasons for not wanting your rendered output crammed up against the left or right margins all the time, or even all centred. Indentation looks nice, for one thing, and columns of data are also common. Anyone who has tried using spaces to achieve this has no doubt found just how many need to go in when using a variable-pitch font, since spaces are very small, and have also probably found that it isn't very reliable.

Most editors (including Word) have tab support. You press the <Tab> key, and the cursor moves to the next tab stop, giving simple and effective alignment away from the margins. Our script editor also supports tabs; you just have to know how to get them to appear, since we don't edit the output directly like in Word.

Those grid lines on the page? They're tabstop positions. Hover your mouse between two of those, and leave the mouse there for a couple of seconds. As long as your mouse isn't 'shivering' (an effect caused by a dirty mouse roller or a bad surface under an optical mouse), you should see a tooltip which tells you what tabstop number to use. This number refers to the line to the left of your current position. If you're almost on a grid line with the mouse pointer, be careful you don't confuse which side it's talking about. It's always best to hover just to the right of a grid line to be sure. To assist, there is a ruler along the bottom of the script editor which numbers every other tab stop permanently.

Let's do some column work. Start a brand new one called "MoreTextAttribs" (no quotes).

Insert a TEXT command using "Short" (no quotes). Now click Insert Tab. Use the left / right arrows to change that to 5, and make sure Absolute is switched on. Leave the default of Left for now, since this is the most common form of tab. Now click Insert to put the TAB command into the script. Don't worry about how the parameter looks, we'll explain that later. Now insert a second TEXT command using "Column A" (no quotes). Now insert another TAB command, this time with a value of 15 (still absolute). Insert another TEXT command using "Column B" (no quotes), and finally insert the EOL.

Repeat all of the above TEXT and TAB commands, but using "Longer text", "Column A also" and "Column B also" as the pieces of text (all no quotes). Don't forget the EOL.

See how they line up, despite the change in text length at each column?

Find the grid line that sits exactly on the left edge of Column A. Hover your mouse just to its right below the rows of text, and satisfy yourself that it says Tab position 5. Repeat for the line on the left edge of Column B (hovering just to its right), and confirm it says Tab position 15.

What's happening here is that for absolute tabs, when you specify the tab number, it means to line up against that exact tab position. Hence, since we told it positions 5 and 15, those are the positions it lined up against.

We're now going to edit some commands for practice, rather than enter them all again. Double-click on the line that says TAB =5>. It will appear in the control panel with all parameters set as you had them when you inserted it. Notice that the button that normally says Insert at the bottom now says Update. Notice also that the buttons down the left are now disabled. This means we must either update or cancel this line edit before we can do anything else.

Switch Absolute off, and click Update. The command should now still be in the same place in the box on the right, but should have changed to TAB *5. Repeat this with all other TAB commands in the script, and then observe the output.

Notice how the columns are now not lined up properly, and in fact what it is doing is spacing each text chunk from its previous chunk by a certain amount. Thus, the longer text lengths on the second line means it spreads further along the line. These are relative tabs. It means that the renderer moves along the number of tabstops you specify from the previous chunk before it writes the next chunk; that explains why the column B items are much further away, because we told it to move 15 tabstops from the last position instead of just 5 for column A items. Think of it as you pressing the <Tab> key that many times to move across the page. Making sense? If not, don't panic; you probably won't have much use for relative tabs, so stick to absolute ones and you'll be fine.

Go back and edit all the tab commands to make them all absolute again, and confirm you have got your aligned columns back. Now, on to our final trick in this section.

We're going to make our first insertion into the script. Move the highlight bar to the line that says TEXT "Short" - i.e., the very first line of this latest script we started.

Insert an ALIGN command (Font Change), and select Right alignment. Now look at your output. Aaarrrggghhh!!! What happened? It looks like what happened when you tried to mix ALIGN commands on the same physical line, doesn't it? What's going on?

The answer is simple: tabs ONLY work on lines that are left-aligned. For lines that are right- or centre-aligned, tabs have no effect and are effectively not even there. (For those of you keeping track (I was tempted to say 'keeping tabs' there) of what our script editor can do vs. Word, you've got your first major difference - Word CAN do tabs on right-aligned lines, we can't). Think about it: you've told the text to line up on the left with tab position X, but you've also told it to line up with the right margin... what should it do with that conflicting information? Relative tabs might be different, but it's just not worth it: if you want to use tabs, keep line alignment to the left. Simple as that.

 

12. You Move Over Here, Queue To The Left; You Move Over There, Queue To The Right
(Starts a new "EvenMoreTextAttribs" script)

We promised earlier that it was possible to achieve mixed alignments on the same line. We didn't lie when we said ALIGN can't do it; that is absolutely correct (not relatively!). But if you haven't guessed by now, these tab thingies are a bit more sophisticated, and they can do the job.

Start a new script to ensure we all start from the same setup. Call it "EvenMoreTextAttribs" (no quotes).

Insert a TAB command, absolute, value of 15, with Right alignment on it. Insert a TEXT command using "Butcher, male" (no quotes). Insert a TAB command, absolute, value of 25, with Left alignment. Insert a TEXT command using "Butcher, female" (no quotes). Insert an EOL.

Repeat the above physical output line twice more, using "Baker" instead of "Butcher" in the TEXT commands for the second line and "Candlestick maker" instead of "Butcher" or "Baker" for the third line.

You should end up with two distinct columns, the males on the left sprawling out to the left but lined neatly near the centre of the page, and the females to the right, sprawling out to the right but aligned neatly near the centre of the page.

See? Two different alignments on the same physical line. We're nothing if not cunning.

To explain: for each of the males, we inserted a TAB with Right alignment. This means that all TEXT commands issued following this (up to the next TAB command or EOL) will be chained together in the normal left-to-right sequence and then the whole bunch aligned with their right edge on the specified tab position. Right alignment = right edge all lined up.

For the females, we inserted a TAB with Left alignment. Thus, the bunch of TEXTs following (up to the next TAB or EOL) all got aligned with their left edge on the specified tab position. Left alignment = left edge all lined up.

If we had used Centre alignment, the bunch of TEXTs following (up to the next TAB or EOL) would get aligned with their centres on the specified tab position.

Notice that in both cases, the text sprawls away from the tab position in the opposite direction, to always ensure the correct edge is lined up with the grid line. This is going to be confusing for a while, but once you get used to it we believe you will find it powerful. Remember the weird TAB =5> ? The arrowhead in that shows which way the text will sprawl from the tab position.

This is probably giving you a headache; take a break - there are probably more headaches to come (sorry!).

 

13. Is My Back A Funny Colour?
(Starts a new "Banners" script)

Ever used a highlighter pen to mark a word on a page? They're very handy for attracting the eye to that word. We can do that too; start a new script called "Banners" (no quotes) and we'll show you how.

Insert a TEXT command using "Some " (no quotes). Insert a font change, but instead of changing the entry in the Colour box, change it in the Highlight box - to Yellow. Leave everything else the same, and click Insert. Insert another TEXT command using "highlighted " (no quotes, but note the spaces in this one and the previous). Insert another HIGHLIGHT command, putting it back to White, the colour of the page (the page is always assumed to be White). Insert a third TEXT command using "text." (no quotes) and insert the EOL. Observe your masterpiece.

Cool, huh?

Notice that the yellow highlight also highlighted the space after "highlighted". This is correct - a HIGHLIGHT colours the background of all text in the following TEXT commands until further notice. Thus, if you don't want this odd effect, you would arrange for the space to go on the TEXT command following the HIGHLIGHT WHITE command instead.

Repeat the exact same set of commands above to produce a second identical line. Notice that there is no gap between the highlighted portions from one line to the next. This is single linespacing. If you want to increase the spacing between lines (just like on a typewriter), you can click Linespace Change. This inserts a LINESPACING command in the script. This is something else which is remembered until you explicitly switch it back. The linespacing change affects the very next EOL encountered; thus, it must go before an EOL command (but technically, doesn't have to be before TEXT commands - but it's good practice to show intention by putting it in front of any TEXT commands you want on the next line to have that spacing).

Let's do this; the reason will become apparent very shortly. Move to the very top of your script and insert a 1.5 LINESPACING command. Notice that now, there is a gap between highlights. HIGHLIGHT only colours the background of text and spaces; it doesn't fill in the gaps.

Hmmm... so what if we wanted to have a nice title block, all shaded in a colour, no gaps? We could certainly put in tons of spaces to get the highlight to stretch across the page, but it wouldn't fill those linespacing gaps.

I'll bet you a pound to a penny one of you has already gotten brave and tried BANNER at this point. If you have, well done. But don't charge too far ahead, you'll get yourself in a pickle {:v)

Try it now. After the LINESPACING command but before your first TEXT command in your script, insert a BANNER command (font change), choosing Light Red. While you're at it (you can do this either in the same font change or as a separate one, no difference), change (line) alignment to Centre so our "Some highlighted text." appears in the middle. This should also be inserted before the first TEXT command.

Impressed? BANNER not only fills the entire line behind anything not covered by text or spaces, but it also fills the gaps between lines, giving a nice, solid background. Notice that it does not colour behind the text - that's HIGHLIGHT's domain, and there's no crossover. HIGHLIGHT for text and spaces, BANNER for gaps. Let's complete the picture since it probably still looks a bit odd.

Using the control panel, insert a Blank Line (value 1) before the first text but after the BANNER. We'll let you figure out how to do that - you should be getting an expert by now! Insert another (value 1) right at the end of your script (yes, after both EOLs - BLANK does not require an EOL, it's implicit that you want a whole blank line).

Go back to the beginning of your text (after the first BLANK, for example) and insert a font change to change the normal text colour to White and at the same time, the highlight to Light Red (same as the banner).

Now it probably looks a bit funny; we're going to do our first deletion from the script. Highlight the first line that says HIGHLIGHT YELLOW (by left-clicking on it once) and then press <Delete>. The line should be removed from the script and the script shuffled up to absorb it. Delete the remaining HIGHLIGHT lines from there onwards (but leave the HIGHLIGHT LIGHT_RED you just inserted).

What you should now be left with is a nice red banner with white writing on it.

Blocks of colour are one way to make a section nice and clear, but there is another very common way; a separator. As a quick demonstration here, move the insert point to the end of your script (next blank line in the box). Insert a font change to change text colour back to Black, banner and highlight colour back to White. Now use the control panel to insert a Separator, value 100%, your choice of style. Simple, huh? For reference, separators don't need an EOL either; they always sit on a line of their own, and always have the same gap above and below them - thus, they are also unaffected by linespacing.

 

14. I'm Going Loopy Here!
(Starts a new "Database" script)

All this flowery stuff is all very well, but we haven't touched a single thing in our database yet. It's not going to be much of a report if it doesn't contain any real and pertinent data. We will now cast aside all the glitter and get down to some real report scripting. Start a new script called "Database" (no quotes).

As some of you may know, a database has rows and fields. A row is self-explanatory; a row represents all the data for a single talk, or a single congregation, or a single speaker, depending on which table you are looking at. Yes, databases also contain tables; think of a database as a library, and each table as a bookcase in that library. A bookcase has shelves (rows), and each row has lots of books (fields) on it, and we should be able to pick out any field from that row. Sound fine?

On the control panel, click Insert DB Field. It produces an error message. Why?

The problem is, we haven't told the parser what table we're using, and we haven't told it what row(s) to work from. Consequently, it has no clue about what fields there should be right now. So logic tells us we have to do something before we try and select fields.

We could, if we wanted, pick out every single row one by one with separate commands, and work that way. It would be perfectly valid, but also an extremely long script since there could be hundreds of rows to work from in any one table. Furthermore, we don't know how many rows there are altogether, so how would we know how many times to write that bunch of script lines?

What we need to do is tell the parser to walk all the rows in the database, and then we'll issue commands to extract fields from each row it gives us. Thus, the script engine has a LOOP command.

Click Insert DB Loop. This one also pops up a window, and the list should contain something at the top of the window. These are your available tables in the database. Select "Away Talks". The controls below this list are irrelevant for now; we'll ignore them. To the right, switch off Sort rows (we'll leave them unsorted), and switch off Ignore date range. Click Insert. Notice how it not only puts the initial LOOP command in, but also a matching END_LOOP. We have to tell the parser when we get to the end of the things we want to do for each row, otherwise it wouldn't know when we wanted it to complete the loop and do non-loop stuff again.

This is the simplest loop you can write; it simply iterates every single row in the table without prejudice, and if you put some script within the loop, you could start actually extracting data from those rows. At the moment, the output shows nothing; that's because we have no script inside the loop, so it does absolutely nothing with each row it is given. So let's do something.

Highlight the END_LOOP command (since we want to insert commands at that point, so that they go before the end of the loop). NOW click Insert DB Field, and this time note that there are fields to choose from. How does it know this now? Because the parser looks to see at what point in the script you're extracting fields, and so can work out which loop is currently in effect. Since we're inside our simple loop now, it knows it's dealing with the Away Talks table and so can produce the correct fields.
Choose Talk Date from the list, and click Insert..

Still nothing, huh? Quite right. Insert an EOL after that command (and still before END_LOOP), and all will be revealed.

 

15. Fields Of Golden... Text
(Continues on from the "Database" script - requires you to follow the previous section first)

Notice how we had to issue an EOL after this FIELD, just like we do with TEXT? There's a good reason for that. The result of a FIELD command is text, just like the result of a TEXT command or even one of the DATE commands. Thus, just like a TEXT command, we can do alignments, tabs, colours, fonts, highlights... anything we can do with TEXT, we can also do with FIELD. The difference is that the parameter to FIELD is not the actual text to display, but the name of a database field for the current row, and the text extracted from that field is what is displayed. This is the key to getting data out of a database: using FIELD commands to replace literal text with database contents.

Notice I said current row. We already know that the LOOP command runs through all rows and gives them to us one at a time, and everything inside that LOOP / END_LOOP pair gets done for every single one of those rows. If a row was omitted from the loop somehow, then clearly those commands shouldn't be done on that missing row.

Since a FIELD is just a TEXT, we can happily mix TEXT and FIELD commands in the same physical line of output to build up our report. This is the remaining reason why we have this need to specify where the end of the physical line is; as well as possibly needing to mix colours, we will need to mix literal text with database contents without having to have broken lines everywhere.

Let's check that: highlight the FIELD command for insertion before it, and insert a TEXT command using "This row's talk date is: " (no quotes). You should see this echoed on every single line in front of the dates now.

It is important to try and get into a mindset: when you're looking at commands within a loop, remember that this is operating on a specific row at any one time, the current row. This will hopefully help later, when we get more complex loops.

 

16. Man, That Looks Weird
(Continues on from the "Database" script - requires you to follow the previous section first)

Don't those dates from above look a bit funny? Not exactly a format you're used to seeing dates in. You may have noticed on the FIELD window that you could choose the format of the field. Go back to the FIELD command and double-click it to edit it. Click the Long Date option, and click Update. Look better now?

Don't get this date confused with the one we saw near the beginning of this tutorial. That date was taken from your system clock (today), or from the range you specified before opening the script; this date is taken from the database, and is the actual date stored in the table.

But whoa! Our FIELD command just changed to LONGDATE_FIELD! Don't panic; it's still a FIELD, it's just a special one to help the parser know what to do with your field. Don't let the fancy name put you off; it still behaves exactly like FIELD, except for how it formats the chunk of text. Similarly the other formats produce their own variations. CUSTOMDATE_FIELD's special formatting parameter is covered elsewhere in this help system, so it won't be covered here. Notice how these adaptations of the FIELD command are like the DATE command changing depending on formatting and source.
Want to know what Talk theme name is for? Edit the command, choose Talk Number as the field instead of Talk Date, and select Don't interpret to begin with. Update it. (If it really bothers you, change the literal text command before that command to say "This row's talk is: " instead.) A nice set of talk numbers, eh?

Now try it again, but format as a Talk theme name. Update it. Woohoo! It translated our talk numbers into proper talk names for us! The parser holds the list of defined talk numbers and their corresponding titles (names), as defined on the maintenance dialogue - thus, it can translate for us.

 

17. Inner Peace, Inner Loop
(Starts a new "MoreDatabase" script)

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 Reports tab of the maintenance dialogue before you open the script; 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), and insert a LOOP command on the "Chairmen" table, with Ignore date range and Sort by switched off. Within this loop, insert a FIELD name on the "Chairman" field, no interpretation (of course), and an EOL to end the line for each row. You should end up with a list of chairmen (assuming you have added the names of your chairmen into the database).

As an example, we will now modify the LOOP by adding criteria to omit one of the names listed.  So choose one of those names that appears.

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, but for now, choose "Chairman" in the top left, "MUST NOT BE" in the top right, and your chosen name exactly as it appears on the output, including punctuation in the bottom left. Now try clicking Update (far right of window) immediately. Notice how it warned you that you changed the criteria, but did not add that criteria; all we did was change some controls, we didn't say "use this criteria". Lucky it warned us!  So click No to this warning message.

Click the Add New that appears above the criteria controls, and you should now see your criteria entered into the box, in a slightly different form but recognisable. NOW click Update, and observe your output.

See how that name is now not there? Any rows that contain that name in the Chairman field 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.

So we have a list of chairmen... now what? What if we want to print out the dates of all assignments that this chairman is earmarked for? For each row we get (thus, each chairman), we want to examine another table and produce not one but a list of items for that chairman; when that list is done, we go back and get another chairman.

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: before we try this, remember that it is possible that not all chairmen in your Chairmen 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 chairmen 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), and insert another LOOP. Make this one on the "Home Talks" table, no criteria for now, switch off Ignore date range and Sort by, and click Insert. Into this loop, insert a TAB command, absolute, value 6, then a FIELD command on the "Last Given" field (format as you wish), and OK it. Don't forget to tie it off with an EOL.

(Yes, you would have seen the display go weird before you put that last EOL in; this is because the script doesn't encounter an EOL in the inner loop, so once it exits that inner loop and goes to the next row of the outer loop, it finds the EOL from our chairman FIELD... so it thinks THAT is the end of the line. You must put in that EOL in the inner loop to avoid this weird effect!)

What We're Trying To Achieve

(Actual names are fake for privacy reasons)

(Actual names blurred for privacy reasons)

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

Edit your inner loop, and add criteria as follows: Chairman (top-left), MUST BE (top right), and the first name listed on the output since that's the chairman for the row (bottom left). Add the new criteria, then Update.

(Actual names blurred for privacy reasons)

Now, if alarm bells haven't already started ringing, you may need to think about that one. From the new output you most likely won't 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. Hmmm...

Cast your mind back to the emphasis I 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. Select the criteria you added, since we need to update it somehow. Look at a couple of the controls we haven't mentioned so far (ignoring Remove and Update!). Current row, and Use variables. We're not doing variables here; that's beyond the scope of this tutorial. 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 (trust me, it does). 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 Chairman in the bottom-left list, click the Update button just above these controls (not to be confused with the Update on the edge of the window, which updates the LOOP command back in the script). This changes our saved criteria to use our new information, and NOW we can click the other Update to put the new LOOP back in the script.


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

We do realise that these last couple of sections are quite daunting, and we don't expect you to suddenly be able to generate 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.

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

If you have got this far and everything is making good sense to you then... what are you waiting for?! Go and read Advanced Scripting Features!