Reports like the Wiring Script report shown in Figure 1 use conditional formatting to highlight columns or rows, change colors, and add borders based on the information in the cells.
Figure 1 – The Wiring Script report uses a variety of conditional formatting options for highlighting and borders.
In the example of Figure 1, the horizontal lines group the rows by rail and pin. If multiple shells of different effect types are fired together from the same rail and pin then they will be split out across multiple rows, but the horizontal lines give an indication that the rows go together with the same pin. Alternatively, you can turn on the light gray zebra striping and use conditional formatting to group rows with the same rail and pin in the same light gray highlighting, as shown in Figure 2.
Figure 2 – Alternative formatting options employ light gray zebra-striping to group rows together instead of borders between rows.
From the Script window’s blue gear menu, do “Edit report template…” and choose “basic_wiring_script_portrait” to see the original Wiring Script report’s configuration dialog. Paragraph 6 of the dialog controls the conditional formatting, as in Figure 3.
Figure 3 – The conditional formatting options employed by the original Wiring Script report.
A summary of the conditional formatting choices for original Wiring Script report is,
- Option A and G invert the colors of the Rail and Pin columns.
- Option C groups the rows with the same rail and pin, putting lines between the groups.
- Option D and E add the colors for angled effects.
- Option F highlights in yellow the rows whose pins have multiple e-matches. The full expression in the box is, “[or [= fullAddress prev.fullAddress] [= fullAddress next.fullAddress] [and [not chainRef] [> numDevices 1]]]”. Options D-H are programming language expressions. If you are not a programmer, you can copy/paste examples from existing report templates.
- Option H highlights the notes in red if they are not blank.
Figure 4 shows the conditional formatting choices for the modified Wiring Script of Figure 2.
Figure 4 – The modification highlights every-other-row, except it keeps rows that have the same address highlighted together.
The differences in Option C are a good jump off point for describing the how the conditional formatting options work.
Conditional formatting options
The conditional formatting Option C provides two selectors for terms, and a selector to choose how to compare them. If the comparison is true, then the choice in the formatting selector applies to the choice in the “applies to” selector. The terms are all the fields of the report rows. For each field you actually have an option of “this row” or “previous row” or “next row”, which enables you to compare a cell value in a row to a cell value in the row above it or below it in the report. For some uses, comparing to the previous or next row is what you need to do. For other uses, you may compare two different fields of the same row.
The examples of Figure 3 and Figure 4 compare the same field of a row to the previous row. The expression in Figure 3 is true if the fields are the same; the expression in Figure 4 is true if the fields are different. Looking back at Figure 1 and 2, the table of Figure 1 adds border above every row that has a different address from the row above it. This logical expression matches the selectors in Figure 3. The table of Figure 2 extends the light gray zebra striping to additional rows if they have the same address as the light gray shaded or white row above it. This logical expression matches the selectors in Figure 4.
Option A and Option B are simpler types of expressions. Option A is the simplest of all. It doesn’t even have any terms. The expression is true if you check the checkbox, and it will apply to every row in the report. This option offers an easy way to change the formatting for an entire column without any special conditions.
Option B has a single term, which is a field of your choice. As the wording around option B explains, the expression is true whenever the field value is not blank or zero.
Options D-H are programming language expressions. If you are not a programmer, you can copy/paste examples from existing report templates. If you are a programmer, the terms and function names are explained below.
All options A-H are expressions. Option A is trivially true whenever you check the box. Option B is true based on the value of a single field. Option C compares two fields. Options D-H are free form expressions that can compare multiple fields. Options D-H can also be just simple expressions if you know what to type. For example, Option G in Figure 3 and 4 is the expression “true” which is not surprisingly true all the time. So Option G in this example is just like the checkbox Option A. Similarly, any expression that can be represented in Option B and C can also be represented in Options D-H. Options A, B, and C are just an easier user interface.
Free-form expressions (Options D-H)
Free form expressions are written in a simple programming language that has access to the field values of the current, previous, and next row. An expression in the programming language is either a term, or a function with terms as parameters. The terms themselves are field values of the rows, or literals like numbers or strings, or the result of functions operating on terms. Functions are written as square brackets containing the function name followed by the terms that the function operates on, separated by spaces. For example, the expression in Option F of Figure 3 and 4 that highlights the Devices in yellow if multiple e-matches connect to the pin is:
[or [= fullAddress prev.fullAddress] [= fullAddress next.fullAddress] [and [not chainRef] [> numDevices 1]]]
The interpretation of this expression in English phrasing is,
( fullAddress = prev.fullAddress ) OR ( fullAddress = next.fullAddress ) OR ( ( NOT chainRef ) AND ( numDevices > 1 ) )
This is a complicated expression, explained in Wiring Script report. In addition to counting the devices associated with the pin, it has to take into consideration that chains represent multiple devices but require only a single e-match (yet multiple chains can be e-matched to the same pin together).
The conditional formatting is applied if the expression value converted to a boolean value is true. Most comparison functions return a boolean result, true or false. Other functions like abs (absolute value) return numbers. Term values are various types including numbers, strings, booleans, and a few other types. If the expression value is not boolean, it is converted to a boolean value following these rules: 1) undefined is false; 2) the number zero is false; 3) empty string is false; 4) everything else is true.
Table 1 – Functions in conditional expressions
| Function | Arguments | Description |
|---|---|---|
| and | One or more terms | Logical conjunction of terms. |
| or | One or more terms | Logical disjunction of terms. |
| = | Two terms | Compares if two terms are equal according to the following rules: 1) numbers compare to each other naturally; 2) symbols compare to each other naturally; 3) boolean values compare to each other naturally; 4) strings compare to each other naturally (case sensitive comparisons of characters in the strings); 4) undefined is equal to undefined; 5) all other comparisons between terms of different types are false. |
| < | Two terms | Compares if two terms for the “less than” relationship according to the following rules: 1) numbers compare to each other naturally; 2) symbols compare to each other lexicographically (case sensitive comparisons of characters in the symbols); 3) boolean values compare to each other naturally, false being less than true; 4) strings compare to each other lexicographically (case sensitive comparisons of characters in the strings); 4) undefined is not less than undefined; 5) all other comparisons between terms of different types are the result of comparing the types themselves in an enumerated order, not comparing the values. |
| > | Two terms | Similar to less than comparison. |
| <= | Two terms | Similar to less than comparison. |
| >= | Two terms | Similar to less than comparison. |
| != | Two terms | Similar to equal comparison (but the opposite). |
| not | One term | True if and only if the term’s value is false or undefined. If the term is zero or empty string, the result of not is false (in some programming languages this is not the case). |
| abs | One term | Absolute value function. |
| containsSubstring | Two terms and then optionally the symbol :caseSensitive | True if the first term contains the second term as a substring, after converting terms to strings if they are symbols or string-type arrays. The search function is not case sensitive unless you add the :caseSensitive parameter. |
| + | One or more terms | Adds the terms and returns the result. |
| – | One or more terms | Subtracts the terms after the first term from the first term and returns the result. |
| * | One or more terms | Multiplies the terms and returns the result. |
| / | One or more terms | Divides the first term by the remaining terms and returns the result. |
| round | One term | Rounds the number to the nearest integer. |
| if | Three terms | If the first term converted to a boolean value is true, then return the second term; else return the third term. |
| stringsEqual | Two terms and then optionally the symbol :caseSensitive | Compare two terms, after converting them to strings if they are symbols or string-type arrays. Returns true if they are the same. The function is not case sensitive unless you add the :caseSensitive parameter. |
| string | One term | Convert the term to a string if it is a number or symbol or string-type array or already a string. |
| symbol | One term | Convert the term to a symbol if it is a number or string or string-type array or already a symbol. |
| int | One term | Convert the term to an integer if it is a double or already an integer (rounding down). |
| double | One term | Convert the term to a floating point number if it is an integer or already a floating point number. |
| type | One term | Return the type of the term, :bool, :string, :symbol, :int, :double, or :array. |
| numberFromString | One term | Convert the term from a string to a number, after converting the term to a string if it is a symbol or string-type array. The conversion to a number permits both a dot or comma character as the decimal radix and allows characters after the number that are part of the number in the string, and allows leading whitespace. For example, " 1,23abc" converts to the number 1.23. The type of the returned number is integer if has no fraction; otherwise it is a floating point number. |
| stringLength | One term and then optionally the symbol :utfi | Returns the length of the string, after converting the term to a string if it is a symbol or string-type array. The length is in Unicode characters (code points), unless the optional symbol :utfi is present, in which case the length is in UTF-8 or UTF-16 or UTF-32 code units, which is platform dependent. On Windows, :utfi means UTF-16 code units, and the length of the string is equivalent to the C++ function wcslen(). |
Table 2 – Terms in conditional expressions
| Term | Example | Description |
|---|---|---|
| All row fields | fullAddress | The internal name (not the English name and not the localized name) of any script column, as defined in Script table columns. Terms are case sensitive, so exact capitalization is required. The value of the term is the internal representation of the field value, not the formatted representation, and the value undefined for any numeric field is converted to zero for natural mathematical comparisons. |
| Literals | “red”, 0.0762, or #<“Front Pos”> |
Strings, numbers, and symbols. Symbols are enclosed in brackets as in #<“MYSYMBOL”> to accommodate special characters and spaces. Strings and symbols can contain backslash and double quotes internally if preceded by backslash. For example, “2.5\”” is the string that prints out as: 2.5″. |
| prev | prev.fullAddress | A representation of the previous row in which the fields of the row can be accessed with the dot character. |
| next | next.fullAddress | A representation of the next row in which the fields of the row can be accessed with the dot character. |
| this | this.fullAddress | A representation of the current row in which the fields of the row can be accessed with the dot character. Since fields themselves are also defined terms, this “this” term merely provides an alternative representation for the fields themselves. Typing “this.fullAddress” is the same as typing “fullAddress” (both expressions without quotations). |
| true | true | A boolean term whose value is true. |
| false | false | A boolean term whose value is false. |
| undefined | undefined | A term whose value is the undefined value. |
Numerical comparisons
All functions including numerical comparisons operate on the internal representation of the field values, which are defined in Script table columns. Non-integer numerical comparisons require care because you need to know what the internal representation is, and you may need to take into account possible precision error such as with the conversions between inches and millimeters.
In Finale 3D, distances and lengths are always represented internally in meters, with the exception of effect size which internally is a floating point number representing typed microns (1/1000th of a millimeter), for which the integer part of the number is the actual size and the fraction part of the number is type information indicating whether the size is to be displayed as inches or millimeters. Since a fraction of a micron is insignificant in pyro it doesn’t affect the size in a meaningful amount, but it can affect comparisons for equality with literals. For example, you should not directly compare a size to 75mm using the expression [= size 75000] because even if the size is 75mm the type information in the fraction will make the comparison false. If you want to compare to 75mm explicitly you could use the expression, [= [round [/ size 1000]] 75].
For pyro it is usually not a great idea to compare effect sizes for equality because the conversion between inches and millimeters in pyro is extremely loosey goosey (is a 3″ shell 75mm or 76.2?). If you need to compare sizes, compare in ranges. Instead of [= size 75000] please write something like [< [abs [- size 75000]] 2000] for whatever error tolerance you want.
Types of numbers
Numbers are either integers or floating point numbers. For comparisons, the type of a number doesn’t matter because comparison functions will convert integers to floating point numbers when necessary. However math expressions involving only integers use “integer math” in which all intermediate and final values are themselves integers, incapable of representing fractional values. For example, [/ 3 2] is 1, whereas [/ 3.0 2] is 1.5 because the terms of the first expression are all integers and the terms of the second expression are not all integers.
Expressions involving integers and floating point numbers will automatically convert the integers to floating point so the calculation can be completed entirely in floating point math. Comparing to the previous example, [/ [+ 3 0.0] 2] is 1.5, because [+ 3 0.0] converts the type of the number 3 from integer to floating point so it can be added to a floating point number. From that point, the final result of the expression is the same as [/ 3.0 2], which is 1.5. An equivalent expression to [/ [+ 3 0.0] 2] is [/ [double 3] 2].
Using integer math and the available functions in Table 1, you can test if a number is even or odd using an expression like [= [/ chainRef 2] [/ chainRef 2.0]], which has the value true if the variable chainRef (which holds an integer value) is even.