Calculation field: Difference between revisions
mNo edit summary |
mNo edit summary |
||
Line 94: | Line 94: | ||
=== Logical functions === | === Logical functions === | ||
{| class="wikitable" style="" | |||
! Function !! Arguments !! Returns | |||
|- | |||
| '''if''' || expression<br/>value if TRUE<br/>value if FALSE || one value of a logical expression if TRUE (not equal to 0.0) and another if it is FALSE (equal to 0.0)<br/>For example, if(#{remaining} == 0, 'Done', 'Incomplete') | |||
|- | |||
| '''switch''' || expression<br/> value/result pairs<br/> default result || the final result based on selecting among the paired results to identify the paired value (or else the default value) to be used.<br/>For example, switch(#{count}, 1, 'One', 2, 'Two', 'Many') | |||
== Link to Entry == | == Link to Entry == |
Revision as of 08:35, 4 December 2016
The values in Calculation fields are calculated from the expressions specified by you in Memento Script, defined herein.
The following types of expressions are supported:
- Numeric
- Expression involving numbers
- String
- Strings can also be added together, compared, etc
- Boolean
- Expressions that evaluate to true (1.0) and false (0.0)
Numeric and string types can not be mixed in a left / right operand pair.
Result type
For each calculation, there must be a correct result type set:
- Real result and Integer result
- are used for expressions with a numerical result
- Date/Time result
- is used to represent the result in the form of date and time. The calculation result is interpreted as the number of seconds that has passed since January 1, 1970 (UNIX time).
- Date result
- is the same as the previous type, but displays only the date
- String result
- is used for expressions that contain string manipulations or functions that return a string
Field values and other variables
The values of other fields in the same entry and fields in linked libraries may be referenced by enclosing them within a hash mark and open brace #{ and a closed brace }. Other values can be referenced this way as well, such as the number of fields in an entry. For example, consider the following expression:
#{field name 1} + #{library 2.field name 2}
String values must be enclosed by single-quote (apostrophe) characters:
'Result: ' + '#{string field 1}' + '#{string field 2}'
Field references may be typed into the Expression area, or they may be inserted by pressing the +Field button and selecting from among the fields listed.
Values of variables cannot be changed by the Calculation field.
Operators
The following operators are supported:
Symbol | Operator | Description |
---|---|---|
( ) |
open parenthesis closed parenthesis |
Group together a subexpression within an outer expression Example: (subexpression 1) + (subexpression 2) |
- + |
minus plus |
Unary number Example: -5 or +10 |
+ - |
addition subtraction |
Binary Example: #{a} + #{b} - 1 |
* / |
multiplication division |
Binary Example: 6 / (8 * 13) |
% | modulo | Binary, integer remainder after division Example: #{months} % 12 |
== != < <= > >= |
equal not equal less less or equal greater greater or equal |
Binary Example: if( #{count} <= #{max} , #{count} , #{max} ) |
&& !! |
and or |
Binary, for TRUE and FALSE (Boolean) Example: if( #{Done} !! ( #{Able} && #{Trying} ) , 'Raise' , 'No raise' ) |
! | not | Unary, for TRUE or FALSE (Boolean) Example: if( ! #{Done} , 'Work' , 'Relax' ) |
Functions
Functions are available to assist in crafting expressions to calculate a final result. Function calls may be typed into the expression, or the user may use the +Function button to display a tabbed list of functions that may be used.
- Note
- The rest of this section documents the available functions. However, while the functions may be better explained here, it may be incomplete or even incorrect. The menus of available functions within Memento when using the +Function button are quite useful and will always be more recently updated than what is documented here.
Math functions
Date/Time Functions
Function | Arguments | Returns |
---|---|---|
datediff | date date |
number of days between the 2 dates |
dateadd 4 arguments |
date days months years |
date incremented by the time interval |
dateadd 7 arguments |
date seconds minutes hours days months years |
date incremented by the time interval |
formatDate | seconds since start of Jan 1 1970 | equivalent date string |
formatDateTime | seconds since start of Jan 1 1970 | equivalent date/time string |
formatTime | seconds since start of Jan 1 1970 | equivalent time string |
String functions
Logical functions
Function | Arguments | Returns |
---|---|---|
if | expression value if TRUE value if FALSE |
one value of a logical expression if TRUE (not equal to 0.0) and another if it is FALSE (equal to 0.0) For example, if(#{remaining} == 0, 'Done', 'Incomplete') |
switch | expression value/result pairs default result |
the final result based on selecting among the paired results to identify the paired value (or else the default value) to be used. For example, switch(#{count}, 1, 'One', 2, 'Two', 'Many') Link to EntryUse the following syntax to get access to the values of Link to Entry and other linking fields: #{link field name.child field name}
where link field name is the name of a Link to Entry or similar field, child field name is the name of a field of a related entry. If a link field contains several links, then use an index (starting from zero) to get access to them: #{link field name@0.child field name}, #{link field name@1.child field name}, …
You can specify one of the supported aggregation functions instead of the index: #{link field name@sum.child field name}
This expression returns the sum of values of field child field name for all entries included into link field name. The functions supported are: sum, avg, min, and max. Use the following syntax to get the number of entries in the library referenced by link field name: #{link field name@size}
ExamplesExample: SumEntries contain fields: Price, Count We add a Calculation field Sum by using the following expression: #{Price}*#{Count}
Example: PercentageEntries contain fields: Count, Total We add a Calculation field Percentage by using the following expression: (#{Count } / #{ Total }) * 100
Example: DurationEntries contain fields: StartDateTime, EndDateTime We add a Calculation field Duration by using the following expression (result is number of seconds): #{EndDateTime} - #{StartDateTime}
Example: DaysEntries contain fields: StartDate, EndDate We add a Calculation field Days by using the following expression: datediff(#{EndDate}, #{StartDate})
Example: Days leftEntries contain field: Time We add a Calculation field Days Left by using the following expression: if( #{Time} > now() , rint((#{Time} - now())/(60*60*24)) , 'in the past' )
Example: ReferencesThere’s a library Order containing orders and a library Item containing items. Library Order contains a link to library Item and a numeric field Count. Library Item contains field Price. To calculate the cost of the order, add Calculation field Sum using the following formula: #{count} * #{item.price}
If an order can make a reference to several different items, their total cost can be calculated: #{item@sum.price}
The most expensive item: #{item@max.price}
The cheapest item: #{item@min.price}
The average price for the item: #{item@avg.price}
Advanced Parameters
Display Options
|