Database Design Documentation
Database Design Documentation
Formulas in Ragic work similarly to those in Excel. However, Ragic's formulas are developed by us, meaning that the supported formulas or syntax may not be exactly the same. Especially, when assigning formulas, it references the Field Header directly.
Formulas can calculate not only numbers but also strings and dates. Ragic will automatically determine the appropriate formula type, but it's best to specify the Field Type (such as Number or Date) to prevent unexpected errors.
To assign a formula to a Field Header from your Form Page, navigate to Design Mode and select the Field Header. Go to the Field Settings menu on the left sidebar and enter your formula into the Formula tab.
For example, in the "Sales Order" sheet, if the formula for the "Amount Due" field (A20) is "Total (A17) + Tax (A19)", you would enter "A17+A19" in that cell. Note that the formula should reference the location of the field headers.
There is a fx() icon in the field assigned with the formula.
Clicking on the icon, the system will automatically highlight all the referenced fields of this formula.
For the list of supported formulas in Ragic, please refer here.
Note: The Multiple Select Field Type can only apply specific formulas from the list.
When you don't know how to create formulas, you can specify the rule you want in the Formula Generator. Let AI help you generate it.
Please note：
1. Describe the rules and specify the expected return value for this field. For example: Return today's date.
2. If you want to include text in your formula, enclose it in double quotation marks. For example: "Transaction Date".
3. After configuring the formula, please manually verify whether the results meet the expected outcomes.
To open the Formula Generator interface, click on the Ask AI for formula help below.
Input rules and click Generate Formula.
Below are some default scenarios you can select and enter the fields based on your sheet, including Regular formulas and Approval formulas.
For example, if you want the "Free Delivery" field to return "Yes" when the "Total" is greater than the "Free Delivery Amount," and "No" otherwise, you can choose "If A1 is greater than A2, return 'Y', otherwise return 'N'," and then modify it to match the relevant fields and return values.
After completing, click Generate Formula to generate the corresponding formula below. Click the "copy" icon next to the formula to automatically input the formula.
Operators specify the type of calculation that you want to perform on the arguments of a formula. There is a default order in which calculations are programmed to occur, but you can change this order by using "parentheses ()".
Note: Unlike Excel, Ragic does not allow a colon ":" to be used as a reference operator to combine ranges of cells.
To perform basic mathematical operations such as addition, subtraction, or multiplication and produce numeric results, please use the following arithmetic operators:
| Arithmetic operator | Meaning | Example |
|---|---|---|
| + (plus sign) | Addition | 3+3 |
| – (minus sign) | Subtraction | 3–1 |
| * (asterisk) | Multiplication | 3*3 |
| / (forward slash) | Division | 3/3 |
| ^ (caret) | Exponentiation | 3^2 |
You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE that can be used within conditional formulas.
| Comparison operator | Meaning | Example |
|---|---|---|
| = | Equal to | A1=B1 |
| == | Equal to | A1==B1 |
| > | Greater than | A1 > B1 |
| < | Less than | A1 < B1 |
| > = | Greater than or equal to | A1 > =B1 |
| < = | Less than or equal to | A1 < =B1 |
| != | Not equal to | IF(A1!=B1,'yes','no') |
| <> | Not equal to | IF(A1<>B1,'yes','no') |
To create strings in formulas, you can use either 'single quotes', or "double quotes". In this document, we will use 'single quotes' for consistency, but both formats are acceptable in Ragic.
Below is the list of the supported formulas and categories in Ragic. Please note that the following formulas are case-sensitive.
Multiple Select Field Formulas
Formulas for calculating numerical values and amounts, such as obtaining sums, averages, maximum and minimum values, etc.
| Formula | Description |
|---|---|
| SUM(value1,[value2],...) | Returns the sum of (adds) all the field values. You can also directly use the format value+value2+...to represent it. |
| AVG(value1, value2,...) | Returns the average (arithmetic mean) of all the listed field values. Note that this function also works for Subtables, where the average of all referenced field values will be added to the calculation. |
| AVERAGE(value1, value2,...) | Returns the average (arithmetic mean) of all the listed field values. Note that this function also works for Subtables, where the average of all referenced field values will be added to the calculation. |
| MIN(value) | Returns the smallest number in a set of field values. This function also works for Subtables. |
| MAX(value) | Returns the largest numeric value in a range of field values. This function also works for Subtables. |
| MODE.SNGL(value1,[value2],...) | Returns the most common value in a range of field values. This function works for independent fields, Subtables, and global constants. |
| MODE.MULT(value1,[value2],...) | Returns multiple most common values in a range of field values. This function works for independent fields, Subtables, and global constants. |
| ABS(value) | Returns the absolute value of a number. The absolute value of a number is the number without its sign. |
| CEILING(value,[significance]) | Rounds number up, away from zero, to the nearest multiple of significance. Significance is optional; if not specified, round up to the nearest integer. Example: CEILING(2.5) will return 3; CEILING(1.5, 0.1) will return 1.5. |
| FLOOR(value,[significance]) | Rounds number down, toward zero, to the nearest multiple of significance. Significance is optional; if not specified, round down to the nearest integer. Example: FLOOR(2.5) will return 2; FLOOR(1.58, 0.1) will return 1.5. |
| ROUND(value) | Rounds a number to the nearest integer. |
| ROUND(value,N) | Rounds a number to N decimal place. |
| ROUNDUP(value,N) | Rounds up a number (away from zero) to N decimal place. |
| ROUNDDOWN(value,N) | Rounds down a number (toward zero) to N decimal place. |
| MROUND(number,N) | Rounds a number to the nearest multiple of N |
| SQRT(value) | Returns the square root of a number. |
| COUNT(value1,value2,...) | Returns the total number of field values. Empty values will not be counted when referencing independent fields but will be counted when referencing Subtable fields. |
| PI() | Returns the number 3.14159265358979, the mathematical constant pi, and the ratio of the circumference of a circle to its diameter, accurate to 15 digits. |
| RAND() | Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated. |
| POWER(value,power) | Returns the result of a number value raised to a power. |
| MOD(value,divisor) | Returns the remainder after a number value is divided by a divisor. The result has the same sign as the divisor. |
| GCD(value1,[value2],...) | Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides the specified number of values without a remainder. |
| LCM(value1,[value2],...) | Returns the least common multiple of integers. The least common multiple is the smallest positive integer, which is a multiple of all integer arguments value1, value2, and so on. Use LCM to add fractions with different denominators |
| PRODUCT() | Multiplies all the numerical values in referenced fields (neglecting empty and text values). You can also reference a Subtable field to multiply all the numeric values of that field. |
| PMT(rate, nper, pv, [fv], [type]) | Calculates the payment for a loan.
rate (Required): The interest rate. |
Formulas for obtaining date and time-related data, such as returning the year, month, day, time, or specific workdays. For explanations and applications of some formulas, please refer to this section.
| Formula | Description |
|---|---|
| TODAY() | Returns the current date. In case of automatic daily recalculation, please replace TODAY() with TODAYTZ(). |
| TODAYTZ() | Returns the current date according to Company Local Time Zone in your Account Settings. |
| NOW() | Returns the current date and time. |
| NOWTZ() | Returns the current date and time according to the Company Local Time Zone in your Account Settings. |
| EDATE(start_date, months) | Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. Both "start_date" and "months" are required, and the start_date needs to be a date field. |
| EOMONTH(start_date, months) | Returns the serial number for the last day of the month, which is a specified number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. Both "start_date" and "months" are required, and the start_date needs to be a date field. |
| YEAR() | Returns the year value of a date field |
| MONTH() | Returns the month value of a date field |
| DAY() | Returns the day value of a date field |
| DATE(year,month,day) | Combines values in referenced numeric fields into a date. Please use four-digit years to prevent confusion. |
| WEEKDAY() | Returns the day of the week, using numbers 1 (Sunday) through 7 (Saturday) |
| WORKDAY(start_date,days,["holidays"], ["makeup_workdays"]) | Returns a number that represents a date that is the indicated number of working days before or after a given date. For details, click here |
| WORKDAY.INTL(start_date,days,[weekend_no],["holidays"], ["makeup_workdays"]) | Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. For details, click here |
| NETWORKDAYS(start_date,end_date,["holidays"], ["makeup_workdays"]) | Returns the number of whole working days between a start_date and an end_date. For details, click here. |
| NETWORKDAYS.INTL(start_date,end_date,[weekend_no],["holidays"], ["makeup_workdays"]) | Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. For details, click here. |
| ISOWEEKNUM(date) | Returns the number of the ISO week number of the year for a given date. Every week begins on Monday. |
| WEEKNUM(Date,[return_type]) | Returns the week number of a specific date in that year, you can define which day the week begins. For details, click here. |
| DATEVALUE(date_text, date_format) | Applied on date (time) fields where you can convert a referenced date of a free text field to a date (time) value. For this formula, "date_text" is the date in a free text field that you will be referencing, and "date_format" is the format of the referenced field with the date. For example, if A1 is a free text field with the value “2019/02/01” and you would like to convert it to a value on the date field, you can use the formula DATEVALUE(A1,"yyyy/MM/dd") on the date field to obtain the converted result. |
| HOUR() | There are three ways to use this formula:
1. Setting the parameter as a numerical value between 0-1 will return the number of hours in regards to the proportion of 24 hours defined by the parameter. For example: HOUR(0.5)=12. 2. Setting the parameter as a date field will return the field’s hour value. For example, if field A9’s value is 2020/10/30 18:30:19, HOUR(A9)=18. 3. Setting the parameter as a date will return the hour value. For example, HOUR(“2020/10/13 17:35:22”)=17. |
| MINUTE() | There are three ways to use this formula:
1. Setting the parameter as a numerical value between 0-1 will return the number of minutes in regards to the proportion of 60 minutes defined by the parameter. For example: MINUTE(0.5)=30 2. Setting the parameter as a date field will return the field’s minute value. For example, if field A9’s value is 2020/10/30 18:50:19, MINUTE(A9)=50. 3. Setting the parameter as a date will return the minute value. For example, MINUTE(“2020/10/13 17:35:22”)=35. |
| SECOND() | There are three ways to use this formula:
1. Setting the parameter as a numerical value between 0-1 will return the number of seconds in regards to the proportion of 60 seconds defined by the parameter. For example: SECOND(0.5)=30 2. Setting the parameter as a date field will return the field’s second value. For example, if field A9’s value is 2020/10/30 18:50:19, SECOND(A9)=19. 3. Setting the parameter as a date will return the second value. For example, SECOND(“2020/10/13 17:35:22”)=22. |
| TIME(hour, minute, second) | The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
Hour: A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = 0.125 or 3:00 AM. Minute: A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = 0.520833 or 12:30 PM. Second: A number from 0 to 32767 represents the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = 0.023148 or 12:33:20 AM |
Formulas for obtaining field value strings or checking field content, such as getting string characters, changing case, checking for null values, etc. For explanations of string formulas, please refer to this section.
| Formula | Description |
|---|---|
| LEFT(value,length) | Returns the first character or characters (from the left side) of a text string, based on the number of characters(length) you specify.
Example: If the length is 3, it will return the left most 3 characters. |
| RIGHT(value,length) | Returns the last character or characters (from the right side) of a text string, based on the number of characters(length) you specify.
Example: If the length is 3, it will return the right most 3 characters. |
| MID(value,start,[length]) | Extracts a given number of characters from the middle of a supplied text string. For the starting character, the first character on the referenced field will be specified as 0. For example, if the value on field A1 is ABCD, setting the formula as MID(A1,1,2) on another field will return BC. |
| FIND(find_text,within_text,[start_num]) | Locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. |
| LEN(value) | Returns the number of characters in a text string. |
| UPPER(value)/TOUPPERCASE(value) | Converts all lowercase letters in a text string to uppercase without changing the original string. |
| LOWER(value)/TOLOWERCASE(value) | Converts all uppercase letters in a text string to lowercase without changing the original string. |
| PROPER(value) | Capitalizes the first letter in a text string and any other letters in a text that follow any character other than a letter. Converts all other letters to lowercase letters. |
| SUBSTITUTE(text,old_text,new_text,[instance_num]) | Substitutes new_text for old_text when you want to replace specific text in a text string. |
| TEXT() | Formats a number or date value into a specified format. For details, click here. |
| REPT(value,number_times) | Returns the repeated value a given number of times. |
| SPELLNUMBER(number, [lang]) | You will see numbers that are written in words in some formal documents. For example, use "one hundred" instead of "100". You can use SPELLNUMBER formula if you need to see numbers in words in your sheets. For details, click here. |
| TRIM() | Remove fullwidth and halfwidth spaces at the beginning and the end of a field value. And if there are multiple full-width and half-width spaces between texts, it will only keep the first space. Example: TRIM(" a c ") will get "a c". |
| CHAR(value) | Returns a character when given a valid character code. For example, CHAR(10) returns a line break, and CHAR(32) returns a space. |
| ISBLANK() | Checks whether the referenced field is empty. You can directly reference specified fields or use them in conditional formulas.
For example, ISBLANK(A2) or IF(ISBLANK(A2), 'Y', 'N'). |
Formulas for returning field values based on specific conditions. For detailed explanations, please refer to this section.
| Formula | Description |
|---|---|
| IF(value==condition,value_if_true,value_if_false) | Returns one value if the condition evaluates to TRUE, or another value if the condition evaluates to FALSE. For details, click here. |
| IFS(value=condition1,value_if_true1,value=condition2,value_if_true2,...,true,default value) | Check whether one or more conditions are met and return a value that corresponds to the first TRUE condition. For details, click here. |
| LOOKUP(value,lookup_list,[result_list]) | Searches for the value in a one-column or one-row range (lookup_list), and returns the corresponding value from another one-column or one-row range (result_list). For details, click here. |
| AND(logical1, [logical2], ...) | Returns TRUE if all its conditions evaluate to TRUE; returns FALSE if one or more conditions evaluate to FALSE. For details, click here. |
| OR(logical1, [logical2], ...) | Returns TRUE if any condition is TRUE; returns FALSE if all conditions are FALSE. For details, click here. |
| NOT(logical) | Returns TRUE if the test condition is FALSE, and FALSE if the condition is TRUE. For details, click here. |
| COUNTIF(criteria_range,criteria) | Returns the number of values in a range within a Subtable field that meet a single specified criterion. For details, click here. |
| COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]...) | Applies criteria to fields across multiple ranges and counts the number of times all criteria are met. For details, click here. |
| SUMIF(range,criteria,[sum_range]) | Returns the sum of values in a range that meets a single specified criterion. For details, click here. |
| SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2, criteria2],...) | Adds all arguments that meet specified criteria. For details, click here. |
| UPDATEIF(condition,value_if_true) | Modifies a field value when at least one condition is met. For details, click here |
| MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Returns the maximum value among cells specified by a given set of conditions or criteria. |
| MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Returns the minimum value among cells specified by a given set of conditions or criteria. |
Formulas for obtaining data related to Subtable fields, such as returning a specified entry of a Subtable, retrieving the number of unique or non-empty Subtable rows, etc. For details, please refer to this section.
| Formula | Description |
|---|---|
| FIRST(value) | Returns the first data point of the column in your Subtable. |
| FIRSTA(value) | Returns the first data point that is not empty of the column in your Subtable. |
| LAST(value) | Returns the last data point of the column in your Subtable. |
| LASTA(value) | Returns the last data point that is not empty of the column in your Subtable. |
| COUNTA(value) | Counts the number of Subtable rows where the specified field is not empty. The formula will count if the specified field is not empty, even if other fields in the row are empty. It does not require the entire row to be non-empty. |
| SUBTABLEROW(value,nth_row) | Returns the targeted data of the column in your Subtable, this can only be set in independent fields. |
| RUNNINGBALANCE(value, [allow_backend_formula_recalculation=false]) | Returns the sum of the values in this row and the previous row of the column in your Subtable, used for calculating running balances. "Allow_backend_formula_recalculation=true" means allowing backend formula recalculation for this formula. To use this formula, your Subtable records must be created in the correct order. |
| LARGE(arg, nth, ["arg2"]) | Refers to the Subtable field(s) and checks the ordinal value of one column while returning the value of another column in the same row. The referenced field "arg2" needs to be in the same Subtable as "arg". The formula will sort your entries in descending order in the backend and return the field value of the specified ordinal number. |
| UNIQUE() | Lists the unique values of the referenced Subtable field. For details, click here. |
| UNIQUE().length | Calculates the number of unique values of the referenced Subtable field. For details, click here. |
| VLOOKUP() | Returns the values in the Subtable that meet the specified conditions. For details, click here. |
Formulas for Multiple Select fields. For detailed explanations, please refer to this section.
| Formula | Description |
|---|---|
| INCLUDES_ALL(Multiple select/ image/ file upload field, value1, value2,...) | If all the listed values (which can be of any field type or value) are contained in the options, returns true. For details click here. |
| NOT_INCLUDES_ALL(Multiple select/ image/ file upload field, value1, value2,...) | If all the listed values (which can be of any field type or value) are not contained in any of the options, returns true, equivalent to the inverse of INCLUDES_ANY. For details click here. |
| INCLUDES_ANY(Multiple select/ image/ file upload field, value1, value2,...) | If at least one of the listed values (which can be of any field type or value) is contained in any of the options, returns true. For details click here. |
| NOT_INCLUDES_ANY(Multiple select/ image/ file upload field, value1, value2,...) | If none of the listed values (which can be of any field type or value) is contained in any of the options, returns true, equivalent to the inverse of INCLUDES_ALL. For details click here. |
| ITEMS_COUNT(Multiple select/ multiple image/ file upload field) | Returns the number of values in a multiple select field. For example, if three options are selected in a multiple select field, returns 3; if there are two files in a file upload field, returns 2. |
Assigning formulas to Field Headers simplifies calculations, especially in Subtables, where complex formulas are unnecessary.
Here's an example of calculations in the "Sales Order" sheet:
In the Subtable, the Unit Price (B12) and Quantity (C12) of each item are listed.
Multiplying these values provides the Total Amount (D12=B12*C12) owed for each item.
Then, the subtotal in the Total field (A17) references the total amount of money (D12) in the Subtable.
Formulas can also work on the Subtables themselves. For instance, if you need to count how many rows there are in a Subtable, you can simply create a separate field in your sheet that uses the COUNT() formula.
For more advanced conditional formula types to count or sum up values in Subtables, please see the COUNTIF Function, the COUNTIFS Function, the SUMIF Function, and the SUMIFS Function.
You can use UNIQUE() and UNIQUE().length to find or calculate the number of unique values in a Subtable.
UNIQUE(): Lists the unique values of the referenced Subtable field. If you don't modify the separator in your formula, Subtable values will default to using "," as the separator, resulting in the image below.
If you would like to configure your separator, you should modify your formula to UNIQUE(field,"separator"). For example, you can do UNIQUE(A1,"/"), UNIQUE(A1," "), or UNIQUE(A1,", "). The result would be "Angie/Rex/Lillian", "Angie Rex Lillian", or "Angie, Rex, Lillian" respectively.
UNIQUE().length: Calculates the number of unique values of the referenced Subtable field.
For example:
The VLOOKUP function returns the field value of Subtable rows if a specified condition evaluates to TRUE.
| Formulas | Syntax |
|---|---|
| VLOOKUP | VLOOKUP(value, queryField, returnField, [approximateMatch=true], [findMultiple=false]) |
The VLOOKUP function syntax has the following arguments:
value: Required. The value you want to look up. Can be a number, expression, reference to another field, or a text string.
queryField: Required. The Subtable field where the lookup value is located (A4 field in the below example).
returnField: Required. The Subtable field contains the return value (B4 field in the below example).
[approximateMatch=true]: Optional. The approximateMatch argument specifies whether you want VLOOKUP to find an approximate or an exact match. The default value is "true" (approximate match). Set to "false" if you would like to find an exact match.
[findMultiple=false]: Optional. The findMultiple argument determines whether the returnFiled returns multiple values. The default value is false. If multiple entries may match the criteria, please set it to "true".
Example
If you want to find the teacher of a specific class in the Subtable below:
You may create a new Free Text field for the user to input the query class. Then, create another free text field and apply the VLOOKUP(A9, A4, B4, false, false) or VLOOKUP(A9, A4, B4) formula. It will return the teacher's name according to the query class inputted.
To return multiple values that meet the criteria and perform an approximate match, you can apply "VLOOKUP(A9, A4, B4, true, true)".
A formula referencing Date fields can calculate dates N number of days in the future or past.
For example, if A1 is a Date field, then A1+7 will be the date for 7 days after A1.
Another common use for using dates in calculations would be: if B1 is a birthday, you can set the formula to "(TODAY() - B1)/365.25" to represent the current age.
Check the list of supported formulas for detailed information about formulas that work with dates.
To calculate time differences within a day, you need a Date field (HH:mm format) or a Numeric field for calculations.
For example, if A1 is the start time (HH:mm) and A2 is the end time (HH:MM), there are two ways to calculate the duration from time A1 to time A2 based on the total number of hours:
1. Use a Date field A3 with formatting (HH:mm) by setting up the formula "A2-A1".
2. Use a Numeric field A3 with formatting (0.0) by setting up the formula "(A2-A1)/60".
Note:
(1) The Date fields referenced in the formula cannot be empty; otherwise, the formula will not trigger (a time value cannot be calculated by adding or subtracting an empty value). If fields might be empty, it is recommended to change the field type to a Numeric field to ensure the formula triggers successfully.
Example:
A1 is the "Start Time" (HH:mm), and A2 is the "Duration" (HH:mm). To calculate the A3 "End Time" (HH:mm) using the formula "A2 + A1", if A2 might be empty, the formula for A3 will not trigger, resulting in an empty value for A3. However, if you change A2 to a Numeric field (representing the duration in minutes), A3 will still display its content even if A2 is empty.
(2) If your start and end times span different dates, you will need to use a Date field formatted to include both time and date elements.
For example, if A1 is the start date and time (yyyy/MM/dd HH:mm) and A2 is the end date and time (yyyy/MM/dd HH:mm), you will require a Numeric field for A3 formatted as (0.0) and apply the formula "(A2-A1)*24".
Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). The WORKDAY function takes a date and returns the nearest working day in the future or past, based on an offset value you provide. Working days exclude weekends and, optionally, any dates specified as holidays, but include specified makeup workdays.
Use WORKDAY to calculate invoice due dates, expected delivery times, total days of work performed, or whenever you need to consider working and non-working days.
| Formula | Syntax |
|---|---|
| WORKDAY | WORKDAY(start_date,days,["holidays"], ["makeup_workdays"]) |
The WORKDAY function syntax has the following arguments:
Start_date: Required. A date that represents the start date.
Days: Required. The number of non-weekend and non-holiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as government and floating holidays. The list should be an array constant of serial numbers representing dates.
Makeup_workdays: Optional. An optional list of one or more dates to include in the working calendar, such as a make-up workday on Saturday. The list should be an array constant of serial numbers representing dates.
Example 1:
Apply the formula "WORKDAY(A1,A2,["2017/06/16","2017/06/19"])" to a date field.
When A1 contains the value "2017/06/15", and A2 contains the value "9", the formula will use 2017/06/15 as the start date and calculate a date nine workdays in the future, excluding the identified holidays on 2017/06/16 and 2017/06/19.
The resulting date would be 2017/06/30.
Example 2:
Applying the formula "WORKDAY(A1,A2,["2017/06/16","2017/06/19"],["2017/06/24"])" to a date field.
When A1 contains the value "2017/06/15", and A2 contains "9", with "2017/06/16" and "2017/06/19" specified as non-countable dates, and "2017/06/24" designated as a workday (despite being a Saturday).
Then, the formula will use "2017/06/15" as the start date and calculate a date of nine workdays in the future. It will exclude the identified holidays on "2017/06/16" and "2017/06/19” but include the specified working day on "2017/06/24".
The resulting date would be 2017/06/29.
Note: When using the holidays or makeup_workdays arguments, to avoid setting them individually for each sheet, you can define an array Global Constant for consistent dates across the database and use it in the formula. For more details, please refer to this section.
Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered workdays.
| Formula | Syntax |
|---|---|
| WORKDAY.INTL | WORKDAY.INTL(start_date,days,[weekend_no],["holidays"], ["makeup_workdays"]) |
The WORKDAY.INTL function syntax has the following arguments:
Start_date: Required. A date that represents the start date.
Days: Required. The number of non-weekend and non-holiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
Weekend_no: Optional. If the weekend days are not on Saturday and Sunday, you can use a weekend number that specifies when weekends occur.
Holidays: Optional. An optional list of one or more dates to exclude from the working calendar, such as government and floating holidays. The list should be an array constant of serial numbers representing dates.
Makeup_workdays: Optional. An optional list of one or more dates to include in the working calendar, such as a make-up workday on Saturday. The list should be an array constant of serial numbers representing dates.
Example:
Apply the formula " WORKDAY.INTL(A1,A2,2,["2017/06/16","2017/06/19"])" in a date field.
When A1 contains the value "2017/06/15", and A2 contains the value "9", the formula will use 2017/06/15 as the start date, take Sunday and Monday as the weekend, and calculate a date nine workdays in the future, excluding the identified holidays on 2017/06/16 and 2017/06/19.
The resulting date would be 2017/06/29.
Note: When using the holidays or makeup_workdays arguments, to avoid setting them individually for each sheet, you can define an array Global Constant for consistent dates across the database and use it in the formula. For more details, please refer to this section.
Returns the number of whole working days between the start_date and end_date. Working days exclude weekends and any dates identified as holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
| Formula | Syntax |
|---|---|
| NETWORKDAYS | NETWORKDAYS(start_date,end_date,["holidays"], ["makeup_workdays"]) |
The NETWORKDAYS function syntax has the following arguments:
Start_date: Required. A date that represents the start date.
End_date: Required. A date that represents the end date.
Holidays: Optional. An optional list of one or more dates to exclude from the working calendar, such as government and floating holidays. The list should be an array constant of serial numbers representing dates.
Makeup_workdays: Optional. An optional list of one or more dates to include in the working calendar, such as a make-up workday on Saturday. The list should be an array constant of serial numbers representing dates.
Example 1:
Apply the formula "NETWORKDAYS(E1,E2,['2017/10/04','2017/10/09','2017/10/10'])" in a Numeric field.
When E1 contains the value "2017/10/01" and E2 contains the value "2017/10/31" and the dates "2017/10/04","2017/10/09" and "2017/10/10" are identified to be excluded.
The number of workdays between the start (2017/10/01) and end date (2017/10/31), with the three identified holidays as non-working days ("2017/10/04","2017/10/09", and "2017/10/10") excluded would be 19.
Example 2:
Apply the formula "NETWORKDAYS(E1,E2,['2017/10/04','2017/10/09','2017/10/10'],['2017/10/28','2017/10/29'])" in a Numeric field.
When E1 contains the value "2017/10/01" and E2 contains the value "2017/10/31", with the dates "2017/10/04", "2017/10/09", and "2017/10/10" identified to be excluded, and "2017/10/28 (Sat)" and "2017/10/29 (Sun)" designated as workdays.
The number of workdays between the start date (2017/10/01) and end date (2017/10/31), with the exclusion of the three identified holidays ("2017/10/04", "2017/10/09", and "2017/10/10") but including the specified working days on "2017/10/28 (Sat)" and "2017/10/29 (Sun)", would be 21.
Note: When using the holidays or makeup_workdays arguments, to avoid setting them individually for each sheet, you can define an array Global Constant for consistent dates across the database and use it in the formula. For more details, please refer to this section.
Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered workdays.
| Formula | Syntax |
|---|---|
| NETWORKDAYS.INTL | NETWORKDAYS.INTL(start_date,end_date,[weekend_no],["holidays"], ["makeup_workdays"]) |
The NETWORKDAYS.INTL function syntax has the following arguments:
Start_date and End_date: Required. The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
Weekend_no: Optional. If the weekend days are not on Saturday and Sunday, you can use a weekend number that specifies when weekends occur.
Holidays: Optional. An optional list of one or more dates that are to be excluded from the working day calendar. The list should be an array constant of serial numbers representing dates.
Makeup_workdays Optional. An optional list of one or more dates to include in the working calendar, such as a make-up workday on Saturday. The list should be an array constant of serial numbers representing dates.
Example
Apply the formula "NETWORKDAYS.INTL(E1,E2,11,['2017/06/16'])" in a Numeric field.
When E1 contains the value "2017/06/01" and E2 contains the value "2017/06/30", the 11 argument is used to specify the weekend as Sunday only, and the date "2017/06/16" is identified to be excluded, the formula subtracts 10 nonworking days (four Sundays, one Holiday) from the 30 days between 2017/06/01 and 2017/06/30.
The result is 25 days.
Note: When using the holidays or makeup_workdays arguments, to avoid setting them individually for each sheet, you can define an array Global Constant for consistent dates across the database and use it in the formula. For more details, please refer to this section.
The following number values indicate the following weekend days:
| Weekend number | Weekend day(s) |
|---|---|
| 1 or omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Sunday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
A string formula is straightforward: if the value in C1 is "Michael", and C2 is "Scott", then "C1+C2" will be "MichaelScott". If you want to add a space between two strings, you can use "C1+' '+C2", resulting in "Michael Scott".
For more string formula applications, you can refer to this article.
Note: If you want to represent "\" in a formula, it should be written as "\\".
You can use the combination of RIGHT() or LEFT() with the FIND() function to find a specific character and get the corresponding string values before and after this character.
In the example below, there is a sheet with "Full Name" as the field header in cell A1. We will extract the first and last name of a person using the space character.
Use LEFT(A1,FIND(" ",A1,1)) for the first name,
and RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) for the last name. Notice that we're looking for the space character with the blank space in between quotation marks (" ") with FIND.
The output is the first name and last name that is extracted from the full name.
Ragic supports the use of conditional formulas. Please be reminded that changing the Field Type may affect the calculation results in some situations.
Note: These are the two circumstances in which you'll need to add ".RAW" to the referenced field upon assigning the conditional formulas:
1. When using the operator "=" to reference two fields that equal the condition of the formula. However, when referring to only one field that equals a fixed value, ".RAW" is not needed.
2. When assigning the formula to a Numeric field using the operator "=" to reference a string field(text, selection, date, etc.) that equals a fixed string value (which will return a numeric value as a result). If you are referencing one Numeric field that equals a value with operator "=", ".RAW" is not needed.
For more details about ".RAW" please refer to this section.
Date fields are calculated as days.
Conditional formulas can also be nested.
The IF function returns one value if a specified condition evaluates to TRUE, and another value if it evaluates to FALSE.
| Formula | Syntax |
|---|---|
| IF | IF(value==condition,value_if_true,value_if_false) |
Examples
Basic example: IF(A2==10,10,0)
If the value in the reference field A2 is equal to 10, the value in this field would be 10. For any other value of A2, the value of this field will be 0.
Having a string value as a result: IF(A1==1,'True','False')
If the value in the reference field A1 is equal to 1, the value in this field would be "True". For any other value of A1, the value of this field will be "False".
Practical usage: IF(A2>=60,'Yes','No')
If the age field is greater than or equal to 60, the value in this field "Qualifies for senior discount?" would be "Yes"; otherwise, the value would be "No".
Note:
Usage of the older syntax of the IF function in Ragic is still supported.
Value=='condition'?'value_if_true':'value_if_false'
Basic Example: A1=='open'?'O':'C'
If A1 is open, give O. If not, give C.
If you would like to reference two fields that are equal to each other with the operator "=" as the condition in conditional formulas, please add .RAW after the referenced fields.
| Syntax |
|---|
| IF(field1.RAW=field2.RAW,value_if_true,value_if_false) |
Examples
Basic Example1: IF(A1.RAW=A2.RAW,1,0)
If the value in the referenced field A1 equals the value in field A2, return 1, otherwise return 0.
Basic Example2: IF(A1.RAW=A2.RAW,'Open','Closed')
If the value in the referenced field A1 equals the value in field A2, return "Open", otherwise return "Closed".
| Syntax |
|---|
| IF(string_field1.RAW="string",numeric_value_if_true,numeric_value_if_false) |
Example: IF(A1.RAW="Yes",1,0)
If the value in the referenced string field A1 equals "Yes", return 1, otherwise return 0.
If you are referencing one Numeric field that equals a value with operator "=", ".RAW" is not needed.
Example:IF(A1=1,'YES','NO')
If the value in the referenced field A1 equals 1, return "YES", otherwise return "NO".
If you would like to use a formula to check whether a field is empty or not, your formula must add .RAW after the referenced field.
For example, the formula IF(A8.RAW='',"TRUE","FALSE") is used to check if the field on cell A8 is empty, and that field's value may be 0. Therefore, .RAW should be added.
Note: Without adding .RAW to your referenced field on your formula, the numerical value "0" will also be considered as an empty value.
For example, if the field on A1 is a free text field with the numeric value "10001", and the field on A2 is a linked field with a conditional formula set to reference and return A1's value "10001", the formula would need to be set as the following: IF(A1!='', A1.RAW)
If you would like to retrieve text from referenced fields by using the functions LEFT(), RIGHT(), and MID(), please add +"" after the field you're referencing.
Example:
IF(A1="Yes",A5,LEFT(A5+"",2))
If the value in the referenced field A1 is equal to "Yes", the value in this field would be the field value of A5. For any other value of A1, the value of this field will be the first 2 digits of A5.
Since the system does not support referencing the value of TODAY() or NOW() within an IF() formula directly, you'll need to create another field that references a field containing the value of TODAY() or NOW().
Example:
If you want to compare the value of the date field A1 to TODAY(), you can create field A2 and configure it with TODAY(). Then, apply the formula: IF(A1>A2,"Valid","Expired")
If the value in the reference field A1 is larger than TODAY(), the value in this field would be "Valid". For a value that is smaller than TODAY(), the value of this field will be "Expired".
On the other hand, if you want to use the TODAY() formula or the field assigned the TODAY() formula as the referenced conditional field for calculations in IF() formulas, you can create field A2 for whole calculations as follows: A1-TODAY(). Then, apply the formula: IF(A2>0,"Valid","Expired")
Note: The value of TODAY() or NOW() will not be auto-recalculated once the entries are saved. If it's necessary to recalculate the TODAY() or NOW() value, you'll need to Apply a Daily Workflow.
1. Apply on the non-Date field to compare the values of Date fields
For example, A1 and A2 are date fields, applying TODAY() to A2. In A4, enter the formula: IF(A1>A2,"Yes","No")
2. Apply on Numeric fields to execute addition or subtraction operations for Date fields
For example, it's not supported to enter the formula IF(A1-1>A2,"Yes","No") in A4. You will need to create the other two numeric fields C1 and C2. In C1, enter the formual: A1-1. In C2, enter A2. In A4, change the formula to IF(C1>C2,"Yes","No").
The conditional process in formulas can also be done with the LOOKUP function, which is the equivalent of conditional processing.
| Formula | Syntax |
|---|---|
| LOOKUP | LOOKUP(value,lookup_list,[result_list]) |
LOOKUP searches for the value in a one-column or one-row range (lookup_list) and returns the corresponding value from another one-column or one-row range (result_list).
value: Required. The value to search for in the lookup_range.
lookup_list: Required. An array like [0,100,500]. The LOOKUP function searches for a value in this list, which would need to be in ascending order.
result_list: Optional. An array that is the same size as the lookup_range like ['Small','Medium','Large']. If the result_list parameter is omitted, the LOOKUP function will return the value in the lookup_list. If the LOOKUP function cannot find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value. If the value is smaller than all of the values in the lookup_range, the LOOKUP function will return an empty string.
Examples
Basic Example: LOOKUP(A1,[0,45,65],['Small','Medium','Large'])
The value would be 'Small' if the value of A1 is between 0 and 44, 'Medium' for 45~64, and 'Large' for greater than or equal to 65.
Referencing multiple fields: LOOKUP(A1,[0,45,65],[A3+A4,B5,B6])
The value would be A3+A4 if the value of A1 is between 0 and 44, B5 for 45~45, and B6 for greater than or equal to 65.
Returns TRUE if all test conditions evaluate to TRUE; returns FALSE if one or more conditions evaluate to FALSE.
| Formula | Syntax |
|---|---|
| AND | AND(logical1, [logical2], ...) |
The AND function syntax has the following arguments:
logical1: Required. The first test condition that can evaluate to either TRUE or FALSE.
logical2, ...: Optional. Additional test conditions that can evaluate to either TRUE or FALSE or be arrays or references that contain logical values.
Returns TRUE if any test condition is TRUE; returns FALSE if all conditions are FALSE.
| Formula | Syntax |
|---|---|
| OR | OR(logical1, [logical2], ...) |
The OR function syntax has the following arguments:
logical1: Required. The first test condition that can evaluate to either TRUE or FALSE.
logical2, ...: Optional. Additional test conditions that can evaluate to either TRUE or FALSE or be arrays or references that contain logical values.
Returns TRUE if the test condition is FALSE, and FALSE if the condition is TRUE.
| Formula | Syntax |
|---|---|
| NOT | NOT(logical) |
Example
NOT(A2>10)
If the value in reference field A2 is less than or equal to 10, the system returns "true"; otherwise, it returns "false".
Use COUNTIF to count the number of rows in a Subtable that met a specified criterion; for example, to count the number of times a particular item appears in a receipt.
| Formula | Syntax |
|---|---|
| COUNTIF | COUNTIF(criteria_range,criteria) |
The COUNTIF function syntax has the following arguments:
criteria_range: Required. This range must be a Subtable field to be checked for values that fit specified criteria.
criteria: Required. The criteria that defines which cells in criteria_range will be added. This can be a number, expression, reference to another field, or text string that determines which values will be added. Reference the table below:
| Application | Examples of Formula Input |
|---|---|
| Reference Number | "8" |
| Number Comparison | "> 8"、"< 8"、"!= 8" |
| Reference Text String | "apple" |
| String Inequality | "!='apple'"(Use different quotes outside and inside, for example, double quotes outside and single quotes inside) |
| Reference the Subtable Field | A4 (No need to add double quotes or "="; just write the field name) |
| Comparison with the Subtable Field | "> A4"、"< A4"、"!=A4" |
COUNTIF can only refer to a single Subtable and needs to be set in independent fields.
COUNTIF uses only a single criterion. Use COUNTIFS if you want to use multiple criteria.
Example:
The Formula COUNTIF(A4,'apple') entered field A9 returns the number of rows that Subtable column A4 contains for the product name apple.
| Formula | Syntax |
|---|---|
| COUNTIFS | COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]...) |
The COUNTIFS function syntax has the following arguments:
criteria_range1: Required. This range must be a Subtable field to be checked for values that fit specified criteria.
criteria1: Required. The criteria that defines which cells in criteria_range1 will be added. This can be a number, expression, reference to another field, or text string that determines which values will be added. Reference the table below:
| Application | Examples of Formula Input |
|---|---|
| Reference Number | "8" |
| Number Comparison | "> 8"、"< 8"、"!= 8" |
| Reference Text String | "apple" |
| String Inequality | "!='apple'"(Use different quotes outside and inside, for example, double quotes outside and single quotes inside) |
| Reference the Subtable Field | A4 (No need to add double quotes or "="; just write the field name) |
| Comparison with the Subtable Field | "> A4"、"< A4"、"!=A4" |
criteria_range2, criteria2,...: Optional. Rows are counted when additional criteria_ranges match their associated criteria.
COUNTIFS can only refer to a single Subtable and needs to be set in independent fields.
Use SUMIF to sum up the values stored in a specified Subtable row that meet a single criterion; for example, to sum up the monetary value of a specific merchandise item when it appears in a receipt.
| Formula | Syntax |
|---|---|
| SUMIF | SUMIF(range,criteria,[sum_range]) |
The SUMIF function syntax has the following arguments:
range: Required. This range must be a Subtable field to be checked for values that fit specified criteria.
criteria: Required. The criteria that defines which cells in range will be added. This can be a number, expression, reference to another field, or text string that determines which values will be added. Reference the table below:
| Application | Examples of Formula Input |
|---|---|
| Reference Number | "8" |
| Number Comparison | "> 8"、"< 8"、"!= 8" |
| Reference Text String | "apple" |
| String Inequality | "!='apple'"(Use different quotes outside and inside, for example, double quotes outside and single quotes inside) |
| Reference the Subtable Field | A4 (No need to add double quotes or "="; just write the field name) |
| Comparison with the Subtable Field | "> A4"、"< A4"、"!=A4" |
sum_range: Optional. The actual fields to add, if you want to add values within Subtable fields other than those specified in the range argument. If sum_range is omitted, only the fields that are specified in the range argument will be added (the same fields to which the criteria are applied).
SUMIF can only refer to a single Subtable and needs to be set in independent fields.
SUMIF uses only a single criterion. Use SUMIFS if you want to use multiple criteria.
Example:
The Formula SUMIF(A4,'apple',B4) that is entered into field A9 returns the sum of the values in Subtable column B4, when Subtable field header A4 is the product name apple.
Use SUMIFS to sum up the value stored in a specified Subtable row that meets multiple criteria; for example, to sum up the monetary value of a number of specific merchandise items in specific store locations when it appears in a receipt.
| Formula | Syntax |
|---|---|
| SUMIFS | SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2, criteria2,...) |
The SUMIFS function syntax has the following arguments:
sum_range: Required. This range must be a Subtable field to be checked for values that fit specified criteria.
criteria_range1: Required. criteria_range1 and criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in sum_range are added.
criteria1: Required. The criteria that defines which cells in criteria_range1 will be added. This can be a number, expression, reference to another field, or text string that determines which values will be added. Reference the table below:
| Application | Examples of Formula Input |
|---|---|
| Reference Number | "8" |
| Number Comparison | "> 8"、"< 8"、"!= 8" |
| Reference Text String | "apple" |
| String Inequality | "!='apple'"(Use different quotes outside and inside, for example, double quotes outside and single quotes inside) |
| Reference the Subtable Field | A4 (No need to add double quotes or "="; just write the field name) |
| Comparison with the Subtable Field | "> A4"、"< A4"、"!=A4" |
criteria_range2,criteria2,...: Optional. Additional ranges to be added and their associated criteria.
In the case that you want to apply multiple criteria to a single field, for example, the sum of the A1 field is equal to A or equal to B, you need to use multiple SUMIF() instead of SUMIFS().
SUMIFS can only refer to a single Subtable and needs to be set in independent fields.
Example:
The Formula SUMIFS(C11,B11," > A16",B11," < A17") in the field A18 returns the sum of values Subtable column C11 when the order date (the value B11) is later than that A16 and earlier A17 field.
Use UPDATEIF to retain the old value in the field if the condition is false. The value in a field where the UPDATEIF function is used should change only if the condition being tested by the UPDATEIF Function is true.
| Formula | Syntax |
|---|---|
| UPDATEIF | UPDATEIF(condition,value_if_true) |
Examples:
Basic Example: UPDATEIF(A2==10,10)
If the value in the reference field A2 is equal to 10, the value in this field would be 10. For any other value of A2, the value of this field would remain unchanged from the previously saved version of the record.
Practical Example: UPDATEIF(A2=='Same as home address',A1)
If the value in field A2 is 'Same as home address', the value in the field "Shipping address" would be A1 (home address); otherwise, the value would remain empty.
Conditional formulas can be nested when multiple conditions must be met.
Example 1:
IF(A1==1,'Bad',IF(A1==2,'Good',IF(A1==3,'Excellent','No Valid Score')))
The above formula means that:
if A1 is 1, the result is "Bad".
if A1 is 2, the result is "Good".
if A1 is 3, the result is "Excellent".
if A1 is anything else, the result would be "No Valid Score".
Example 2:
IF( AND(A1.RAW=='YES',A2.RAW=='Jimmy'), C3*C7, IF( AND(A1.RAW=='YES',A2.RAW=='John'), C3*C8, IF( AND(A1.RAW=='YES',A2.RAW=='Jane'), C3*C9, C3*C10 ) ) )
The above formula means that:
if A1 has the value "YES", and A2 has the value "Jimmy", the result is C3*C7.
if A1 has the value "YES", and A2 has the value "John", the result is C3*C8.
if A1 has the value "YES", and A2 has the value "Jane", the result is C3*C9.
if these conditions do not apply, then the result is C3*C10.
In addition to the nested conditional formulas, you can also use IFS() to check whether one or more conditions are met, and return a value that corresponds to the first TRUE condition.
| Formula | Syntax |
|---|---|
| IFS() | IFS(value=condition1,value_if_true1,value=condition2,value_if_true2,...,true,default value) |
The IFS function syntax has the following arguments:
value=condition1: Required. The first condition evaluates to TRUE or FALSE.
value_if_true1: Required. Result to be returned if value=condition1 evaluates to TRUE.
value=condition2: Required. The second condition evaluates to TRUE or FALSE.
value_if_true2: Required. Result to be returned if value=condition2 evaluates to TRUE.
*You need to set at least two sets of conditions. You can apply more if needed.
true: Optional. Please input "true" if you want to set the default value when none of the other conditions are met.
default value: Optional. The result is returned if none of the other conditions are met.
Example:
IFS(A1=1,"Bad",A1=2,"Good",A1=3,"Excellent",true,"No Valid Score")
The above formula means that:
if A1 is 1, the result is "Bad".
if A1 is 2, the result is "Good".
if A1 is 3, the result is "Excellent".
if A1 is anything else, the result would be "No Valid Score".
| Formula | Syntax |
|---|---|
| INCLUDES_ALL | INCLUDES_ALL(Multiple select/ image/ file upload field, value1, value2,...) |
| NOT_INCLUDES_ALL | NOT_INCLUDES_ALL(Multiple select/ image/ file upload field, value1, value2,...) |
| INCLUDES_ANY | INCLUDES_ANY(Multiple select/ image/ file upload field, value1, value2,...) |
| NOT_INCLUDES_ANY | NOT_INCLUDES_ANY(Multiple select/ image/ file upload field, value1, value2,...) |
| ITEMS_COUNT | ITEMS_COUNT(Multiple select/ multiple image/ file upload field) |
Using INCLUDES_ALL as an example, first apply INCLUDES_ALL(D1, A1, A2, B4) to D2.
A1 = Free text field, the field value is "A"
A2 = Multiple select field, the field values are B, C
B4 = Free text field in subtable with two records. The value in the first record is "D"; in the second record is "E"
D1 = Multiple select field, the field values are A, B, C, D, E
D2 will return "true".
Formula calculations will be executed when you edit referenced fields, and the values are saved when you save the entry.
By default, the values that are already saved will not change when you modify the formula while designing your sheet. This is because, in most cases, a previous calculation is still valid for older entries and should not be overwritten when you have updated the formula. A practical example would be calculating taxes after a tax hike; all previous entries would still need to reflect the older tax rate.
In some cases, you may need to recalculate a formula on all previous entries. To do so, you can choose to apply the formula change to all saved records, or, if you have modified more than one formula, apply all formulas on this sheet to all saved records.
Remember to save the design first before recalculating formulas in Design Mode.
Besides manually applying formula recalculation, you can also recalculate through the Workflow. Additionally, if you frequently change formulas or use TODAY(), you may consider using Daily Workflow to recalculate formulas daily.
Note: With workflow formula recalculation, there are two situations where changes will not be recorded in the entry history.
1. No changes are made after recalculation.
2. To optimize system performance, recalculations exceeding 3,500 entries will not be logged in the history (although the recalculation still executes normally).
To run a formula recalculation on related records on other sheets, go to Form Settings > Form Settings > Recalculate all formulas on parent or related sheets.
Parent Sheets:
In the above example, A and B are parent sheets of C.
Related sheets:
B and C are related sheets of A; A and C are related sheets of B.
Note: Currently, the limit number of recalculated records is 1000. If the number of records to be recalculated exceeds the system's limitation, it will ignore the setting to recalculate all formulas on related sheets.
The diagram below shows the design concepts and logic between parent sheets, child sheets, and related sheets.
Formulas also work in Description Fields for display purposes only.
This is useful if you need to recalculate a formula each time your database form is loaded, but do not need to keep this value in your database. You will need to use the BBCode [formula] for your formula to work.
For example, to display a person's age based on their birthday, you can use the formula [formula](TODAY() - A1)/365.25[/formula] in a description field. This formula will continuously update to show the person's age according to the current date.
About the Math Objects supported in Ragic, please refer to this page.
If you need to use other unsupported formulas, please write to Ragic Support to suggest them.
