Calculation field: Difference between revisions

From Memento Database Wiki
Jump to navigation Jump to search
mNo edit summary
mNo edit summary
Line 21: Line 21:


== Field value references and other variables ==
== 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 <big>'''#{'''</big> and a closed brace <big>'''}'''</big>. 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:
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:


<source lang="java">
<source lang="java">

Revision as of 08:43, 9 December 2016

« Page as of 2016-12-04, editions Mobile 4.1.0, Desktop 1.0.7 »

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

TBD

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

TBD

Logical functions

Function Arguments Returns
if expression,
value if TRUE,
value if FALSE
3 arguments
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 value 1, else value 2"
Example: if(#{remaining} == 0, 'Done', 'Incomplete')
switch expression,
value/result pairs,
default result
1+(2*#pairs)+1 arguments
The final result based on matching the expression against the paired values to identify the paired result (or else the default result) to be used
Example: switch(#{count}, 1, 'One', 2, 'Two', 'Many')

Link to Entry

Use 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}

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}
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.