Calculation field: Difference between revisions

{{Stamp|2016-12-10|4.1.0|1.0.7}}

| '''<big>&&</big>'''<br/>'''<big>!!</big>''' || <big>and<br/>or</big> || Binary, for TRUE and FALSE (Boolean)<br/>Example: if( #{Done} '''<big>!!</big>''' ( #{Able} '''<big>&&</big>''' #{Trying} ) , 'Raise' , 'No raise' )

:; 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.

| '''numToStr''' || number || String representation of number

| '''compareTo''' || string1,<br/>string2 || Zero, if the strings are equal<br/> A negative number, if string1 < string2<br/> A positive number, if string1 > string2

| '''compareToIgnoreCase''' || string1,<br/>string2 || Zero, if the strings are equal (ignoring case)<br/> A negative number, if string1 < string2 (ignoring case)<br/> A positive number, if string1 > string2 (ignoring case)

| '''concat''' || string1,<br/>string2 || String of string2 appended to the end of string1

| '''endsWith''' || string1,<br/>string2 || TRUE (1.0), if string1 ends with string2<br/> FALSE (0.0), if it doesn't

| '''equals''' || string1,<br/>string2 || TRUE (1.0), if the strings are identical<br/> FALSE (0.0), if not

| '''equalsIgnoreCase''' || string1,<br/>string2 || TRUE (1.0), if the strings are identical (ignoring case)<br/> FALSE (0.0), if not

| '''indexOf''' || string1,<br/>string2,<br/>index || Within string1 from index to the end, the index of the next occurrence of string2<br/>-1, if no more occurrences

| '''datediff''' || date,<br/>date || number of days between the 2 dates

| '''dateadd'''<br/>4 arguments || date,<br/>interval days,<br/>interval months,<br/>interval years || date incremented by the date interval

| '''dateadd'''<br/>7 arguments || date,<br/>interval seconds,<br/>interval minutes,<br/>interval hours,<br/>interval days,<br/>interval months,<br/>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<br/>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

'''[[TBD]]'''

| '''if''' || expression,<br/>value if TRUE,<br/>value if FALSE || 3 arguments<br/>One value if a logical expression is TRUE (not equal to 0.0)<br/>and another if it is FALSE (equal to 0.0)<br/>Read it as "If expression is true, then value 1, else value 2"<br/>Example: if(#{remaining} == 0, 'Done', 'Incomplete')

| '''switch''' || expression,<br/>value/result pairs,<br/>default result || 1+(2*#pairs)+1 arguments<br/>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<br/>Example: switch(#{count}, 1, 'One', 2, 'Two', 'Many')

datediff(#{EndDate}, #{StartDate})

== Accessing values of linked library entries ==

Use the following syntax to get access to the values of Link to Entry and other linking fields:

</source>

 

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:

 

You can specify one of the supported aggregation functions instead of the index:

 

#{link field name@sum.linked field name}

</source>

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

 

 

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:

=== 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:

<source lang="JavaScript">#{products@sum.price}</source>

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