Design Guide
Design Guide
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. To ensure correct results, set the Field Type according to the type of calculation. For example, use a Numeric or Money field when applying numerical formulas. This allows the system to interpret the formula correctly and return the expected result. If the field type is set incorrectly, the formula may not work as intended.
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.
Note: On-premises Private Server require a parameter to enable this feature. See this section for details.
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.
The following lists all formula categories supported in Ragic. You can refer to each category for detailed formulas and usage examples as needed.
Numeric Calculation Formula: Formulas for calculating numerical values and amounts, such as obtaining sums, averages, maximum and minimum values, etc.
Dates and Times Formulas: Formulas for obtaining date and time-related data, such as returning the year, month, day, time, or specific workdays.
Strings Formulas: Formulas for obtaining field value strings or checking field content, such as getting string characters, changing case, checking for null values, etc.
Conditional Formulas: Formulas that return field values based on specific conditions, for example, returning "Yes" when the condition is met or summing the values of fields that match the criteria.
Subtables Formulas: 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.
Multiple Select Field Formulas: In multiple selection fields (for example, Multiple Select, Multiple Image Upload, or Multiple File Upload), you can apply formulas to check for specific items, find missing ones, or count uploaded attachments.
Approval Formula: Used to return values related to the approval process when an Approval Flow is configured in the sheet.
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.
Thank you for your valuable feedback!
