AnyDB Formulas
Introduction to Formulas
Formulas in AnyDB allow you to perform dynamic calculations, automate data processing, and create insightful reports. By leveraging references and expressions, you can transform your databases into powerful analytical tools.
Creating a Formula
To create a formula:
- Select a Cell: Click on the cell where you want to insert the formula.
- Enter Formula: Type
=followed by your desired formula. - Press Enter: Your calculation will be executed immediately.
Example
Typing =A1 + B1 in a cell will add the values from cells A1 and B1.
References in Formulas
References allow you to pull data from specific cells, items, or attributes within AnyDB.
Basic References
| Reference Type | Syntax | Description | Example |
|---|---|---|---|
| Cell | A1 | Refers to a specific cell's position. | =A1 |
| Badge | B@BADGE_NAME | Refers to the value of a specific badge. | =B@Priority |
| Prop | P@PROP_NAME | Refers to the value of a specific prop of a cell. | =A1!P@BACKGROUND_COLOR |
| Current Cell | CURRCELL | Refers to current cell or badge. Useful for use in conditional properties | =CURRCELL > 100 ? '#FF0000' : '#00FF00' |
| Item Title | M@NAME | Refers to the title of an item in AnyDB. | =M@NAME |
| Item Assigned To | M@ASSIGNED | Refers to the user assigned to the item. | =M@ASSIGNED |
| Item Follow-up Date | M@FOLLOWUP | Refers to the item's follow-up date. | =M@FOLLOWUP |
| Item Status | M@STATUS | Refers to the status (open/closed) of the item. | =M@STATUS |
| Item Template Name | M@TEMPLATENAME | Refers to the template name of this item | =M@TEMPLATENAME |
| Item Template ID | M@TEMPLATEID | Refers to the template ID of this item | =M@TEMPLATEID |
Advanced References
Advanced references let you interact with hierarchical data, such as child items, and perform complex calculations.
| Reference Type | Syntax | Description | Example |
|---|---|---|---|
| Children of Current Item | C@CURRDOC!REF | Refers to all children of the current item with a specified reference. Returns an array. | =mean(C@CURRDOC!A1) returns the average of all A1 values in child items. |
| Children with Specific Template | C@CURRDOC!E@TEMPLATEID!REF | Refers to children of the CURRDOC item using a specific template. Returns an array. | =mean(C@CURRDOC!E@394834!A1) averages A1 values in children with a specific template. |
| Children of a Specific Item | C@CURRDOC!REF | Refers to children of a specific item using the item ID. Returns an array. | =mean(C@2392983!A1) averages A1 values of children under item ID 2392983. |
| Parent of Current Item | A@CURRDOC!REF | Refers to all Parents of the current item with a specified reference. Returns an array. | =mean(A@CURRDOC!A1) returns the average of all A1 values in parent items. |
Object and Array Access
When dealing with arrays of objects, you can access specific elements using array indexing.
Syntax: C@26a1!A2[1].name
This returns the name attribute of the first object in the A2 array.
Expression Syntax
AnyDB formulas support a wide range of expressions to perform calculations and logical operations.
| Operator | Name | Description | Example |
|---|---|---|---|
() | Grouping | Groups operations for precedence. | (5 + 6) * 2 |
[] | Array Index | Accesses an item in an array (1-based indexing). | A1[1] |
+ | Add | Adds two values. | 3 + 3 |
- | Subtract | Subtracts one value from another. | 6 - 3 |
* | Multiply | Multiplies two values. | 10 * 3 |
/ | Divide | Divides one value by another. | 6 / 2 |
^ | Power | Raises a number to the power of another. | 3 ^ 3 |
! | Factorial | Returns the factorial of a number. | 4! |
and | Logical AND | Returns true if both conditions are true. | A1 == 3 and B1 == 4 |
or | Logical OR | Returns true if either condition is true. | A1 == 3 or B1 == 4 |
not | Logical NOT | Returns the opposite of a condition. | not A1 == 3 |
?: | Conditional Expression | Returns one value if a condition is true, another if false. | A1 > 5 ? "Yes" : "No" |
: | Range | Specifies a range of cells. | A1:A10 |
== | Equal | Checks if two values are equal. | A1 == 5 |
!= | Not Equal | Checks if two values are not equal. Blank strings are always 0 value, so you will have to check if non-zero. | A1 != 5 |
> | Greater Than | Checks if one value is greater than another. | A1 > 5 |
< | Less Than | Checks if one value is less than another. | A1 < 5 |
>= | Greater or Equal | Checks if a value is greater than or equal to another. | A1 >= 5 |
<= | Less or Equal | Checks if a value is less than or equal to another. | A1 <= 5 |
Object Syntax
Formulas can return objects with named properties.
Syntax:
{ property1: value1, property2: value2 }
Example:
A1 = { name: "John", age: 30 }
To access these properties, use A1.name or A1.age.
Available Functions
Text Functions
- LEN - Returns the length of a text string.
- CONCAT - Joins several text strings into one text string.
- TRIM - Removes extra spaces from text.
- LEFT - Returns the specified number of characters from the start of a text string.
- RIGHT - Returns the specified number of characters from the end of a text string.
- MID - Returns a specified number of characters from the middle of a text string.
- REPLACE - Replaces part of a text string with a different text string.
- SUBSTITUTE - Replaces existing text with new text in a text string.
- UPPER - Converts a text string to uppercase.
- LOWER - Converts a text string to lowercase.
- PROPER - Capitalizes the first letter in each word of a text string.
Geographic Functions
- CONTINENTS - Returns a comma seperated list of continents based on a location.
- COUNTRIES - Returns a comma seperated list of countries based on a location.
- STATES - Returns a comma seperated list of states based on a location.
- LANGUAGES - Returns a comma seperated list of languages based on a location.
Mathematical Functions
- SUM - Adds all the numbers in a range of cells.
- MAX - Returns the largest value in a set of values.
- MIN - Returns the smallest number in a set of values.
- MEAN - Returns the average of its arguments.
- POWER - Returns the result of a number raised to a power.
- ROUND - Rounds a number to a specified number of digits.
- SQRT - Returns the square root of a number.
- FLOOR - Rounds a number down to the nearest integer.
- CEILING - Rounds a number up to the nearest integer.
- PMT - Calculates the payment for a loan based on constant payments and a constant interest rate.
Date and Time Functions
- TODAY - Returns the current date.
- DATEVALUE - Converts a date in text format to a date value that AnyDB recognizes.
- DAYS - Returns the number of days between two dates.
- DAY - Returns the day of the month from a date.
- MONTH - Returns the month from a date.
- YEAR - Returns the year from a date.
- HOUR - Returns the hour from a time.
- MINUTE - Returns the minute from a time.
- SECOND - Returns the second from a time.
- WEEKDAY - Returns the day of the week from a date.
- WEEKNUM - Returns the week number of the year from a date.
- DATEADD - Adds a specified number of days, months, years, hours, minutes, or seconds to a given date.
- DATESUBTRACT - Subtracts a specified number of days, months, years, hours, minutes, or seconds from a given date.
- ISODATE - Checks if a text string is a valid ISO date.
- ISMONTH - Checks if a date is within a specific month.
- ISYEAR - Checks if a date is within a specific year.
- DATEDIF - Calculates the number of days, months, or years between two dates.
- DATE - Returns the serial number of a particular date.
- YEARFRAC - Returns the year fraction representing the number of whole days between start_date and end_date.
- READABLEDATE - Returns date as a easy to read date
- READABLEDATETIME - Returns date time as a easy to read date and time
Conditional and Logical Functions
- COUNTIF - Counts the number of cells within a range that meet the given criteria.
- COUNTIFS - Counts the number of cells within a range that meet multiple criteria.
- SUMIFS - Adds the cells in a range that meet multiple criteria.
- COUNTNIF - Counts the number of cells that does not meet the given criteria.
- GROUPBYSUM - Merges objects in an array that share a specified property, summing numerical values
- MAXBY - Returns the max value based on a criteria.
- SUMBY - Sums the values of a specified property in an array of objects.
- FILTER - FIXME Filters a range of data based on specified criteria.
- COUNT - Counts the number of cells that contain numbers.
- IF - Specifies a logical test to perform.
- IFERROR - FIXME Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.
Special Functions
- DYNREF - Returns the value of a cell pointed to by a reference item
Need Help?
For additional assistance with AnyDB formulas, please contact our support team or visit our community forums. We're constantly adding new functions to make your data management experience even better!