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 number values. All operators except the Not operator combine 2 (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 user 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.
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 ff 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 | Number |
Add 2 or more numbers together. Year(%INDI.DEAT.DATE%)+100 Can be used in fact to add any number of numbers together. |
| - | Number | Number |
Subtract one number from another number. Year(%INDI.DEAT.DATE%)-100 |
| * | Number | Number |
Multiply one number by another number. 160*100 |
| / | Number | Number |
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 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 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 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 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 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 product 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.