Calculations
Some times, fields don’t have to be manually input into you database. If you can think of any way to automate the calculation of their value, you’ll gain even more time.
Calculations are a defined way to define a value based on other values in your app. You can define a formula using the graphical tool we provide in the platform. When the trigger parameters are met, the formula will be run for this field, pulling data from the required fields, and computing its value so that it will be updated on the server, directly in the database.
Calculation triggers
Calculation triggers
We provide 3 types of triggers:
- Auto-calculated: Some times, fields don’t have to be manually input into you database. If you can think of any way to automate the calculation of their value, you’ll gain even more time. Setting a field as calculated makes it automated, you won’t have to edit it manually any more. When enabling of the auto-calculated mode, the field is marked as a “read only field” by default. This is not mandatory, we just found that most people wanted it this way. You are free to deactivate the “read only field” option if you want to manually input data as well. Keep in mind that the data will be overridden by the formula every time something the formula is based on changes in the database.
- Calculated default value: Some times, values need to be calculated only once and not updated later automatically. This way you can manually change the value but for the initial value, it’s calculated. Setting the field as “read-only” lets you create a field that calculated at the record creation but won’t be updated later.
- Manual calculation: This option allows you to calculate the value manually only. It will not be calculated on initialization nor on data changes. You will simply need to click a button on the title menu of the field to trigger the calculation.
Calculation Graphical Edition (empty)
Formula graphical edition
To edit the formula used to calculate the field, click on the blocks. Click on the dotted squares to add new blocks to the formula.
Select a function
The formulas in Simitless are using a code-less calculation graphical interface.
To define your formula:
- Click the dotted square to select a function.
- In the function selection box that appear in a popup window, select a function. Functions are sorted by categories, you can filter/search through keywords on the same window. When necessary, static and app data are configured right there, in the same window.
- Select the correct formula and repeat the operation with new blocks until you have what you want.
A formula can be represented as a tree, with each smaller branch feeding its output data to the bigger branch.
Formulas are simple function trees
In this platform, we are representing the tree as nested blocks. Each block representing a formula or a data source.
Formula as blocks
Re-calculate all data
Using the re-calculation button, you can trigger a general re-calculation of all the data of this type. Be careful, in addition to overriding all the values in the fields of this type, this may take time and slow down your application!
The button will become inoperable for a few seconds after use to avoid double triggering a full re-calculation too often. Once finished, a notification toast will appear on the screen confirming the completion.
Formula run history
Below the formula, a list of recent function runs and their details is shown. The platform keeps in memory the 5 last execution of each formula. These are sorted by execution time with the most recent on top. In this list, for each run of the formula, you will find the result, followed by the detail of each operation and its intermediary result. This view is very useful to debug the working of your formula.
Formula Library
There are 237 functions, so far!
App Data
| Function | Description | Arguments |
|---|---|---|
| Table / Objects | Get the values from a sub-object type. for example, get a series of value from the content of a table. | field / internalfield / metafield / backlinks / object / objects |
| Nested Sub-Record / Links | Get one of the value from one of the sub-object. for example, get a single value from a link or a panel. | field / internalfield / metafield / backlinks / object / objects |
| Field | Get the value from a data type in the same object. | |
| Internal Field (DB ID) | Get the internal DB ID from a data type in the same object. | |
| Meta Field | Get the value from a meta field in the object. | |
| Back links | Use the value from records linking to this record. | field / internalfield / metafield / backlinks / object / objects |
| Parent Record | Get the parent record object to select a value within it. | field / internalfield / metafield / backlinks / object / objects |
Static
| Function | Description | Arguments |
|---|---|---|
| Value | Set a static value in your formula. | |
| Now | Returns the date and time at the instant of the function call on the server in ISO 8601 format, UTC (Z) | |
| Today | Returns the date at the instant of the function call on the server in ISO 8601 format, UTC (Z) | |
| Random | Returns a random number between 0 and 1. | |
| Random Integer | Return a random integer between the max and min numbers provided. Default max = 100 and min = 0. | max, min |
Filters & search
| Function | Description | Arguments |
|---|
Logics
| Function | Description | Arguments |
|---|---|---|
| Either | Checks if either value within the array is truthy. | … |
| All true | Checks if all the values within the array are truthy. | … |
| Equal Value | Checks if the values are equal | Value, Value |
| Equal Object | Checks if the objects or arrays are the same | Object, Object |
| Has Value? | If the value contains something, returns a boolean true, otherwise false. | Value |
| Trigger | Triggers any calculation based on trigger fields that are not taken into account in the calculation. First field is the calculation, all the following arguments are triggers and their change will trigger a re-calculation. | calc, … |
| if…then…else | If a condition is true (truthy), then returns the true value else returns the false value. (Caution, both expressions are evaluated, keep calculations levels simple and low) | Condition, If True, If False |
Text
| Function | Description | Arguments |
|---|---|---|
| Concatenate | Concatenates all the text in one text. | … |
| Join | Join all the texts from a series of elements using a separator (for example, it could be “, “). | Separator, … |
| Text Length | Returns the length of the text provided. | Text |
| Sentiment Score (English) | Provide a sentiment score based on AFINN-165 english language word list. | Text |
| Sentiment Score (French) | Provide a sentiment score based on AFINN word list translated in French. | Text |
| Text preview | Generates a preview of a text by cutting it and limiting the text to full sentences | Text, Number |
| Is URL? | Checks if the value is a url with http:// or https://. | Value |
| Levenshtein distance | Calculates Levenshtein distance between two text strings. http://en.wikipedia.org/wiki/Levenshtein_distance | Value, Value |
| Capitalize | Converts first letter of the text to uppercase. | Value |
| Decapitalize | Converts first letter of the text to lowercase. | Value |
| Chop text | Chops the text in equal sections | Value, chunkSize |
| Clean text | Trim and replace multiple spaces with a single space. | Value |
| Clean diacritics | Replace diacritic characters with closest ASCII equivalents. (ąàáäâãåæăćčĉęèéëêĝĥìíïîĵłľńňòóöőôõðøśșşšŝťțţŭùúüűûñÿýçżźž are supported) | Value |
| Chars from text string | Chops the text string into an array of individual characters. | Value |
| Swap case | Returns a copy of the text in which all the case-based characters have had their case swapped. | Value |
| Text includes? | Tests if text contains a specific text (substring). | Value, substring |
| Count chars in text | Returns number of occurrences of substring in text. | Value, substring |
| Escape HTML | Converts HTML special characters to their entity equivalents. This function supports cent, yen, euro, pound, lt, gt, copy, reg, quote, amp, apos. | Value |
| Unescape HTML | Converts entity characters to HTML equivalents. This function supports cent, yen, euro, pound, lt, gt, copy, reg, quote, amp, apos, nbsp. | Value |
| Insert in a text | Insert a text within another text. | Value, index, substring |
| Replace all occurences in Text | Replaces all the occurence of a text in another text by a replacement text. | string, find, replace, [ignorecase=false] |
| Is Blank text? | Checks if the text is blank (empty text). | Value |
| Split lines | Split lines to an array | Value |
| Reverse text | Return reversed text: “hello” -> “olleh”. | Value |
| Dedent | Dedent unnecessary indentation or dedent by a pattern. | Value, pattern |
| Splice text | Adds/removes letters to/from the text. | Value, index, howmany, substring |
| Text starts with | Checks whether the text begins with a text. | Value, starts, [position] |
| Text ends with | Checks whether the text ends with a text. | Value, ends, [position] |
| Predecessor | Returns the predecessor text to a text. (B -> A) | Value |
| Successor | Returns the successor text to a text | Value |
| Titleize | Capitalize the first letter of each word. | Value |
| Camelize | Converts underscored or dasherized text to a camelized one. Begins with a lower case letter unless it starts with an underscore, dash or an upper case letter. | Value |
| Classify text | Converts text to camelized class name (same as in coding standard for object-oriented languages). First letter is always upper case | Value |
| Underscored | Converts a camelized or dasherized text into an underscored one | Value |
| Dasherize | Converts a underscored or camelized text into an dasherized one | Value |
| Humanize | Converts an underscored, camelized, or dasherized text into a humanized one. Also removes beginning and ending whitespace, and removes the postfix ‘_id’. | Value |
| Trim | Trims defined characters from begining and ending of the text. Defaults to whitespace characters. | Value, [characters] |
| Left Trim | Left trim. Similar to trim, but only for left side. | Value, [characters] |
| Right Trim | Right trim. Similar to trim, but only for right side. | Value, [characters] |
| Truncate | Truncate a text after a given number of character, if necessary. Optionally, give a special text to add to the end instead of the text that was remove (”…” for example) | Value, length, [truncateString="…"] |
| Words | Split text by delimiter (String or RegExp), /\s+/ by default. | Value, [delimiter] |
| sprintf | C like text formatting. sprintf("%.1f", 1.17) | Value, Format, … |
| Pad text | pads the text with characters until the total text length is equal to the passed length parameter. By default, pads on the left with the space character (" “). the padding text is truncated to a single character if necessary. | Value, length, [padding], [type] |
| Left Pad text | Same as pad but on the left only. | Value, length, [padding] |
| Right Pad text | Same as pad but on the right only. | Value, length, [padding] |
| Left Right pad text | Same as pad but both sides at once. | Value, length, [padding] |
| To number | Parses a text to return a number. Returns NaN if the text cannot be parsed to a number. | Value, [decimals] |
| Search right | Searches a text from left to right for a pattern and returns a subtext consisting of the characters in the string that are to the right of the pattern or all string if no match found. | Value, pattern |
| Search Back Right | Searches a text from right to left for a pattern and returns a subtext consisting of the characters in the string that are to the right of the pattern or all string if no match found. | Value, pattern |
| Search left | Searches a text from left to right for a pattern and returns a subtext consisting of the characters in the string that are to the left of the pattern or all string if no match found. | Value, pattern |
| Search Back Left | Searches a text from right to left for a pattern and returns a subtext consisting of the characters in the string that are to the left of the pattern or all string if no match found. | Value, pattern |
| Strip HTML tags | Removes all html tags from text. | Value |
| Array To Sentence | Join an array into a human readable sentence. ([“Apples”, “Oranges” and “Bananas”] => “Apples, Oranges and Bananas”) | Array, [delimiter], [lastDelimiter] |
| Array To Serial Sentence | Join an array into a human readable sentence, using the serial comma. ([“Apples”, “Oranges” and “Bananas”] => “Apples, Oranges, and Bananas”) | Array, [delimiter], [lastDelimiter] |
| Repeat | Repeats a text count times. | Value, count, [separator] |
| Surround | Surround a text with another text. | Value, wrap |
| Quotes | Quotes a text. defaults to using “. | Value, quoteChar |
| Unquotes | Unquotes a text. | Value, quoteChar |
| Slugify | Transform text into an ASCII slug which can be used in safely in URLs. Replaces whitespaces, accentuated, and special characters with a dash. Limited set of non-ASCII characters are transformed to similar versions in the ASCII character set such as ä -> a. (Caution: this function is charset dependent) | Value |
| Natural order comparison | Naturally order comparision helps to sort text like humans would do. Numbers are not compared by their ASCII values but by their actual value. Note: this means “a” > “A”. Transform to lower case first if this isn’t to be desired. | Value, Value |
| To boolean | Turn strings that can be commonly considered as booleas to real booleans. Such as “true”, “false”, “1” and “0”. This function is case insensitive. | Value, boolean |
| Upper case | Transforms a text to all upper case | Value |
| Lower case | Transforms a text to all lower case | Value |
| Split text | Splits a text to an array using the provided splitter character | Value, splitter |
| Replace text | Replace a value or pattern (regexp) in the text with another value. The value is replaced only once while a pattern is replaced through the entire text. | Value, pattern, replacement |
| Slice text | Slices text from a begining index to the end of the text or to an optional end index. | Value, start, end |
| Proper Case | Synonym for Titleize. Named as such to please Excel users. | Value |
| Repts | Synonym for Repeat. Named as such to please Excel users. | Value, count, Separator |
| Substring | Return a part of the text from a begining index to the end of the text or to an optional end index. If the start is more than the end, it swaps numbers. | Value, start, end |
| Substring from Start | Return a part of the text from 0 to the ending index provided. | Value, end |
| Substring to the end | Return a part of the text from the starting index to the end of the string. | Value, start |
| Substring from end | Returns a part of the text from an index that is counted from the end of the text, to the end. | Value, startFromEnd |
| Regular Expression | Generates a regular expression object based on the regexp string. Useful for search and replace on advanced use cases. | regexp, flags |
| More than | Checks if the first value is more than the second value. | Value, Value |
| More than or equal to | Checks if the first value is more or equal than the second value. | Value, Value |
| Less than or equal to | Checks if the first value is less or equal than the second value. | Value, Value |
| Less than | Checks if the first value is less than the second value. | Value, Value |
| Between | Checks if the second value is between the first and third values. | Value, Value, Value |
| Between | Checks if the second value is between the first and third values, including either value. | Value, Value, Value |
| Has Value? | If the value contains something, returns a boolean true, otherwise false. | Value |
Dates
| Function | Description | Arguments |
|---|---|---|
| Is Date? | Checks if the value is a date (ISO 8601 format) | Value |
| Is Date & time? | Checks if the value is a date with time (ISO 8601 format) | Value |
| Now | Returns the date and time at the instant of the function call on the server in ISO 8601 format, UTC (Z) | |
| Duration From Dates | Calculates the duration between two ISO 8601 (“YYYY-MM-DDTHH:mm:ssZ”) dates strings. Returns the ISO 8601 duration representation (“PT30M”) | Start Date, End Date |
| Make Event | Builds an ISO 8601 event from a start date and end date | Start Date, End Date |
| Make Event (Start date) | Builds an ISO 8601 event from a start date and a duration | Start Date, Duration (ISO-8601, e.g. PT30M) |
| Make Event (End date) | Builds an ISO 8601 event from a end date and a duration | End Date, Duration (ISO-8601, e.g. PT30M) |
| Make date-time | Builds an ISO 8601 date time string from a date and a time | Date (YYYY-MM-DD), Time (HH:mm:ss) |
| Date only | Builds an ISO 8601 date (“YYYY-MM-DD”) from an ISO 8601 date (“YYYY-MM-DDTHH:mm:ssZ”) | Date and Time (ISO 8601) |
| Make date (from custom format) | Builds an ISO 8601 date time string from a string of text and a format (based on https://momentjs.com/docs/#/displaying/format/) | Date (YYYY-MM-DD), Format |
| Start date from a Calendar Event | Extract the start date (ISO 8601 format) from an event input into a calendar (containing start date, end date and duration) | Event - Date and Duration (ISO 8601) |
| End date from a Calendar Event | Extract the end date (ISO 8601 format) from an event input into a calendar (containing start date, end date and duration) | Event - Date and Duration (ISO 8601) |
| Duration from a Calendar Event | Extract the duration date (ISO 8601 format) from an event input into a calendar (containing start date, end date and duration) | Event - Date and Duration (ISO 8601) |
| Duration to Decimal Hours | Transforms a standard duration (ISO 8601) into a decimal number representing the number of hours and any subsquent 100th of an hour. | Duration (ISO-8601, e.g. PT30M) |
| Today | Returns the date at the instant of the function call on the server in ISO 8601 format, UTC (Z) | |
| Add to date | Add a duration to a date (ISO 8601 format for both) | Date and Time (ISO 8601), Duration (ISO-8601, e.g. PT30M) |
| Subtract from date | Subtract a duration to a date (ISO 8601 format for both) | Date and Time (ISO 8601), Duration (ISO-8601, e.g. PT30M) |
| Start of year | Calculates the start of the year from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| End of year | Calculates the end of the year from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| Start of month | Calculates the start of the month from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| End of month | Calculates the end of the month from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| Start of quarter | Calculates the start of the quarter from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| End of quarter | Calculates the start of the quarter from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| Start of week | Calculates the start of the week from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| End of week | Calculates the end of the week from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| Start of day | Calculates the start of the day from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| End of day | Calculates the end of the day from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| Start of hour | Calculates the start of the hour from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| End of hour | Calculates the end of the hour from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| Start of minute | Calculates the start of the minute from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| End of minute | Calculates the end of the minute from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| Start of second | Calculates the start of the second from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
| End of second | Calculates the end of the second from a given date (ISO 8601 format) | Date and Time (ISO 8601) |
Geographical
| Function | Description | Arguments |
|---|---|---|
| Address Search | Provides with search information from the address provided in a text format. | Value |
| Address Coordinates | Search for the most likely coordinates for an address provided in text form | Value |
| Address Annotations | Search for the most likely coordinates for an address provided in text form. This returns an object containing DMS, MGRS, Maidenhead, Mercator, OSM UN_M49, phone calling code, currency, flag (emoji code), geohash, quibla, roadinfo, sunrise, sunset, timezone, what3words. | Value |
| DMS from Address | Get the most likely latitude and longitude from an address (lat/lng), in the DMS format. | Value |
| MGRS from Address | Get the most likely Military Grid Reference System (MGRS) from an address. | Value |
| Maidenhead from Address | Get the most likely Maidenhead Locator System (QTH, IARU) from an address. | Value |
| Mercator from Address | Get the most likely Mercator projection references from an address. | Value |
| OSM links from Address | Get links for the most likely location for an address, on the Open Street Map site. Two links are provided. One to consult the map and one to submit correction suggestions. | Value |
| OSM note link from Address | Get the most likely page for an address on the Open Street Map site, in order to enter a correction suggestion. | Value |
| OSM link from Address | Get the most likely location for an address on the Open Street Map site. | Value |
| UN M49 from Address | Returns a data object with United Nations M49 informations on the most likely location for the address provided | Value |
| UN M49 Regions from Address | Returns a data object with United Nations M49 region informations on the most likely location for the address provided. | Value |
| UN M49 Statistical Grouping from Address | Returns an array of statistical grouping from an address. (Codes from https://blog.opencagedata.com/post/new-annotation-UN-M49-statistical-codes.) - LDC Least Developed Country, LEDC Less Economically Developed Country, LLDC Landlocked Developing Country, MEDC More Economically Developed Country, SIDS Small Island Developing State | Value |
| Phone code from Address | Returns the phone code for the most likely address. | Value |
| Currency info from Address | Returns informations on the currency for the most likely address. | Value |
| Currency alternate symbols from Address | Return alternate symbols for currencies at an Address | Value |
| Currency decimal mark from address | Returns the standard decimal mark used for currencies at an address | Value |
| Currency html entity from address | Returns the html entity for the currency at the provided address | Value |
| Currency ISO code from address | Returns the currency ISO code for the currency at the provided address | Value |
| Currency numeric ISO code from address | Returns the currency numeric ISO code at the provided address | Value |
| Currency name from address | Returns the currency name from the provided address | Value |
| Smallest currency denomination from address | Returns the smallest currency denomination at the provided address | Value |
| Currency subunit from address | Returns the currency sub-unit (cents) name at the provided address | Value |
| Multipiler currency subunit to unit from address | Returns the multiplier between the currency unit and currency sub-unit at the provided address | Value |
| Currency unicode from Address | Returns the unicode character code for the currency at the provided address | Value |
| Currency symbol position from address | Returns 0 if the currency symbol is before the value or 1 if the currency symbol is after the value for the currency at the provided address. | Value |
| Currency thousands separator form address | Returns the character used for separating the thousands for the currency at the provided address. | Value |
| Unicode Emoticon Flag code from address | Returns the unicode character code for the flag corresponding to the provided address. | Value |
| Geohash from address | Returns the geoash (geocode system) for the provided address. | Value |
| Quibla from address | Returns the quibla (Decimal degrees clockwise from true north to turn to point to the Kaaba) for the provided address. (Calculated using the great circle method). | Value |
| Road info from address | Returns an object containing road information from the provided address. | Value |
| Road side information from address | Returns the side of the road on which vehicles drive for the provided address. (left/right, in english) | Value |
| Speed unit from address | Returns the speed unit (km/h, mph), at the provided address. | Value |
| Sun set/rise info from address | Returns an object with sunset and sunrise information from the provided address. | Value |
| Sunrise info from address | Returns sunrise informations from the provided address. (apparent, astronomical, civil and nautical sunrise information are included in an object) | Value |
| Apparent sunrise info from address | Returns apparent sunrise informations from the provided address. (what is typically reported as sunrise/set) | Value |
| Astronomical sunrise info from address | Returns astronomical sunrise informations from the provided address. (sky is completely dark/light) | Value |
| Civil sunrise info from address | Returns civil sunrise informations from the provided address. (a person can read / no longer read) | Value |
| Nautical sunrise info from address | Returns nautical sunrise informations from the provided address. (navigation using a sea horizon possible/no longer possible) | Value |
| Sunset info from address | Returns sunset informations from the provided address. (apparent, astronomical, civil and nautical sunset information are included in an object) | Value |
| Sunset info from address | Returns sunset informations from the provided address. (apparent, astronomical, civil and nautical sunset information are included in an object) | Value |
| Apparent sunset info from address | Returns apparent sunset informations from the provided address. (what is typically reported as sunrise/set) | Value |
| Astronomical sunset info from address | Returns astronomical sunset informations from the provided address. (sky is completely dark/light) | Value |
| Civil sunset info from address | Returns civil sunset informations from the provided address. (a person can read / no longer read) | Value |
| Nautical sunset info from address | Returns nautical sunset informations from the provided address. (navigation using a sea horizon possible/no longer possible) | Value |
| Timezone info from address | Returns the following information on the time zone at the provided address. (name, now_in_dst, offset_sec, offset_string, short_name) | Value |
| Timezone name from address | Returns the timezone name information at the provided address. | Value |
| DST from address | Returns the current timezone DST information (Daylight Saving Time) at the provided address. | Value |
| Time offset seconds from address | Returns the timezone offset in seconds at the provided address. | Value |
| Time offset from address | Returns the timezone offset at the provided address in a text format ("+0200”) | Value |
| Timezone short name from address | Returns the timezone short name from the provided address | Value |
| What3Words (english) from address | Returns the 3 words address from the provided address. The 3 words are based on the english version of their system. https://what3words.com/ | Value |
| Location components from address | Returns an object with data about the address, including components such as ISO_3166-1, continent, country, county, local_administrative_area, political_union, postcode, and state. | Value |
| ISO_3166-1 alpha-2 from address | Returns the ISO_3166-1 alpha-2 country code from the provided address. | Value |
| ISO_3166-1 alpha-3 from address | Returns the ISO_3166-1 alpha-3 country code from the provided address. | Value |
| Continent from address | Returns the most likely continent from the provided address | Value |
| Country from address | Returns the most likely country from the provided address | Value |
| Country code from address | Returns the most likely country code from the provided address | Value |
| County from address | Returns the most likely county name from the provided address | Value |
| Admin Area from address | Returns the most likely Local Administrative Area from the provided address | Value |
| Political Union from address | Returns the most likely Political Union area name from the provided address | Value |
| Postcode from address | Returns the most likely Postcode from the provided address | Value |
| State name from address | Return the most likely State name from the provided address | Value |
| Formated Address from Address | Returns a formated and validated address version from a search address. This is based on OSM data. | Value |
| Lat/Lng from address | Returns the most likely lat/lng object of decimal coordinates corresponding to the address provided. | Value |
Numbers
| Function | Description | Arguments |
|---|---|---|
| Is a Number? | Checks if the value is a number. | Value |
| Round | Rounds up the number to the precision provided. The precision is the maximum number of significant digits after the whole separator | Number, Precision |
| Ceiling | Rounds a number up to the nearest upper integer. | Number |
| Floor | Rounds a number down to the nearest lower integer | Number |
| Modulo | Calculates the modulo from a number | Number, modulo |
| Average | Averages all the elements and provides a unique value. | … |
| Sum | Sums up all the elements and provides a unique value. | … |
| Subtract | Subtracts all the elements in order, from the first element. | Number, … |
| Multiply | Multiplies up all the elements and provides a unique value. | … |
| Divide | Divides all the elements in order, from the first element. | Number, … |
| Power | Brings the base value to the power of the exponent (b^e). | Base, Exponent |
| Absolute | Returns the absolute value of the number | n |
| Min | Returns the lowest element in value. | … |
| Max | Returns the highest element in value. | … |
| More than | Checks if the first value is more than the second value. | Value, Value |
| More than or equal to | Checks if the first value is more or equal than the second value. | Value, Value |
| Less than or equal to | Checks if the first value is less or equal than the second value. | Value, Value |
| Less than | Checks if the first value is less than the second value. | Value, Value |
| Between | Checks if the second value is between the first and third values. | Value, Value, Value |
| Between | Checks if the second value is between the first and third values, including either value. | Value, Value, Value |
| Percentage | Calculates the percentage of a value with the percentage expressed as an integer 0-100+. | Value, Percentage |
| Add tax % to a value | Add the corresponding % tax to the value. value=10 at percent=20 tax will give the value of 12. This works with any number value. It increases the value by the percentage. | Value, Percentage |
| Progress in percent | Calculates the percentage progress from a value and a target value. 1 out of a target of 2 is 50%. The value returned is a number (no % sign). The value can be more than 100. | Value, Target Value |
| Cross-multiply | Performs a cross multiplication to find the missing input such as a/b = c/d. If a is missing, returns a, if b is missing, returns b, etc. Only one variable can be missing otherwise the function returns null. | a, b, c, d |
| Random | Returns a random number between 0 and 1. | |
| Random Integer | Return a random integer between the max and min numbers provided. Default max = 100 and min = 0. | max, min |
| Formats numbers | Formats numbers to be presented as text. (123456789.123, 5, “.”, “,") => “123,456,789.12300” | Number, decimals, decimalSeparator, orderSeparator |
| Equal Value | Checks if the values are equal | Value, Value |
| Has Value? | If the value contains something, returns a boolean true, otherwise false. | Value |
Financial
| Function | Description | Arguments |
|---|---|---|
| Currency info from Address | Returns informations on the currency for the most likely address. | Value |
| Currency alternate symbols from Address | Return alternate symbols for currencies at an Address | Value |
| Currency decimal mark from address | Returns the standard decimal mark used for currencies at an address | Value |
| Currency html entity from address | Returns the html entity for the currency at the provided address | Value |
| Currency ISO code from address | Returns the currency ISO code for the currency at the provided address | Value |
| Currency numeric ISO code from address | Returns the currency numeric ISO code at the provided address | Value |
| Currency name from address | Returns the currency name from the provided address | Value |
| Smallest currency denomination from address | Returns the smallest currency denomination at the provided address | Value |
| Currency subunit from address | Returns the currency sub-unit (cents) name at the provided address | Value |
| Multipiler currency subunit to unit from address | Returns the multiplier between the currency unit and currency sub-unit at the provided address | Value |
| Currency unicode from Address | Returns the unicode character code for the currency at the provided address | Value |
| Currency symbol position from address | Returns 0 if the currency symbol is before the value or 1 if the currency symbol is after the value for the currency at the provided address. | Value |
| Currency thousands separator form address | Returns the character used for separating the thousands for the currency at the provided address. | Value |
| Round | Rounds up the number to the precision provided. The precision is the maximum number of significant digits after the whole separator | Number, Precision |
| Ceiling | Rounds a number up to the nearest upper integer. | Number |
| Floor | Rounds a number down to the nearest lower integer | Number |
| Average | Averages all the elements and provides a unique value. | … |
| Sum | Sums up all the elements and provides a unique value. | … |
| Subtract | Subtracts all the elements in order, from the first element. | Number, … |
| Multiply | Multiplies up all the elements and provides a unique value. | … |
| Divide | Divides all the elements in order, from the first element. | Number, … |
| More than | Checks if the first value is more than the second value. | Value, Value |
| More than or equal to | Checks if the first value is more or equal than the second value. | Value, Value |
| Less than or equal to | Checks if the first value is less or equal than the second value. | Value, Value |
| Less than | Checks if the first value is less than the second value. | Value, Value |
| Between | Checks if the second value is between the first and third values. | Value, Value, Value |
| Percentage | Calculates the percentage of a value with the percentage expressed as an integer 0-100+. | Value, Percentage |
| Add tax % to a value | Add the corresponding % tax to the value. value=10 at percent=20 tax will give the value of 12. This works with any number value. It increases the value by the percentage. | Value, Percentage |
| Progress in percent | Calculates the percentage progress from a value and a target value. 1 out of a target of 2 is 50%. The value returned is a number (no % sign). The value can be more than 100. | Value, Target Value |
| Payment (PMT) | Payment calculation function based on Excel’s PMT function. Used to calculate the payment for a loan that has constant payments and a constant interest rate. Parameters include: The interest rate for the loan. The total number of payments for the loan. The present value, or the total amount that a series of future payments is worth now; also known as the principal. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. Type: The number 0 (zero) or 1 and indicates when payments are due (start of the period or end of the period). | rate_per_period, number_of_payments, present_value, future_value, type |
Boolean
| Function | Description | Arguments |
|---|---|---|
| To boolean | Turn strings that can be commonly considered as booleas to real booleans. Such as “true”, “false”, “1” and “0”. This function is case insensitive. | Value, boolean |
| Boolean Not | Reverses the value of a boolean value. True becomes false and false becomes true. | Value |
| Either | Checks if either value within the array is truthy. | … |
| All true | Checks if all the values within the array are truthy. | … |
Array
| Function | Description | Arguments |
|---|---|---|
| Is an Array? | Checks if the value is an array | Array |
| Reverse | Returns a reversed array | Array |
| Sort | Sort an array. “desc”, -1, “d” or “>” indicate descending order (Z-A, 99-0), otherwise the order is ascending (A-Z, 0-99) | Array, order |
| Sort Object | Sort an array of objects by a field. “desc”, -1, “d” or “>” indicate descending order (Z-A, 99-0), otherwise the order is ascending (A-Z, 0-99) | Array, Field, order |
| Contains value | Checks if the array contains the specified value. | Array, Value |
| Last item | Returns the last item of the array | Array |
| First item | Returns the first item of the array | Array |
| Nth item | return the nth item of the array. n being the number provided in the index. | Array, index |
| Array Length | Returns the length of the array provided. | Array |
| Ensure array | If the input is an array, returns the same value. If the input empty or null or undefined, it returns an empty array. Otherwise, it returns the value provided, wrapped in an array. | Array |
| Count | Counts the number of items in an array (length of array) or the number of characters in a text or the number of keys in an object or the number of symbols in a number. | Object |
| Compact array | Remove blank and falsy values from the array. | Array |
| Flatten array | Brings all the elements in nested array at the same level, makes one array of values. | Array |
| Array size | Returns the array size. Works only on arrays | Array |
| Deduplicate Values | Returns an array with all the duplicate values in the array removed. All values are unique in the array. | Array |
| Either | Checks if either value within the array is truthy. | … |
| All true | Checks if all the values within the array are truthy. | … |
| Equal Object | Checks if the objects or arrays are the same | Object, Object |
| Natural Sort | Natural Sort of an array. “desc”, -1, “d” or “>” indicate descending order (Z-A, 99-0), otherwise the order is ascending (A-Z, 0-99) | Array, order |
| Natural Sort Object | Natural Sort of an array of objects by a field. “desc”, -1, “d” or “>” indicate descending order (Z-A, 99-0), otherwise the order is ascending (A-Z, 0-99) | Array, Field, order |
Object
| Function | Description | Arguments |
|---|---|---|
| Count | Counts the number of items in an array (length of array) or the number of characters in a text or the number of keys in an object or the number of symbols in a number. | Object |
| Is object? | Checks if the value is an object (javascript/JSON object) | Object |
| Get Field | Get the value of a field from an object. This fonction allows getting sub-fields from inner objects, separated by the ‘.’ notation. | Object, Field |
| Set Field | Set the value of a field in an object. This fonction allows setting sub-fields from inner objects, separated by the ‘.’ notation. | Object, Field, Value |
| Has Field | Checks if a field exists in an object. This fonction allows accessing sub-fields from inner objects, separated by the ‘.’ notation. | Object, Field |
| Stringify JSON | Créates a string of text representing the JSON data. | Value |
| Equal Object | Checks if the objects or arrays are the same | Object, Object |
Need more?
We are happy to add any calculation formula to the platform as long as they can benefit more than one app/user. (We try to avoid over-customizing formula). Please contact us to integrate more calculation capabilities to the platform: [email protected]