Custom Reports

Scripting Tutorial - Variables

This is an advanced topic in the list of tutorials for custom script writing - if you have mastered the basics of writing a script, then this will explain in detail about using variables to achieve more flexible scripts. If you have not mastered basic scripting yet, you are advised to read How To Write Scriptsfirst; otherwise, this will make no sense whatsoever.

1. What Is A Variable?

In very broad terms, a variable is something that does not have a fixed value; its value is variable from one moment to another. The interest rate on your mortgage, for example, is a variable; it changes during the term of your mortgage.

In broad programming terms, a variable is a location in memory that can hold any value it wants; it starts by being given an initial value, and later in the program it can get a new value. When you want to use that value, you make a reference to the variable by its name, and you are given whatever contents it currently has. This is extremely useful, since a variable can increase each time round a loop and allow you to pick out different records in a set; or it can be used to get information from the user of the program - information the program could never guess - and use it to alter how it behaves later.

In specific Public Talks Schedule scripting terms, a variable is a location in the scripting engine's memory that holds a piece of information that YOU, the person generating the script, give it. In this particular case it never changes value until you generate a new script; but the very fact that the information comes from you and can be anything you want, makes it a variable.

So what does a variable look like? Well, in PTS scripting we always use a dollar sign ($) to indicate a variable. It is any word you like with the dollar sign in front. Thus, $MyName, $speakerName and $Chairman are all valid variable names. Note that, unlike in real programming, a variable name with an underscore in it is NOT acceptable, and won't work. Thus, $speaker_name is invalid. When you insert the variable request, it will not be seen as an error, but the parser will not understand when you try and reference it. Thus, a variable name in PTS must always consist of letters only - if you mix lowercase and uppercase that's fine, as long as you use the exact same combination both when you request the variable and when you try and use it. You might find it odd, but a variable name made of numbers IS legal, but of little help.

There are some special variables that you cannot declare yourself (but of course, you can use them). If you try to put any of them in a REQUIRES script command, it will be coloured as an error and will NOT appear in the small variables window where you enter values for variables. If you do inadvertently do this, you will have to delete the line and enter it anew; error lines automatically become comments and cannot be changed to anything afterwards (only deleted and something new inserted in its place).
This is a special variable because its value is already filled out - it refers to whatever congregation you have marked as your local on the Congregations maintenance page (the Congregations page is a page on the maintenance sheet, a different page from the Custom Reports page that you used to get to the script editor). If you have not filled out this bit of information, then the $LocalCong variable will default the value to "Local" which will likely produce unexpected results.
(Actual congregation names blurred for privacy reasons)
(today - 7 days)
$LastMonth (today - 31 days)

(today + 7 days)
(today + 31 days)
$NextSixMonths (today + 183 days)

(today + 365 days)
$NextTwoYears (today + 731 days)

(today + 91 days)

These are also special variables which have their values already filled out. They all contain the appropriate date in a special format: #YYYY MM DD#. These date variables are designed to be used with LOOP and IF custom report script commands. Specifically, they should be used with any database fields of type date (that is - TIMESTAMP).


2. Where Are Variables Used?

There are three main places where variables are used: TEXT, PARAGRAPH and LOOP commands. TEXT and PARAGRAPH are, of course, more or less the same thing, they just are rendered differently. Here, the variable is simply used to write a piece of text into your report that you don't know beforehand. For example, if your report was listing a Brother's talks for the coming month, then a variable you would need would be to enter the Brother's name so that you could generate a report for each Brother in your congregation. In which case, you might wish to have the report start with "Dear XXX". The XXX is where the variable would come into play.

Since a LOOP command in itself does not print anything out, it is clear that the variable has a different role to play here. Do you remember trying to extract chairmen by using CURRENT."Chairmen" to ensure it extracted the chairmen as given by the outer loop? Well, if you used a variableto say which chairmen instead of whatever the outer row gave us, then we could extract all data for the chairman YOU specify, instead.

This may not make a lot of sense right now, so bear with us. We will come to some real-world examples shortly.


3. How Does The Parser Handle Them?

Whenever the parser comes across a word beginning with a dollar sign, it assumes it is a variable and tries to make sense of it. It extracts the word immediately following the dollar sign, and then tries to look up that combination to see if such a variable exists. If it does, it then grabs its current value, and THAT is rendered on the output instead of the variable's name. Thus, it substitutes the variable's value in place of the variable's name in the script (internally), and then uses the result as normal.

Notice that since the value is substituted in place of the variable name, the variable reference is embedded directly into the TEXT (or PARAGRAPH) command. It is not possible to use a variable for rendering simple text without embedding it like this. Even if you wanted to change colours or font and thus needed the variable on a raw script line of its own, it must still be part of a TEXT or PARAGRAPH command and within speech marks.

Talking of embedding and speech marks, what happens if you want to include speech marks in your piece of text? If you try simply putting them directly into the piece of text you insert via the control panel, you will be disappointed. The parser will get confused, will terminate the piece of text at the extra speech marks and simply ignore everything else on the line.

To insert a speech mark within your text, use the special &speech item. It works a like a variable, but it is NOT a variable; it is a special token. The script will look rather funny with the special speech item in it due to the & part, so we advise you to write the sentence out without the speech marks to begin with, then go back and put them in afterwards.

What if you want a dollar sign in the script? Even though the dollar sign is used to indicate a variable, it is possible to get it in a script literally. Depending on how you want it, there are ways: if you want a dollar sign on its own with spaces either side, simply write a single dollar sign on its own in the raw script. The reason this works is due to how the parser handles various 'wrong' combinations of variables.

If a dollar sign is followed by a space, it is not interpreted as a variable and is just drawn as is. This is why the single dollar sign works as above; it is followed by a space, and so is shown literally.

If a dollar sign is followed by an illegal variable name, or a legal variable name but for a variable that just doesn't exist, then the dollar sign AND the variable name will be shown as is. This is a good indication of whether you have got the variable name wrong, or have not declared it; if the variable name comes out in the rendered output complete with dollar sign, you made a tiny mistake that needs fixing.

If a dollar sign is followed immediately by another, the first one is always printed out literally, and the second one is parsed using normal variable rules.

Since numbers are legal in variable names, beware of trying to show monetary values in a script. If you use $50 in script, this WILL come out unless you happen to have a variable called $50, in which case it will substitute its value as normal. In that specific case, using two dollar signs together will make the extra dollar sign appear. Rule of thumb: don't make variable names out of pure numbers!


4. Why Do We Have To Declare Variables?

As mentioned earlier, when the parser encounters a dollar sign it tries to look up the variable in a list. What list? Well, the list of variables that it knows the script needs. This is the same list it would have to present to you to allow you to fill in the values. How does it get this list?

It could be argued that the parser could run through every line of the script and for any references it finds, add them to the list. But there are flaws to this approach.

First, if it only had the names of the variables to go on, when it wanted to ask you for the values, it would simply have to use those names; by having variable declarations, you get to put in a nice piece of informative text that only you will see when you generate the report, that makes it clear what the variable you are being asked for will be used for. Without this, you'd have to choose names carefully to make sense.

Second, what happens if you made a mistake and used the wrong variable name in the script? If it saw a different spelling, it would simply assume it was a different variable and list it. You'd be asked for an extra variable! By declaring the variables beforehand, it can spot a mis-spelt variable name and throw it out (by rendering it directly to the output as the variable name with dollar sign).

So we declare the variable in advance, and let the parser know exactly what it should be expecting. To insert a variable declaration you use the Variable Request button on the control panel. You type in the name you want to give your variable (without the dollar sign), and the text you want to use as a prompt when entering a value for the variable. This then inserts a REQUIRES command, as shown in the previous section.

Note that the REQUIRES command does NOT have to come at the top of the script; it will happily work even if it occurs at the end of the script, after you've referenced the variable. The script is actually pre-parsed and so the list is built up before it tries to render anything; it thus already knows what the variables are by the time it tries to render the raw script.

Whenever your script is altered and it contains a variable that has not yet been given a value, the Variables window pops up. This is not to annoy you; it is to help make sure you don't forget to fill in values. Note that in script editor mode, the values you give variables are for testing purposes only, to check if you have written your script correctly. These values are NOT remembered once you close the script. When you generate a real report from one of your custom scripts, you enter the real values of the variables then, and these ARE remembered when you save the report (actually, the report output is saved verbatim, so the values of the variables are permanently embedded in the text, having been substituted).


5. Real-World Samples

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


And what are those other odd bits?

NODATERANGE is a special word that is used only in loops. It tells the parser to ignore the date range we specified on the outside page of the maintenance window. 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 on the outside of the script? 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.

Finally, what's up with the "-1"? Well, in that table the field called "AwaySpeaker" is a booleanfield - 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, please use the contact page.