Understanding Functions
Operators
Operators can be used to combine expressions to make more complex expressions, which can be passed as parameters to functions. For example, + and - are numeric operators which operate on (integer) numbers or floating-point values. All operators except the Not operator combine two (or sometimes more) other expressions and appear between them. The Not operator appears before the single expression it operates on, which must be placed in brackets. The following expression illustrates the use of both the And operator and the Not operator.
=TextIf(Exists(%INDI.BIRT.DATE%) and Not(Exists(%INDI.DEAT.DATE%)), "Birth known but not death", "no match")
The first parameter to the TextIf function is this expression:
Exists(%INDI.BIRT.DATE%) and Not(Exists(%INDI.DEAT.DATE%))
This expression can be viewed as the following 2 expressions, combined using the And operator:
Exists(%INDI.BIRT.DATE%)
Not(Exists(%INDI.DEAT.DATE%))
In the latter expression, the Not operator is used to negate the expression it operates on.
The examples above use the 'Exists' function. In practice this is not usually the most useful function because a field can exist but be empty. For this reason, it is usually recommended to use the 'IsSet' function instead. The 'IsSet' function returns true if and only if the specified field exists and is not empty. The opposite of the 'IsSet' function is the 'IsEmpty' function which is also useful. This latter function returns true if a field does not exist or if it exists but is empty.
Brackets can be used to create indefinitely complex expressions with operators. Some examples are given below.
| Operator | Data Types to Use With | Type of Resulting Expression | Description and Examples* |
|---|---|---|---|
| and | Boolean | Boolean |
Combines 2 boolean expressions to create an expression which is true if and only if both combined expressions are true. e.g Exists(%INDI.BIRT.DATE%) and Exists(%INDI.DEAT.DATE%) |
| or | Boolean | Boolean |
Combines 2 boolean expressions to create an expression which is true if and only if either or both of the combined expressions are true. Exists(%INDI.BIRT.DATE%) or Exists(%INDI.DEAT.DATE%) |
| not | Boolean | Boolean |
This operator is the only operator which does not combine 2 expressions but instead operates on a single expression. The Not operator appears before the expression it operates on, which must be placed in brackets. Not(Exists(%INDI.DEAT.DATE%)) |
| + | Number or Float | Number or Float |
Add 2 or more numbers together. Year(%INDI.DEAT.DATE%)+100 Can be used in fact to add any number of numbers together. |
| - | Number or Float | Number or Float |
Subtract one number from another number. Year(%INDI.DEAT.DATE%)-100 |
| * | Number or Float | Number or Float |
Multiply one number by another number. 160*100 |
| / | Number or Float | Number or Float |
Divide one number by another 160/100 |
| mod | Number | Number |
'mod' is short for 'modulus'. The modulus operator returns the remainder after one number is divided by another. 10 mod 3 = 1 |
| = |
Number or Float or Date or Text |
Boolean |
Compare 2 numbers or 2 dates or 2 text items, and return true if they are the same. You cannot compare a date with a number or vice versa, or a date or number with a text item. Text comparisons are case-insensitive. Date comparison example: %INDI.FAMS>MARR.DATE% = DateAt(1900,1,1) Number comparison example: ChildCount() = 2 Text comparison Text(%INDI.SEX%) = Text(%INDI.FAMS>CHIL>SEX%) Actually you could, in this case, dispense with the calls to the Text function, and write this as %INDI.SEX% = %INDI.FAMS>CHIL>SEX% However, it is safer to use a function such as Text, when comparing two data items using '=', to ensure that it is treated as a text comparison (bearing in mind that the = operator can also be used with dates and numbers). |
| < | Number or Float or Date | Boolean |
Compare 2 numbers or 2 dates and return true if the first is less than (or earlier than) the second. You cannot compare a date with a number or vice versa. Date comparison example: %INDI.FAMS.MARR.DATE% < DateAt(1900,1,1) Number comparison example: ChildCount() < 2 |
| > | Number or Float or Date | Boolean |
Compare 2 numbers or 2 dates and return true if the first is greater than (or later than) the second. You cannot compare a date with a number or vice versa. Date comparison example: %INDI.FAMS.MARR.DATE% >DateAt(1900,1,1) Number comparison example: ChildCount() >2 |
| <= | Number or Float or Date | Boolean |
Compare 2 numbers or 2 dates and return true if the first is less than or equal to (or earlier than or the same date as) the second. You cannot compare a date with a number or vice versa. Date comparison example: %INDI.FAMS.MARR.DATE% <= DateAt(1900,1,1) Number comparison example: ChildCount() <= 2 |
| >= | Number or Float or Date | Boolean |
Compare 2 numbers or 2 dates and return true if the first is greater than or equal to (or later than or the same date as) the second. You cannot compare a date with a number or vice versa. Date comparison example: %INDI.FAMS.MARR.DATE% >= DateAt(1900,1,1) Number comparison example: ChildCount() >= 2 |
| . | Text | Text |
Concatenates text expressions. So, for example, take this expression: %INDI% . " married " . %INDI.~SPOU>% if this were used with "John Smith" whose first wife was "Margaret Jones" it would produce the string "John Smith married Margaret Jones". Data references are treated as text if used in a context that requires a text item, as in this case. |
* The examples are all of expressions which can be used as parameters to function calls only. If you want to use these expressions to display values (in a query) you must use a 'wrapper' function such as IsTrue, Calc or Text to provide a context for them to be used.