Expression Editor

Expression Editor

The Expression Editor allows you to build more complex values that involve variables and mathematical / string operations to combine them. Such expressions can then be used as:

Operators (the symbols that combine variables and literal values) do not have any kind of priority over each other unlike in most programming languages, so they will be evaluated in the order they are given. To overcome this, you *must* use normal brackets ( and ) to group together parts that must be evaluated first. The expression parser will work these parts out and replace them before continuing with the rest of the expression.

There are a number of places where expressions you have created will be surrounded by curly braces { and }. These are required in certain places (such as TEXT and LOOP) to indicate that what follows must be evaluated and replaced before trying to obey the command. Where an expression is expected (such as in a TAB command or in a variable assignment), the braces are not required. The Control Panel and its helper dialogs will insert them for you as necessary.

Available Variables You can use variables within your expressions. Select the variable that you would like to use from the list and it will be inserted at the cursor location.

If the cursor is on an existing variable (or its associated formatting) it will get replaced by the new variable. 
Variable Formatting When a variable is initially selected the formatting will be automatically set to No formatting.

If the variable contains a date, then you can set the formatting to Format as date and set the style to short, long or custom.
Short: Formats the date using your computers regional settings. Example: 15/07/11
Long: Formats the date using your computers regional settings. Example: 15 July, 2011
Custom: Formats the date using your own settings. The following controls become available for you to use:

Custom date settings
Use the drop list to select one of several predefined formatting styles and make further adjustments within the edit text box.

More detailed information about the customization of dates can be found by clicking on the link.
Apply
Once you have made any changes to the formatting, you must click Apply. Then, your adjustments will be reflected in the expression edit box at the top of the window.

There are several functions that you can use with variables and they greatly enhance the capabilities of custom report scripting. Please find below a summary of these functions and the parameters. It should be noted that any literal string / substring parameters must be enclosed with speech marks unless they are variables.

There is a sample script provided with the program called Script Expression Functions that uses some of these functions.
Info about possible script functions As you are typing in your expession the editor will display all possible functions that can be used.
Info about script function parameters Once it has determined the function itself it will show you the paremeter list.

abs( number )
  Returns the absolute value (magnitude) of a number (negatives becomes positives, positives remain unchanged).
ceil( number )
  Returns the next integer above the given value, unless it is already an integer (always rounds).
count( substring, main_string[, ignore_case = 1] )
  Returns the number of times substring appears in main_string, ignoring case by default.

Example:

$strTalksThisYear = "55, 121, 86, 134, 121, 19, 121"
$iNumTalks = count("121", $strTalksThisYear)
TEXT "The brother will be giving talk number 121 $iNumTalks time(s) this year."
EOL
default_value( $variable )
  For an option variable only, returns the default value from its assigned list of options (the first option in the list). If you attempt to get the default value for any other variable, it will return <NO DEFAULT>. The variable you supply as the parameter is not changed; only the value it is assigned to gets the default value. If the value you are assigning it to is itself the option variable you want to reset, then you are allowed to omit the parameter; for all other cases, it must be supplied (especially if you are using it as part of an expression).

Here are some examples:

REQUIRES ":dynamic:" AS $optionVar VALUES "red, green, yellow, blue, cyan, white"
$normalVar = "-"

$defaultedOptionVar = default_value($optionVar)

// Note that if the variable we're assigning to is the option variable and
we want to default it, we are allowed to omit the variable parameter:
$optionVar = default_value()
ellipsis( string, max_chars )
  Truncates a string at the nearest word and adds ..., such that the resulting string is no longer than the specified number of characters.
field( field name )
  This function extracts the contents of the specified field in the current row, exactly as the FIELD or VARIABLE_FIELD command would do; since the return value of this function is the field contents, it can be either assigned to a variable (an alternative to VARIABLE_FIELD), or more usefully, it can be embedded directly into TEXT or PARAGRAPH to more easily print out field contents as part of a string.

Three ways to print out text mixed with a field:

TEXT "The brother is called "
FIELD "BrotherName"
EOL


or

VARIABLE_FIELD "BrotherName" "$strName"
TEXT "The brother is called $strName"
EOL

or

TEXT "The brother is called { field("BrotherName") }"
EOL
floor( number )
  Returns the next integer below the given value, unless it is already an integer (never rounds).
format_time( time_as_string )
  Formats the passed in time value using program preferences. You can pass in a literal text string, variable or an appropriate field. Here are some examples:

PARAGRAPH "{format_time("2:00pm")}"
PARAGRAPH "{format_time("31/5/2013 2:00pm")}"

LOOP FROM "Congregations" NODATERANGE SORTBY "Congregations"
     PARAGRAPH "{format_time(field("Time"))}"
END_LOOP

It ignores any date included in the passed in value. Please be aware that this function only returns the passed in time (correctly formatted). It does not work in the same way as the CONG_MEET_TIME script command that adjusts the time according to the date in question.
index_of( substring, main_string[, start_index = 0[, ignore_case = 1] ] )
  Returns the index of the first occurrence of substring found in main_string, starting from start_index. It ignores case unless you specify 0 for the last optional parameter.
int( number )
  Is the same as round( number, 0 ) - i.e., it rounds to the nearest integer.
last_index_of( substring, main_string[, last_index = 0[, ignore_case = 1] ] )
  Returns the index of the last occurrence of substring found in main_string, working back from (and including) last_index. It ignores case unless you specify 0 for the last optional parameter.
left( string, count )
  Returns up to the specified number of characters from the start of the string.
len( string )
  Returns the number of characters in the specified string.
lower_case( string )
  Returns the entire string with all letters converted to lower case.
mid( string, start_index[, count = to end] )
  Returns up to the specified number of characters from the middle of the string starting at start_index. If you don't supply the count, it returns from start_index to the end of the string.
nearest_space( string, start_index )
  Returns the result of either next_space() or prev_space(), whichever is nearer to the given index.
next_space( string, start_index )
  Returns the index of the next space within the specified string, starting from (and including) start_index.
prev_space( string, start_index )
  Returns the index of the previous space within the specified string, working back from (and including) start_index.
replace( substring, replace_string, main_string[, ignore_case = 1] )
  Returns a string where all occurrences of substring appearing in main_string have been changed to replace_string, ignoring case by default. Note that even if it ignores case when finding occurrences, it always inserts an exact copy of replace_string, no matter what case it has.

Example:

TEXT {"Brother " + $strBrotherName + " will be providing hospitality this week. Please contact " + $strBrotherName + " if you have any questions."}

can also be accomplished with:

$strTemplate = "Brother %1 will be providing hospitality this week. Please contact %1 if you have any questions."
TEXT { replace("%1", $strBrotherName, $strTemplate) }
right( string, count )
  Returns up to the specified number of characters from the end of the string.
round( number, decimal_places )
  Rounds a number to the nearest value, to the specified number of decimal places.
sgn( number )
  Returns just the sign of the number; it returns 1 for positive values, -1 for negative values and 0 for values within 1e-5 (+/- 0.00001) of zero.
span_to( main_string, separator_string )
  It's a handy shortcut for: left( main_string, index_of(separator_string, main_string) ).
tab( count[, type[, alignment]] )
Inserts a tab character.

count is the tab stop position (number or variable)
type can be "ABS" (absolute) or "REL" (relative). Default is ABS.
alignment can be "CENTRE" or "CENTER" for text aligned centrally around the tab, "FLOWLEFT" or "ALIGNRIGHT" for text that flows left from the tab (i.e., it aligns on its right edge at the tab), or "FLOWRIGHT" or "ALIGNLEFT" (the default) for text that flows right from the tab (i.e., it aligns on its left edge at the tab). These can be shortened to "FL", "AR", "FR" and "AL" respectively. You can also use "<", ">" and "<>" as short versions of "FLOWLEFT", "CENTRE" and "FLOWRIGHT" respectively (as in the TAB script command, the arrow points in the direction that text will flow from the tab).

See Insert Tab help topic for more details.
talk_theme( number )
  Returns the theme for the specified talk number. You can pass in a literal value, variable or the contents of an appropriate field. Here are some examples:

// We can show the theme directly
PARAGRAPH "{talk_theme("166")}"

// We can show the theme using a variable
$strTalkNumber = "171"
PARAGRAPH "{talk_theme($strTalkNumber)}"

// Or using the contents of an appropriate field
LOOP FROM "Home Talks" SORTBY "Last Given"
     TEXT "{talk_theme( field( "Talk Number" ) )}"
     EOL
END_LOOP
title_case( string )
  Returns the entire string with all letters that start words converted to upper case, even if they follow symbols such as speech marks, brackets, hashes, etc.). Note that apostrophes do not cause this behaviour unless they are used as quotes around text.
trim( string )
  Removes spaces, tabs and newline characters from the start and end of the specified string (but not from within the string).
upper_case( string )
  Returns the entire string with all letters converted to upper case.

Manipulating Dates
Strings representing dates can be manipulated with + and - just like numbers, and have a few functions to help work with them. Dates are seen internally as strings, and should be enclosed in quotes when trying to use literal dates; they can be given in your local / national format. The following operators are supported:

date + count --> date string representing date + count days
date - count --> date string representing date - count days
date1 - date2 --> integer representing elapsed days (can be negative if date2 is a later date)

Note that when using standard operators, the operation always uses days as the time unit. To use other time units, you must use the supplied functions.
date_add( date, count[, unit_string] )
  This adds the specified count of time units to the given date, and returns a new date.
date_subtract( date, count[, unit_string] )
  This subtracts the specified count of time units from the given date, and returns a new date.
date_subtract( date1, date2[, unit_string] )
  This subtracts the specified date from the first, and returns the elapsed time in the specified units. The result is *always* the count of *whole* units that have elapsed. So if you specify dates 20 days apart and ask for months, no whole months have elapsed and so 0 will be returned.
format_date( date, format_string )
  This reformats the date string into the specified format for friendlier presentation. format_string can be #SHORTDATE, #LONGDATE, #CUSTOMDATE[date_format] just as they are for formatting dates with format specifiers. The hashes are optional here; for CUSTOMDATE you can simply supply the format such as "%Y-%m-%d".

To make it easier to select date formats for the second parameter of format_date(), you can apply formatting whenever you are inside speech - as long as you have an odd number of double quotes to the left of the cursor position the format can be inserted / updated as if the speech mark on the left were a variable. For example, if you type

format_date($Today, "

(note the trailing double quote) you can now select a date format and click Apply. It will be inserted at the cursor position, right after the quote. Placing the cursor anywhere within that format string will reflect the current format and allow you to change it, just like it does for a variable suffix.
date_part( date, units_string )
  This returns an integer representing just the portion of the date you request.
make_date( year, month, day[, hour, minute, second] )
  This returns a date string built from the parts you supply. Month is 1 - 12, day is 1 - 31.
Notes:
- Unless otherwise specified, any date strings are returned in Access' timestamp format; use format_date() to change to a more familiar style.
- units_string is "Y", "M", "D" for years / months / days or the relevant portion of a date, or "h", "m", "s" for hours / minutes / seconds or the relevant portion of a date. Where supported, if you omit the unit string (or supply an unrecognised one), it is assumed to be "D" for days.