Calculation field: Difference between revisions
mNo edit summary |
mNo edit summary |
||
Line 97: | Line 97: | ||
=== String functions === | === String functions === | ||
Regarding string functions, an '''''index''''' is the relative number of a character within the string. The first string character has index 0 ( | Regarding string functions, an '''''index''''' is the relative number of a character within the string. The first string character has index 0 (zero). | ||
{| class="wikitable" style="font-size:120%" | {| class="wikitable" style="font-size:120%" |
Revision as of 08:00, 10 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
- Expressions 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 value references 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 in similar fashion, such as the number of fields in an entry. These constitute read-only variables. 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 |
Grouping 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, relational 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
Function | Arguments | Returns |
---|---|---|
abs | number | Absolute value of number |
Date/Time Functions
Function | Arguments | Returns |
---|---|---|
datediff | date, date |
number of days between the 2 dates |
dateadd 4 arguments |
date, interval days, interval months, interval years |
date incremented by the date interval |
dateadd 7 arguments |
date, interval seconds, interval minutes, interval hours, interval days, interval months, interval 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 |
now | The current time in seconds since the start of Jan 1 1970 Uses Universal Time (UTC) regardless of local settings or time zone | |
relativeTimeStr | start time | String describing the elapsed time since the given start time |
formatDuration | seconds | duration as a string formatted as MM:SS or H:MM:SS |
String functions
Regarding string functions, an index is the relative number of a character within the string. The first string character has index 0 (zero).
Function | Arguments | Returns |
---|---|---|
numToStr | number | String representation of number |
compareTo | string1, string2 |
Zero, if the strings are equal A negative number, if string1 < string2 A positive number, if string1 > string2 |
compareToIgnoreCase | string1, string2 |
Zero, if the strings are equal (ignoring case) A negative number, if string1 < string2 (ignoring case) A positive number, if string1 > string2 (ignoring case) |
concat | string1, string2 |
String of string2 appended to the end of string1
concat(#{name}, ', Jr.')
|
endsWith | string1, string2 |
TRUE (1.0), if string1 ends with string2 FALSE (0.0), if it doesn't |
equals | string1, string2 |
TRUE (1.0), if the strings are identical FALSE (0.0), if not |
equalsIgnoreCase | string1, string2 |
TRUE (1.0), if the strings are identical (ignoring case) FALSE (0.0), if not |
indexOf | string1, string2, index |
Within string1 from index to the end, the index of the next occurrence of string2 -1, if no more occurrences next = indexOf(#{Description}, " and ", next)
|
length | string | Length of string, in characters |
replace | string, char1, char2 |
String of string with all matching char1s replaced with char2s
replace(#{Description}, ';', '.')
|
startsWith | string1, string2, index |
TRUE (1.0), if string1, starting at position index, starts with string2 FALSE (0.0), if it doesn't |
substring | string, startIndex, endIndex |
The substring of string that starts at startIndex and ends at endIndex |
toLowerCase | string | The string string with all uppercase characters converted to lowercase |
toUpperCase | string | The string string with all lowercase characters converted to uppercase |
trim | string | The string string with all "white space" (blanks, tabs, etc) removed from both ends |
Date/Time Functions
Function | Arguments | Returns |
---|---|---|
datediff | date, date |
number of days between the 2 dates |
dateadd 4 arguments |
date, interval days, interval months, interval years |
date incremented by the date interval |
dateadd 7 arguments |
date, interval seconds, interval minutes, interval hours, interval days, interval months, interval 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 |
now | The current time in seconds since the start of Jan 1 1970 Uses Universal Time (UTC) regardless of local settings or time zone | |
relativeTimeStr | start time | String describing the elapsed time since the given start time |
formatDuration | seconds | duration as a string formatted as MM:SS or H:MM:SS |
Logical functions
Function | Arguments | Returns |
---|---|---|
if | expression, valueIfTrue, valueIfFalse |
One value if a logical expression is TRUE (not equal to 0.0) and another if it is FALSE (equal to 0.0) Read it as "If expression is true, then valueIfTrue, else valueIfFalse" if(#{remaining} == 0, 'Done', 'Incomplete')
|
switch | expression, valueResultPairs, defaultResult |
1+(2*#pairs)+1 arguments The final result based on matching expression against the paired values to identify the paired result (or else defaultResult) to be used switch(#{count},
1, 'One',
2, 'Two',
'Many')
|
Examples
Example: Single-operator expression
Entries contain fields: Price, Count
We add a Calculation field Sum by using the following expression:
#{Price} * #{Count}
Example: Percentage
Entries contain fields: Count, Total
We add a Calculation field Percentage by using the following expression:
(#{Count } / #{ Total }) * 100
Example: Duration
Entries contain fields: StartDateTime, EndDateTime
We add a Calculation field Duration by using the following expression (result is the number of seconds):
#{EndDateTime} - #{StartDateTime}
Example: Days
Entries contain fields: StartDate, EndDate
We add a Calculation field Days by using the following expression:
datediff(#{EndDate}, #{StartDate})
Example: Days left
Entries 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: References
There’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}
Accessing values of linked library entries
Use the following syntax to get access to the values of Link to Entry and other linking fields:
#{link field name.linked field name}
where link field name is the name of a Link to Entry field, linked field name is the name of a field of a related entry.
You can select a field, including one from a related library, using the +Field button.
If a link field contains several links, then use an index (starting from zero) to get access to them:
#{link field name@0.linked field name}, #{link field name@1.linked field name}, …
You can specify one of the supported aggregation functions instead of the index:
#{link field name@sum.linked field name}
This expression returns the sum of values of field linked 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}
Example: Parents and children
Parents contains fields Name and Age. Children has fields Name and Parents (link to entry (many-to-many)).
Add field "Average parent age" (calculation, real, scale 1) to Children and select the related field from the +Field list: parents.age. Then insert "@avg", making it:
#{parents@avg.age}
Example: Orders of products
Products has fields SKU, Description, and Price. Orders has fields #, Date, Customer (link to entry (one-to-many)), and Products (link to entry (many-to-many)).
Add field "Total" (calculation, real, scale 2) to Orders and select the related field from the +Field list: products.price. Then insert "@sum", making it:
#{products@sum.price}
Note: This example points out a deficiency in using a many-to-many relationship for orders of products (or of services). One would hope to associate a quantity for each product ordered, but that requirement cannot be met with this 3-library technique. So, in real life, order solutions generally involve 4 libraries: Customers, Products, Orders, and Order Items. The quantity can reside in Order Items.
- Name
- Textual, case-insensitive field identifier that can include spaces; it's best to avoid characters that can be used as operators, though (standard across all field types)
- Hint
- Textual, case-insensitive field identifier that can include spaces
Advanced Parameters
- Thousands separators
- If the chosen result type is Integer or Real, use spaces as separators to break the value into thousands. By default, it will not.
Display Options
- Display in the list as
- Select among the following roles for the current field in the entries list:
- As a Regular field
- As an Entry Name
- As an Entry Description
- As an Entry Status
- Use in Calendar as
- None / Duration, mins / Duration, hours
- The field name
- Display the name of the field in the card entry (By default, On). By this, it is meant that the field name will or will not be displayed on either the Entry View card or the Entry Edit card.
- Font
- Choices are:
- Family: Regular, Light, Condensed, Thin, Medium. By default, Regular.
- Style: Normal, Bold, Italic, Bold Italic. By default, Normal.
- Size: By default, 18. Direct entry to change.
- Color: By default, White. Palette of 20 colors, backed up by a continuously variable color chooser.
- Dependencies
- Ability to add one or more visibility dependencies upon fields with qualifying types.