Listing Pages and Form Pages are the unique advantages of Ragic compared to other spreadsheet software, and they are very important concepts in usage.

Listing Pages：Provides a summary overview of all data in the sheet.

Form Pages：Displays detailed information of a single data/record.

The Listing Page and the Form Page are two sides of one sheet. Adding data on either side will synchronize to the other side. When entering the sheet, you will first see the Listing Page. Clicking on a record will lead you to the Form Page for detailed information.

In the example below, when you enter the "Sales Order" sheet, it will lead you to the Listing Page. From there, you can browse multiple sales orders. Clicking on one of the records will take you to the Form Page for detailed information.

Designing the Form Page

When modifying sheet design, it will start from the Form Page. You can create fields under the Form Page design mode.

There must be at least two independent fields to save the design.

You will be able to create Subtable fields only after the independent fields are created.

Designing the Listing Page

When creating the sheet, there is usually no need to separately design the Listing Page. When you save the Form Page, the Listing Page will be automatically generated, arranging all fields from the Form Page horizontally in the Listing Page format. Please do not add fields outside of the first row.

To access the Listing Page design mode, you can click on "Change Design" on the Listing Page.

While in the Form Page design mode, you can click the arrow at the top-right corner to access the Listing Page design mode.

To explore more detail about creating your own Ragic Sheet, please head here.

Listing Page Field Picker

In Listing Page design mode, you can use the Listing Page Field Picker to decide which fields will appear on the Listing Page and their order. If there are numerous fields, use the filter tool at the top to find the desired fields.

"Gray-background fields" within the Listing Page Field Picker are automatically generated by the system. You can also add them to the Listing Page if needed.

Updating Field Names on the Listing Page

When modifying field names on the Form Page that also exist on the Listing Page, after saving, the system will ask you whether to sync the names of the fields on the Listing Page.

Note: If you choose not to update the names of these fields, the system will not display the message when you modify the names of the same fields next time.

Video Tutorial

To get clearer concepts of Listing Pages and Form Pages, you can refer to the following tutorial video.

On the web interface, you can modify the layout and field order of both the Listing and Form Pages on the Ragic mobile app.

Modify the Layout of the Listing Page

To modify the layout of the Listing Page on the Ragic mobile app, go to the Listing Page > Change Design > Form Tools > Mobile app layout setting.

After clicking on Mobile app layout setting, you will see two sections. In the upper part, You can select six fields and place them in corresponding positions. The bottom part shows the preview of your current layout.

Remember to save your settings before exiting the design mode.

The circle on the left will only display the first letter of the referenced field value. If there is an image field on your sheet, you can place it here to display its thumbnail.

After saving, you will see the configured layout in the app.

Modify the Layout of the Form Page

The Ragic mobile app's Form Page displays fields vertically. By default, it follows the sequence of fields in the "web interface" from left to right and then from top to bottom.

To change the order of fields, go to the Form Page > Change Design > Form Tools > Mobile app layout setting.

You can rearrange the order of both independent field and Field Description through the arrow button on the right side. The positions of Subtables cannot be changed and will remain at the bottom of the Form Page.

You can also restore to default layout.

There are four ways to create a new sheet：

1. Creating a Ragic sheet from scratch, please follow step 1 to 5.

2. Installing Templates, such as Customer Relationship Management, Project Management, Order Management, Procurement, Inventory, etc. Most templates include multiple sheets with established linking relationships, providing a complete design for certain applications.

3. Applying Quick Templates, which are independent sheets with relatively basic structure.

4. Importing existing spreadsheet to generate Ragic sheets.

We strongly recommend new users to create a sheet from scratch to acquire basic knowledge for future adjustments.

The Ragic database consists of three levels: Tab, used for classifying sheets; Sheet, used for storing records; and Record, which is composed of multiple fields.

Step 1. Creating a Tab

First, click the "+" on the upper-right of the page.

Then, click the "+" next to the tab to add a new one.

If you want to add a new sheet to an existing tab, you can skip this step.

Step 2. Adding a New Sheet

Choose the tab where you want to add a new sheet, then name your sheet under the "Design New Sheet" option.

Next, click "Create", and it will take you to Form Page design mode.

Step 3. Creating Fields

In the Form Page design mode, start by typing in the field names that you would like to create. Each independent field takes up two columns： the left column is for the "field name" ("field header"), and the right column is for the "field value". When adding new fields, place them in empty spaces and arrange them vertically.

You can also right-click on an empty column to add a new field or Field Description.

You can select the Field Types for different purposes. For example, if the field value is a category, you can choose Selection or Multiple Select. There are various Field Types available, including Date, Number, Money, etc. For all supported Field Types, please refer to this section.

You can also quickly create independent fields and Field Descriptions by Copying (right-click and select "Copy", or Ctrl+C). It duplicates not only the field name but also all the "field attributes", including the Field Type, Formula, Style, etc.

After creating fields, you can adjust them by clicking on the field header and modifying in the field settings on the left sidebar.

When you want to delete a field, click on the field header, then press the Spacebar, Delete key, or Backspace key.

Note: If the field is referenced by other sheets, deletion will be prohibited. Please refer to "the reasons for not being able to delete specific sheets or fields" for more information.

Step 4. Adjusting Field Width and Height

You can adjust the height and width of rows or columns by dragging the borders, just similar to Excel.

After completing the design, click "Save" and then exit the design mode to start using your sheet!

Step 5. Adjusting Displayed Fields in the Listing Page

Click the arrow at the top left of the Form Page to return to the Listing Page, where you will see the created fields.

Click on "Change Design" to access the Listing Page design mode. Just as on the Form Page, you can drag the borders to adjust the height and width of the fields.

You can use the Listing Page Field Picker to rearrange the displayed fields. For more details on Listing Page settings, please refer here.

Remember to save design before exiting.

Video Tutorial

For a clearer demonstration, you can refer to the following tutorial video.

Quick Templates

When you create a new sheet based on the above steps, you will see the Quick Templates on the right side.

You can click on each template to preview the design.

There is a "Categories" menu to help you quickly find suitable templates.

After applying the template, you can still adjust the design according to your needs, such as adding, deleting, or moving fields.

You can also access the Quick Templates from the Form Tools.

Install Templates

When creating a new sheet, you can select the Install Template. After clicking "Create", you can install several pre-designed templates, such as Customer Relationship Management, Sales Order Management, Purchasing, Inventory, etc.

Choose the category from the left sidebar, and clicking on a template will display its description. Select the one you need and click "Install" (if the template has already been installed, it will display "Update").

For instructions on using the templates, please refer to this page.

There are two other ways to access Install Templates:

1. From the hamburger menu in the top left corner.

2. From the personal homepage in the top right corner.

For instructions on modifying template designs, please refer to this article.

Creating a New Sheet by Importing Your Data

Please refer to this section for detailed instructions.

Entering or Importing Your Data

After completing the sheet design, the next step is to add new entries.

If you already have data in a spreadsheet, you can also import your data into the sheet.

Deleting a Sheet or Tab

To delete a sheet or tab, hover your cursor over the sheet or tab you want to delete then right-click. Select "Delete" from the menu.

Note: If the sheet contains referenced fields from other sheets, deletion will be prohibited. Please refer to "the reasons for not being able to delete specific sheets or fields" for more information.

If you accidentally delete a sheet or tab and want to recover it, please check the Recycle Bin.

When designing sheets, Ragic supports various Field Types, determining the format in which users input data, such as Free Text, Selection, Numeric, or File Upload, etc. By default, fields are set to Free Text, allowing users to enter any kind of data without restrictions. However, selecting the appropriate Field Type can effectively manage data and prevent errors. For example, setting a field to accept only numeric values can reduce errors from accidental text entries. The Field Type can be set from the left sidebar under the Basic tab in Design Mode.

We will introduce all the Field Types in Ragic below:

Free Text

The default Field Type allows users to input any text they want without length restrictions. The input text is regarded as "strings".

It is commonly used for fields like product names, names, etc.

Selection

Users can select previously defined field values from a dropdown menu, typically used for categorization, grouping, or departments, etc.

These field values can be added in the Choices box while in Design Mode.

When there are many choices to choose from, this field can also work as a "text filter", displaying available options as the user types.

You can also set a Default Value or "allow users to add options" when entering data.

If you need to create subcategories or display results connected to a previous selection field, you can set Cascaded Selections.

Set Option Colors

For data identification, you can also set option colors. After selecting the Set Option Colors option below, there will be a default color icon next to each option. You can click on the icon to change the display color.

Then, each option will be displayed according to the set color.

Multiple Select

Users can select multiple values from the dropdown menu of previously defined field values in the Choices box in Design Mode.

For easier identification of each option, you can refer to the Set Option Colors section of the Selection field.

Checkbox

This Field Type works similarly to the Selection field but displays options with icons. Users can quickly switch between options by clicking on the icon.

By default, there will be two options: Yes and No.

You can include their sorting order within parentheses ( ). This will number the icons as a prefix next to the icon name. The text in ( ) will not be displayed.

Note: The values of checkbox fields are treated as "text". Therefore, when applying formulas or setting default values, you need to specify the text corresponding to the checkbox option.

For more supported icons, please refer to the table below:

Options	Icon
Yes
No
X
Important
Star
Green
Yellow
Red
Blue
Black
White
Orange
Purple
Lightgreen
Darkblue
Toggle-On
Toggle-Off
Thumbs-Up
Thumbs-Down
Lock
Unlock
Plus
Minus
Smile
Meh
Frown
Circle
Dot-Circle

Select From Other Sheet (Linked Field)

Users can select values that are referenced from another sheet when entering data into this field. For example, in the "Customer ID" field of the "Sales Order," select data that is already saved in another "Accounts" sheet.

You can select the sheet and the field to be linked from Design Mode, but please note that the values in the linked source field must be the Unique Value.

The following Field Types are blocked from being selected as a link source:

User type fields: Select user, Select group, Reviewer fields.

Selection type fields: Selection, Multiple select, Checkbox, Linked fields.

Numeric type fields: Numeric, Money, Percentage, Exchange rate, Statistics fields.

The Select from other Sheet field can also work like a "text filter", displaying available options from the source sheet as you type values into it.

If you need to load other values when selecting a value from another sheet, you can use Linking and Loading Fields.

Multiple Select

You can also configure Select from other sheet field as Multiple select.

For example, the Members field (a Select from other sheet field) in the Project Report sheet is linked to the Employee Management sheet and is set as multiple select. With this configuration, the Members field will display a drop-down menu that enables multiple selections.

Note: If the multiple select option is checked, the linked field will no longer support loaded field configuration.

If the corresponding loaded fields are already set for the linked field, the setting will be automatically canceled once the multiple select option is checked, but the original field values will not be affected.

We currently limit the number of selections to 2000. If the number of records to select from the source sheet exceeds 2000, only the first 2000 records will be displayed in the selection list. If you need to include over 2000 selections, please contact support@ragic.com and briefly describe your requirement so we’ll be able to evaluate the plausibility of adjusting the limit.

File Upload

To upload a file, you can select it from your computer or drag and drop it into the field. The field values will appear as links for downloading.

The maximum size limit for a single file is 1GB. If you want to restrict the file types for user uploads, you can enter the acceptable file extensions into the Filename Extension Filter. For example, if you want to limit users to uploading only Word files, enter ".docx". If there are more than two file types, please separate them with a comma ",".

If you upload an audio or video file, Ragic will automatically display a media player for streaming supported files.

Audio streaming supports .mp3 and .ogg files.

Video streaming supports .mp4, .ogv, and .webm files.

While integrated media streaming is provided, the uploaded files can still be downloaded by clicking on the icon.

If you'd like to upload files to multiple existing records, you can use the Mass File Upload feature.

Disable Downloading Files

You can choose "Disable downloading Microsoft Office files or PDF files". In this case, users will only be able to preview Microsoft Office or PDF files from the right panel.

Multi-files Upload

To upload multiple files to one field, you can check this option.

You can select the files from your computer or drag and drop them into the field. To download all the uploaded files, please click on the download icon.

Limit File Size

You can set the maximum size of each file, the maximum size limit currently supported is 1 GB.

Image Upload

To upload an image, you can select it from your computer or drag and drop it into the field. It will then be displayed as a thumbnail, and users can view and download the image in a lightbox image gallery by clicking on the thumbnail.

If you'd like to upload images to multiple existing records, you can use the Mass File Upload feature.

Image Display

When you upload an image to an Image Upload field, by default, the system will maintain the original field width but will slightly expand the field height to avoid distorting the image. This will also cause the height of other fields on the same row to expand.

If you don't want the height of other fields on the same row to be affected, you can configure advanced settings under Design Mode. There are two options: "Stretch/shrink to fit cell" and "Image can overflow beyond cell".

(1) Stretch / Shrink To Fit Cell

The image will be automatically resized to fit the cell, therefore the height of other fields on the same row will not expand.

(2) Image can overflow beyond cell

The image will be displayed in its actual aspect ratio regardless of the cell borders. If you do not set the "max width" or "max height", the image height will be 120 px.

In this mode, the field height in the same row will not expand, but the cells below the field may be covered by the image. Therefore, you will need to adjust the layout to avoid this.

Please note that "Stretch/shrink to fit cell" and "Image can overflow beyond cell" work in different mechanisms and that errors might occur if you check both options in the same image upload field.

Other Advance Setting

If you do not choose to "stretch/shrink to fit cell", the max height of the thumbnail will be 120 px, and you can fill in the "Max Height" and "Max" to resize the thumbnail.

If you check the "Hide Field Header" box, the field header will be hidden, and the image will align to the upper-left corner of the header's original position.

Multi-images Upload

To upload multiple images to one field, you can check this option.

You can select the images from your computer or drag and drop them into the field as well.

Limit File Size

You can set the maximum size of each file, the maximum size limit currently supported is 1 GB.

Image Orientation

When uploading multiple images, you can set their orientation in the Listing Page and Form Page to be "Vertical" or "Horizontal".

Select User

Choose from a preset list of account users.

It's also possible to specify a certain user group that this user can be selected from.

If you choose Set selected user as entry owner, the person that is chosen as the value for this entry will have the same Access rights as the person who created the entry.

Please view this document on Assigning an entry to a user for more information.

When importing data into a Select User field, the imported values must exactly match the emails or usernames in your database for the system to automatically match them to the respective users.

Select User Field is linked to the system's users sheet, so you can apply Dynamic Filtering and Link & Load sync to it.

Multiple Select

If you enable Multiple select in Design Mode

Then users can select multiple users. If select a group, all users within that group will be selected. Deselecting the group will also deselect all users within it.

If you want to set multiple default values, please refer to this document.

Select Group

Lists the groups from this account to choose from. If you choose Set selected group as entry owner, all users in the selected group will have the same Access rights as the person who created this entry.

Please view this document on Assigning an entry to a user for more information.

It can also allow multiple selections. If you want to set multiple default values, please refer to this document.

Date

Specifies that the selected field is a date. A Date Picker will appear automatically when the user clicks on the field.

You can also choose a date format from the formatting options listed under Format Type in Design Mode.

Note: If you also have time formatting applied, the Date Picker will allow you to set both options.

Display Using Browser’s Time Zone

Ragic's default date field is a fixed time. If the field value is 9/15 00:00, the field value would be the same no matter which time zone you are in.

When selecting "Display Using Browser’s Time Zone", the field value will be different depending on the users' browser time zone. If the user located in the UTC time zone enters "9/15 00:00", this field value will display as "9/15 08:00" for the user in the UTC+8 time zone or "9/14 20:00" for users in the UTC-4 time zone.

With this option checked, users located in different time zones won't need to calculate the time differences and will present a more accurate time sorting.

Note:

1. Without enabling "Display Using Browser’s Time Zone", the time saved in the database won't belong to any time zone. If you enable this setting afterward, the field value will be regarded as UTC+0 time. For instance, if you are in the UTC+8 time zone and entered the field value 2022/06/10 00:00 when "Display Using Browser's Time Zone" is not enabled, the field value will become 2022/06/10 08:00 if you enable this setting afterward.

2. When enabling "Display Using Browser’s Time Zone", the field value will be saved as UTC time and display different values depending on the users' browser time zone. If this setting is disabled afterward, the field value will be displayed in the UTC time zone.

3. "Display Using Browser’s Time Zone" is not supported on reports yet, so the date and time values will still display using the database's UTC time.

Display as Countdown

When selecting "Display as countdown", the filed value will be displayed using countdown.

Clock In

Check the box "Clock In" will give users the option to clock in the present time when editing the field value.

Formulas can be applied to the date field to calculate dates.

Another common usage for the date field is to create a timestamp. This can be done by assigning a Default Value to the date field.

Date fields can also help you remember dates by setting a Reminder or syncing with your favorite calendar application.

Recurring Date

You can use this Field Type to set a cyclic Reminder to automatically send birthday e-mails to customers on a specific day every year, or payment requests to clients every month.

The recurring Cycle includes Yearly, Monthly, and Weekly.

If you choose the Yearly cycle, the recurring value will be the specific month and day of each year (e. g. May the 4th of every year). For the Monthly cycle, the value will be the specific date in every month (e. g. the date 13 in every month). As for the Weekly cycle, the value will be the specific day of the week (e. g. every Monday).

If you set a Reminder with a recurring date on the 31st of every month, the Reminder will not be executed on the months that do not have a 31st.

About recurring date fields in the event calendar, please refer to this page.

Numeric

Specifies that the selected field is a numeric value.

You can also choose a format from the formatting options listed under Format Type in Design Mode.

Numeric fields are most used with Formulas.

Large Number Abbreviation

When dealing with large numerical values, you can select the option below for Large Number Abbreviation. If the input value exceeds the selected threshold, it will be abbreviated.

For example, when the "K" option is selected for the "Qty field", the field will display "2K" when the quantity is "2000".

Percentage

Specifies that the selected field is a percentage value.

Percentage fields are most used with Formulas.

Background Progress Bar

Checking the “Show background progress bar” option below the percentage field will turn the field into a progress bar.

After clicking the Set button next to the "Show background progress bar" option, a window will appear where you can adjust the progress bar settings by dragging separators to define percentage ranges. For example, if you want to highlight percentages above 70% in scarlet red, you can drag the separator from 75% to 70%.

When clicking on different percentage ranges on the progress bar, you can add separators, and set the text color and background color for the ranges.

If you want to reset the progress bar, simply click on the "Reset button" and the progress bar will be reset to default settings.

Auto Generate

The value of this field will be automatically generated. This is a great help for generating system IDs and serial numbers.

When clicking on a field to enter data, users will see the Auto Generate option.

Ragic will generate a number or string according to your formatting.

You can choose from a list of formatting options provided in the Design Mode or customize the values by writing your filing codes.

You can also set Auto Generate Field to generate sequence numbers based on the specified Selection field or Date field value.

For example, you would like to generate sequence numbers for sales orders based on sales groups to categorize.

For more information about this field, please see the Auto Generated Field Values section.

Signature

In this field, a signature can be added via mouse or trackpad.

You can also upload the image file of a signature or insert a link to the URL of the image or load a saved signature in Personal Settings.

In Design Mode, the Signature Field has the same advanced settings as the Image Upload Field.

Rich Text Editor

This Field Type gives users text formatting options, as well as the ability to insert images, tables, and links with a pop-out menu. There is also space for content editing and style notes.

By default, the height of the field adjusts automatically based on its content. If you prefer a fixed height, you can specify a "Max Height". Any content exceeding this height will not be fully displayed, and you will need to open the editor to view the entire content.

Barcode

You can use this Field Type to generate a Barcode. There is a selection of Barcode types you can generate, such as Code 128 and QR Codes.

Alternatively, you can configure Additional field settings to display values as Code 128 Barcodes.

For more use cases of barcodes, please refer to Ragic Learning Center.

Action Barcode

Action Barcode allows you to display an Action Button as a QR code, so that the Action Button can be executed by scanning the QR code. Common use cases include event sign-in or adding payment records.

For example, there is a "Corporate Training" sheet with an Action Button labeled "Attendee Sign-in," which fills in the username and current time for sign-in.

Add an Action Barcode field in the sheet and select the "Attendee Sign-in" Action Button under Field Settings.

With that, attendees can sign in by simply scanning the QR code. You can also print out the QR code or display it on a screen for attendees to scan, so the sign-in process can be completed without accessing the sheet.

Every time the Action Button is executed, SYSAdmin will see a sign-in record in the sheet.

Masked Text

This Field Type is similar to the Free Text Field Type, but can additionally be configured to display only the last N digits of text, as well as full details for select users.

It is useful when typing sensitive information, such as customer ID numbers or phone numbers.

Users with access rights can click the Preview icon to see the full information.

Note: The masked text field cannot be edited. For data security purposes, the field value will be cleared when you try to edit content in a Masked Text field.

Money

Similar to the Numeric Field Type, this Field Type sets the selected field as a money field so you can apply formulas.

You can edit the currency format from the formatting options listed under Format Type in Design Mode.

When dealing with larger amounts, you can refer to the Large Number Abbreviation section in the Numeric Field for setting it up.

Exchange Rate

This field retrieves real-time exchange rates from Open Exchange Rates when adding or editing a record. You can use Formulas along with it to convert the original currency into other currencies. You can also Populate Empty Values if needed.

The Exchange Rate field will default to fetching the latest rate and allow you to specify the conversion from one currency to another. For example, if you set it to convert from USD to EUR, the field value will display the fixed exchange rate of US Dollars to Euros.

Sometimes, we don't want to use the latest rate for calculations. For instance, quotations are often based on the exchange rate of the day the quote was issued, rather than the current rate. In such cases, you can use a Referenced date field to specify the exchange rate date based on the "Sales Order Date" field in the sheet.

If the original currency or target currency is not fixed, you can set the conversion from one currency to another based on a currency reference field. For example, if your company deals with customers from Europe and Japan and needs to convert USD pricing to EUR and JPY, you can set the conversion from USD and select a "Currency" field as a reference. Conversely, if you receive various foreign currencies and need to convert them back to USD for accounting purposes, you can set the conversion from the currency reference field to USD. If both currencies are not fixed, you can refer to different currency fields accordingly.

Please note that the referenced fields can only be either Free Text or Selection fields. The field value must be three uppercase letters, such as USD, JPY, EUR, etc. If you are unsure of the currency abbreviation, you can refer to the options in the menu.

When editing, selecting "EUR" for the "Currency" field will automatically retrieve the corresponding exchange rate.

Update Rate

The exchange rate field will only retrieve the exchange rate when adding or editing a record. If you want to update the exchange rate for that field in all records at once, you can execute the Update Rate in Design Mode.

Please note that executing this button will update all the values of this field, overwriting the existing values. If there is no referenced date field to refer to, the system will update the field value with the latest exchange rate; otherwise, the system will update the field value with the exchange rate on the specified date.

Updating rate will also be recorded in the Entry Information.

Reviewer

You can assign specific users the right to review entries by sending them a link to their email through this Field Type.

This field is the same as a Select User field where you select users and check to invite them to review this entry.

For detailed instructions, please refer to this article.

E-mail

The value of this field will be formatted as a link to the e-mail. Clicking on the link will prompt your web browser to respond according to its settings, such as by launching an e-mail application. Supports inputting multiple email addresses, separated by “,” and half-width spaces.

Phone

The value of this field will appear in the format type you selected in Design Mode. Clicking on the value of a phone field will prompt your web browser to respond according to its settings.

You can further select the desired format for the country's phone/mobile number in Formatting.

Address

This field allows you to enter your address or longitude and latitude so that when you click on it, you can see its location on Google Maps.

For the longitude and latitude, we also support multiple formats:

Degrees, minutes, and seconds (DMS): 41°24'12.2"N 2°10'26.5"E

Degrees and decimal minutes (DMM): 41 24.2028, 2 10.4418

Decimal degrees (DD): 41.40338, 2.17403

Another way to enter such values into this field is by clicking the Pin Icon.

A Google Maps window will pop up to let you pin the target location on the map. Please make sure that you pin it on the right location to generate the correct address value for the field. Save the value by clicking OK.

You can also set this field to display the address as a map.

Enter and save the address in the field. It will be automatically displayed as a map.

Locate User's Location

Sometimes, you might need users to report their current location, for example, for field visit location reporting. You can use an address field with a default value setting to choose the desired location format based on your needs.

Remember to add the Read Only setting, so that users cannot change the address on their own.

It will automatically retrieve the current location when creating the record. (Location data needs to be provided to the browser to obtain the current location.)

Note: This feature is also available in the app.

URL

The value of this field will be recognized as a link, and clicking on the value will take you to the linked website.

Special Format

The value of this field will be formatted according to universal standards that you can specify.

There are preprogrammed options available such as "SSN (Social Security Number)" and "VIN (Vehicle Identification Number)", but you can enter custom formats here as well.

Aggregate

This field can aggregate all records on the source sheet (linked sheet) that has a linked field linked to this record. There are five aggregate methods: count, total, minimum, maximum, and average.

For example, if you have "Sales Order" and "Customers" sheets, and in "Sales Order", you have already established a Link and Load relationship to "Customers".

You can add an Aggregate Field in the "Customers" to calculate the total amount spent by each customer from the records in "Sales Order". Based on your requirements, you can select the desired aggregate method, source sheet, and field to be aggregated.

In "Customer", you will be able to display the total amount spent, highest spending, and average spending for each customer.

When setting the Field Type to Auto Generate, the field value will be automatically generated. You can use it to generate records' IDs or serial numbers.

Choose a desired sequence format in the "Formatting box" below and view an example of the generated value.

The following content will introduce the application of various formats.

Generating Sequence Numbers Based on the Specified Selection or Date Field Value

You can generate sequence numbers based on the specified Selection Field or Date Field value.

For example, you can generate sequence numbers based on "Sales Group" (e.g., A, B, C, D) for categorization.

Set the Auto Generate Field to Generate sequence based on another field and select the "Sales Group" field.

After configured, selecting "Sales Group" as "A" will start the Auto Generate Field sequence with "A", and similarly for "B" and other groups.

Note: If the Auto Generate Field is based on the Date Field, the auto-generated values will follow the format set in the referenced Date Field. If you would like to apply a different date format, you need to create another Date Field with the desired format and apply the formula "=Original Date Field" to obtain the same date value. After completing, you can set the field to reference that new Date Field.

Sequence Format with Letters and Dates

The sequence format QUOTE-{1,date,yyyyMMdd}-{0,number,000} consists of two variables:

{0,number,000} is a serial number.

{1,date,yyyyMMdd} is the date when the serial number is generated.

Example 1:

If the format is QUOTE-{0,number,000}, the automatically generated numbers will be: QUOTE-001, QUOTE-002, QUOTE-003, and so on.

Example 2:

If the format is QUOTE-{1,date,yyyyMMdd}-{0,number,000}, the variable numbers generate a continuous sequence within the same date and reset on different dates.

When records are created on January 1, 2020, will generate numbers like QUOTE-20200101-001, QUOTE-20200101-002, and QUOTE-20200101-003.

It will reset on the next day to QUOTE-20200102-001, QUOTE-20200102-002, and QUOTE-20200102-003.

If the format is changed to QUOTE-{1,date,yyyyMM}-{0,number,000}, the generated numbers for January 1, 2020, and January 2, 2020, will be QUOTE-202001-001, QUOTE-202001-002, QUOTE-202001-003, QUOTE-202001-004, QUOTE-202001-005, QUOTE-202001-006.

The sequence numbers will reset in the next month.

Generating Entry URL

You can choose the Record URL format to automatically populate the URL for each record.

The Unique Value of Each Record (RAGIC_ID)

The Ragic ID is the Unique Value for identifying each record in the system. It appears as the last parameter in the Record URL.

For example, if the record URL is "https://www.ragic.com/LearningRagic/ragicsales/20001#!/20001/7", the last "7" represents the record's RAGIC ID.

If your sheet does not have a Unique Value field, you can consider using the Ragic ID as the Unique Value for importing or exporting data, and the downloaded Excel backup file of the sheet will include the Ragic ID by default.

The Unique Value of Each Subtable Record (SUBTABLE_RAGIC_ID)

The SUBTABLE RAGIC ID is the Unique Value for identifying each Subtable record in the system. It appears as the last parameter in the URL when generating a new sheet from that particular Subtable.

For example, in the URL "https://www.ragic.com/LearningRagic/ragicsales-order-management/20006/33," the "33" at the end represents the SUBTABLE RAGIC ID for that record. This format applies only to Subtable fields.

If you are in New Sheet From Subtable, selecting RAGIC ID will generate the same value.

Setting the Sequence Order

In the Design Mode, you can reset or change the current sequence order by entering the desired sequence number for the next record and clicking the Set seq of next record button.

Note: Clicking the Set seq of next record button will take effect immediately and does not require "Saving" the design.

For example, if you have a "Customer Number" field which is Auto Generate Field. If previously created records have been deleted, and the customer numbers for the existing record do not start from C-00001.

You can enter "1" in the Sequence of next record, then click on Set seq of next record. The new record's "Customer Number" will start from 1 (C-00001).

If you want to reorder existing records, you can manually adjust them.

For instance, if you have a record with the "Customer Number" as C-00003 and want to change it to C-00001, with the following records starting from C-00002, you can manually change C-00003 to C-00001 and then set the Sequence of next record to "2". The next record will start from C-00002.

You can view the current sequence number by clicking on Get seq of next record.

If your field is set to Generate sequence based on another field, you will need to Get seq of next record or Set seq of next record for each option in the "Field Options" below.

This feature is useful for ensuring that the sequence of imported data continues from the current one.

Populating Empty Values

If some of the Auto Generate fields in the sheet are empty, you can Populate empty values to automatically fill in the serial number in these fields.

If the field is set to Generate sequence based on another field, it will generate numbers for all the options. For example, if there are no sequence numbers in options "A", "B", and "C" and you click Populate empty values, you will see all the options generate their sequence numbers: A-00001, B-00001, C-00001, A-00002, B-00002, C-00002, and so on.

If the reference field is empty, it will generate sequence numbers without options, for example: "-00001".

When you don't have Unique Values in your records, you can use this feature to create them for future importing purposes.

Sequence Formatting

For more information about the formatting of sequences based on Java MessageFormat, you can visit this page.

Cascaded Selection helps you to set options connected to another Selection field depending on what is selected previously.

For example, when the "Major Category" option is "READY-TO-WEAR", the "Minor Category" options will display "Suits", "Outerwear", "Shirts", "T-shirts", "Pants & Shorts".

Setting up Cascaded Selections

To set Cascaded Selections for a Selection field, you must first create at least 2 Selection fields in the Design Mode.

Select the field "Minor Category" and check the Set Cascaded Selections box, then select "Major Category" from the drop-down menu.

Click on the Set button, and a pop-up menu will display all options of the referenced Selection field. You can add the Cascaded Selections to each parent option. Each Cascaded Selection must be written on one line.

Don't forget to click on the Save button after you finish.

It is possible to set Cascaded Selections referring to a field already with Cascaded Selections, so you can have a chain of Cascaded Selections.

Note:

1. Both the parent Selection field and the child Selection field need to be added to the Listing Page if you would like to display one of them there. If you do not want one of those fields displayed on the Listing Page, you may Hide that field.

2. The Multiple Selection field doesn't support Cascaded Selection.

3. The maximum number of selections in a Cascaded Selection field is 10000. When reaching the limit, the selection settings on the Cascaded Selection field will be reset after saving the sheet's design. If it is required to have more than 10000 selections, please use Link and Load with Dynamic Filtering applied.

Setting up a Default Value

Sometimes, you might need Ragic to fill out some of your fields with Default Values, such as the current time or date, to save time and avoid errors.

You can set Default Values by clicking the link next to the Default Value option in the Design Mode, under Field Settings > Basic.

Variables Offered

You can use the variables to generate a Default Value when a record is being created or updated.

Default Value	Default Formatting	Variable	Example
Create date	yyyy/MM/dd	$DATE	2015/05/19
Create time	HH:mm:ss	$TIME	09:21:05
Create date time	yyyy/MM/dd HH:mm:ss	$DATETIME	2015/05/10 09:21:05
Create year	yyyy	$YEAR	2015
Create month	M	$MONTH	5
Create weekday	E	$WEEKDAY	2
Create user name	Name LastName	$USERNAME	Jane Doe
Create user e-mail	email@domain.com	$USERID	jane@initech.com
Last modified date	yyyy/MM/dd	#DATE	2015/05/23
Last modified time	HH:mm:ss	#TIME	09:32:06
Last modified date time	yyyy/MM/dd HH:mm:ss	#DATETIME	2015/05/23 09:32:06
Last modified year	yyyy	#YEAR	2015
Last modified month	M	#MONTH	5
Last modified weekday	E	#WEEKDAY	2
Last modified user name	Name LastName	#USERNAME	Karen Moore
Last modified user e-mail	email@domain.com	#USERID	karen@initech.com
Sequence	1,2,3	$SEQ	3

Regarding the variables related to "Create", if Default Values are set after the record has been created, they will be triggered at the moment of adding or editing. If the field is empty, it will capture the editing user and editing time at that moment, rather than the actual creator and creation time of that record. To avoid this, it is recommended to manually Populate empty values to fetch the original created information.

Inputting Your Default Value Formatting

You can choose the Format Type options provided by Ragic, or you can directly enter text into the Formatting box below the Default Value options. You can also set a custom date format for Date fields.

Applying Formulas

You can also apply formulas to the Default Value. In the Design Mode, check the Interpret as formula below the Default Value field, then write the formula. For supported formulas, please refer to this section.

Notes

Formulas in the Default Value field will only be interpreted when the reference field is edited for the first time. Once the formula is interpreted, it will not be recalculated with further edits. If you would like the Default Value to be recalculated based on changes to the reference field, please use Formulas instead of assigning them as a Default Value.

When you turn on Auto Save in a sheet, Default Value formulas won't be applied if one formula refers to multiple fields. In such cases, it's recommended to use Formulas instead. Because when Auto Save is activated, the formula will be triggered when one of the reference fields is auto-saved, and it won't be recalculated when other fields are edited.

Using the Read Only Option

Fields with Default Values are often used in conjunction with the Read Only option to prevent users from manually changing the value.

Populating Empty Values

When your sheet already has records and you would like to add a new field with a Default Value, or you would like to change an existing Field Type to the Default Value field, Ragic will not automatically populate the Default Values to existing records according to the new configuration. Therefore, the new or newly modified field may remain empty.

If you want empty values to be automatically filled with Default Values, such as "creation date", "creator", or "exchange rate" fields, you can follow these steps in Design Mode：

Step 1. Access Design Mode to add or modify the desired Default Value field.

Step 2. Save your modification

Step 3. Under the Default Value setting, select the desired Default Value field. Then click on Populate empty values.

Once you click on the Populate empty values button, Ragic will start to populate the empty values in the background. This may take a while depending on the number of records in your sheet. Feel free to navigate away from Design Mode if needed. There will be a pop-up notice in the lower-left corner of the page once the process is complete.

You will then see the default value(s) auto-populated on the Form Page.

Setting Multiple Default Values for Select User or Group Fields

When setting multiple Default Values for Select User fields, you can use "|" to separate the values. For example, by entering "Rex|Amy" as the Default Value, the field will automatically be populated the field with "Rex" and "Amy".

You can also use "|" to separate multiple values for Select Group fields.

Note: If the user or group names themselves already contain the "|", You need to use "%7C" instead.

For example, to include the groups "IT" and "RD|Senior", it should be written as follows:

Setting the Formatting of Your Fields

You can modify field value formatting or select from available options based on the Field Type.

These settings automatically format input values, enabling quick inputs on entries in Ragic.

You can set the formatting of certain Field Types in the Design Mode of your sheet, under Field Settings > Basic.

Example Formatting Types

See some of the examples of what you can do with custom field formatting below.

Field Type	Formatting Applied	Input	Formatted Value	Notes
Date	yyyy/MM/dd	20151022	2015/10/22	Ragic will format your date value according to what you enter.
Date	dd-MM-yyyy	1022	22-10-2015	If you do not enter the year value, Ragic will input the current year.
Date	yyyy/MM/dd	22	2015/10/22	If you only enter the day, Ragic will autofill with the current month and year.
Date	yyyy, MMMM, dd EE	22	2015, October, 22 Thursday	Please check the section for custom date formats
Numeric	#,###.##	3266.528	3,266.53	Your input will be rounded to the nearest decimal according to the formatting.
Numeric	#,##0.00	3266	3,266.00	If no value exists in a position, access displays a zero (0).
Auto Generate	PO-{1,date,yyyyMMdd}-{0,number,000}		PO-20150519-029	Auto Generate fields do not require input from the user and will generate a serial number in sequence.
Money	$#,###.##	12859.2	$12,859.2	You can change the currency symbol in the formatting box by replacing $.
Phone	(XXX) XXX-XXXX	9497692270	(949) 769-2270	You can enter your formatting if your country isn't listed.
Special Format	XXX-X-XX-XXXXXX-X	9781451673319	978-1-45-167331-9	Some options include the standard ISBN, SSN, and VIN, but you can also enter your custom format.

Custom Date Formatting

If none of the available date formats fit your requirements, you can write your formatting with the table below:

Field	Full Form	Short Form
Year	yyyy (4 digits)	yy (2 digits), y (2 or 4 digits)
Month	MMMM (name. Ex. January),MMM (abbr. Ex. Jan),	MM (2 digits), M (1 or 2 digits)
Day of Month	dd (2 digits)	d (1 or 2 digits)
Day of Week	EEEE (Monday)	E (Mon)
Hour (1-12)	hh (2 digits)	h (1 or 2 digits)
Hour (0-23)	HH (2 digits)	H (1 or 2 digits)
Hour (0-11)	KK	K (1 or 2 digits)
Hour (1-24)	kk (2 digits)	k (1 or 2 digits)
Minute	mm (2 digits)	m (1 or 2 digits)
Second	ss (2 digits)	s (1 or 2 digits)
AM/PM	a

Displaying the Date Field in the Japanese Calendar Format

If you would like to display the date information in the Japanese calendar format, you can create your custom date formats by using the code from the table below. For example, if you want to display the date information as “Nov 11, Reiwa 3”, you can do it by typing “MMM dd, JGGG Jyy” in the formatting.

Code	Meaning	Display in English
JGGG	Japanese era name	Reiwa
JGG / JG	Short form of the Japanese era name	R
Jyy / Jy	Japanese year	1, 3, 5
EEEE	Days of the week	Tuesday
E / EE / EEE	Short form of the days of the week	Tue
a	AM/PM	AM, PM

Custom Numeric Formatting

You can also adjust the numeric format for "decimal" and "thousand" separators. On the left sidebar numeric field setting, click on Advanced next to the Format Type. Then, uncheck the Use system separators checkbox and modify the separator format as shown below.

Setting Default Formatting Through Account Settings

If you have a regularly used formatting for "Numeric", "Currency", and "Date fields", you can set them as defaults in the Company Setting > Account Settings.

This way, when designing and adding fields of relevant types, the default formats will be automatically applied.

You can configure additional field settings apart from a Default Value and a Field Formatting from the Design Mode, under Field Settings > Basic.

Not Empty

Setting fields to Not Empty indicates that the field value should not be empty. Fields with this attribute will be marked with a "red asterisk" on the upper left side of the field header.

Unique

When you set a field as Unique, the value of this field cannot be a duplicate.

This feature is most useful when you want to make sure that you do not register the same thing twice in your database, for example, a username, e-mail, or phone number.

With this configuration, when a user tries saving a record with the same value for a field marked as Unique, not only would the database prevent the user from saving but will also display a pop-up window that includes a link to the original saved record.

Subtable Unique

You will see this option in Subtable field settings (different from the Unique attribute of "independent field" settings). Enabling this option will prevent users from entering duplicate Subtable field values within a record, while Subtable values in other records are not affected by this restriction.

Read Only

When a field is set as Read Only, the value of this field cannot be manually edited.

This is useful for fields referenced from another sheet, Auto Generate fields, or those with a Default Value.

Column Span (Merge Cells)

You can Merge Cells to span columns, making a single field look wider.

It differs from adjusting the height of a row or the width of a column.

Do Not Clone

If you're creating many records with similar data, using Clone Entry might be a more efficient way than rewriting the data.

You can specify fields that should not be cloned by marking them as Do Not Clone.

No Wrap Text

When this configuration is set, the text in this field won't be wrapped.

If this function is not enabled and there is no wrapping, please refer to the Line-breaking rules.

Barcode

Specifies that the value of this field will be displayed in Barcode (QR Code).

If you would like to generate other types of Barcodes, such as Code 128, you may use the Barcode field type.

Hidden

Specifies that the field that will be Hidden in the following:

1. Sheet interface

2. Printer Friendly

3. Excel download (and related export options)

4. Web embed form

If a field doesn't need to be displayed to the user but its value is necessary, such as for constants or Formulas, you can set it as the Hidden field. When used within a Subtable, a Hidden field will create a "gap". Therefore, it's recommended to move Hidden fields to the end of the Subtable.

Note: This setting only hides specific fields in the layout interface. The field data will still be displayed in the change history or record update notifications. Therefore, if you want to hide the field based on different user access rights, it is recommended to use Field Level Access Rights settings.

Ignore Record Lock

When enabling this option, this field will remain unlocked after locking the record.

Field Instruction

You can add the Field Instruction to help users enter data into the field.

The configuration for Field Instruction can be found on the left sidebar under Design Mode > Field Settings > Other.

This will add a "(?)" to the field header, and your instruction will show when the user clicks on the "(?)".

Placeholder

In addition to Field Instruction, you can also add a Placeholder to a field, which will be displayed directly in the field to assist users in entering data. The configuration for the Placeholder can be found on the left sidebar under Design Mode > Field Settings > Other.

Before entering data, the set text will be displayed in gray within the field to guide users to accurately fill in the data.

Manual Attributes

Note: Please do not use this feature unless instructed by Ragic support.

To add Manual attributes to a field, enter the Design mode of the Form Page, select the field, and navigate to Field Settings > Other > Manual attributes on the left sidebar.

After entering your parameter into the Manual attributes configuration, click the "+" button next to it.

You might notice the entered parameter is now cleared, which means it has been added to the field.

Don't forget to Save before exiting.

To remove an attribute, simply try to add an empty attribute value and the attribute will be removed.

Field Descriptions / Static Text Fields

A Ragic Sheet can contain not only fields but also Static Form Descriptions that serve as instructions or decorative elements for your sheets. Normally when you start typing in a field in Design Mode, it creates a field for inputting data.

You can change the editing mode by clicking on the Editing Fields button at the top, and then selecting Editing Descriptions.

By doing so, when you type on the sheet, you will be able to create Field Descriptions instead of Fields.

Another way to create Field Descriptions is to go to the Add Component tab on the left sidebar, then drag and drop a Description from Static Text directly onto your sheet.

Styling Your Field Descriptions

When you select a Field Description, you can modify its style in the Field Settings from the left sidebar.

You can adjust the font size and style, text alignment, text, and cell colors, and link it to a URL.

You can span Field Descriptions across multiple columns by selecting the Column Span (Merge Cells) option in the left sidebar under the Basic tab. This will merge the specified number of fields within the same row.

Alternatively, you can simply drag the lower-right corner of the field.

Formulas in Field Descriptions

Formulas can also be applied in Field Descriptions but they only display the "calculated result", and their value will not be stored in the database. For more details, please refer here.

What Are Subtables?

Subtables are extremely useful for managing one-to-many relationships in your sheet. For example, you may want to create an entry for a customer account with activity history records for multiple contacts, or you may need to create records for multiple items in a purchase order or quotation. Subtables are everywhere in business applications, and Ragic provides the simplest way for you to create and manage them.

Creating Subtables

While creating a sheet in Design Mode, you can simply create a Subtable by arranging fields horizontally. Ensure that the field headers are placed next to each other in a row of consecutive cells. Just start typing the next field header in the adjoining horizontal cell, disregarding where the field value of the previous field would typically be placed. The field values will automatically shift to be displayed vertically below the field headers.

Note：

1. Creating sheets cannot begin with Subtables.

2. We recommended placing Subtables at the bottom of your sheet since Subtables will expand to accommodate new entries.

3. We suggest not placing more than one Subtable per row or placing an "independent field" next to the Subtable.

4. If you have different Subtables placed in different rows, but start from different columns, it's suggested to place a long description field in between to prevent the displaying issue caused when Subtables expand.

Entering Data Into Subtables

When using Subtables, users can enter many sub-entries belonging to the main sheet entry. Initially, the Subtable displays three empty rows for entering data, but as a user enters more information, additional rows will appear.

Filtering and Sorting in Subtables

When you want to filter or sort Subtable records, it's just like filtering and sorting data using field headers on a Listing Page. You can click on the dropdown arrows next to the field header.

Filtering Your Subtable Records

You can use the Filter by Text option to filter your Subtable. If the field is a Selection Field or a Multiple Selection Field, you can also choose one of the options to filter.

The applied filters can be cleared either by using the Clear Filter and Sort button located at the top of your page or by using the Clear Filter option accessible from the drop-down arrow of each field header.

Sorting the Order of Your Subtable Records

Options include sorting by ascending or descending order.

The applied Sorting can be cleared either by using the Clear Filter and Sort button located at the top of your page or by using the Clear Sorting option accessible from the drop-down arrow.

Setting a Default Filter and Default Sort to a Subtable

You can set a default filter or sort to Subtables, which filters and/or sorts the results displayed whenever entries are viewed.

To set a default filter or sort, click the Save current subtable filter and sort as default option accessible from the drop-down arrow.

The default setting can be cleared by clicking the Remove current subtable default filter and default sort option from the same menu.

Exporting Data from Your Subtables

If you want to export data from your Subtable, you can follow these steps：

1. Create a New Sheet from Your Subtable. This will generate a new sheet with all the records from the Subtable of the original sheet.

2. By Linking Fields from Parent Sheets, you can add "independent fields" from the parent sheet. This ensures that the necessary data from the parent sheet will also be exported along with the Subtable data.

3. Export your data from your new sheet. It works just like exporting from a sheet.

Adjusting Subtable Field Order

You can simply drag and drop the field headers horizontally to change the Subtable field order.

Moving the Subtable

1. Ensure that there is enough space in the desired location on the sheet.

2. Simply drag any of the Subtable fields to the desired location.

3. Specify the destination cell and click "Move" to complete.

More About Subtables

1. Learn more about how to use Formulas to calculate values in Subtables.

2. Having hundreds of records in a Subtable can slow down the loading speed. It is recommended to manage the records from a new sheet created from the Subtable.

3. If you want to prevent users from deleting Subtable records, you can lock permissions using the No subtable deletion allowed option while in Design Mode.

Video Tutorial

For more detailed information about Subtables, you can refer to the following tutorial video.

You can adjust the appearance of your sheet in the Design Mode and design specific versions for printing purposes.

Changing the Height and Width of Your Fields

In Form Page

Adjusting the height and width of cells works the same way as in Excel. You can drag the right border of a column to adjust its width and the bottom border of a row to adjust its height.

In Listing Page

You can adjust the width of a column by dragging its right border and the height of all rows by dragging the bottom border of one row.

Directly Set Column Width or Row Height

You can right-click the column or row you want to edit and select the Column Width or Row Height option. Then, directly enter "pixel values" to adjust the column width or row height in the pop-up window.

Additionally, you can drag multiple fields and adjust their widths or heights simultaneously.

Changing the Style of Field Headers and Values

You can modify the style of field headers or values from the Styles tab on the left sidebar in the Design Mode, including color, font size and style, and text alignment.

This can be done separately for Form and Listing Pages.

If you want to apply custom styling to field values while entering data, you can use BBCodes for text formatting.

Adding Field Borders

You can also add Borders to field headers and values from the Styles tab in the left sidebar. This can be done separately for Form and Listing Pages.

Click on the Border Icon under the Label Style or the Value Style formatting toolbar.

A formatting menu will display options for choosing the borders and their color and style.

Copying Styles

You can click the Copy Styles to apply the same style configurations to the next clicked field.

To apply a selected style to multiple fields, check Paste copied styles to multiple fields.

Clearing Styles

Clear Style of This Field

You can clear the style of a single field by clicking Clear style of this field under the Styles tab.

Clear Style of All Fields

You can clear all the styles and Themes in this sheet by clicking Clear style of all fields.

Applying a Theme

You can also apply the preselected theme to a Form Page or Listing Page. To preview available themes, click the Select Theme Icon under the Styles tab, then select and apply the theme of your choice.

Moving Fields

You can move fields by dragging and dropping in the Design Mode.

If you want to move the Subtable, please refer to this section.

Formatting Static Text Fields

For formatting Static Text Fields, please refer to this section.

Spanning (Merging) Cells

You can span fields across multiple columns by selecting Column Span (Merge Cells) option in the left sidebar under the Basic tab. It will merge the number of fields you input within the same row.

In static text fields, you can do this the same way or simply by dragging the lower-right corner of the field.

Changing Tab Color

To change the color of a tab, right-click to access the context menu and select Change Color.

This will display a Color Picker, where you can choose a color swatch, or enter a hexadecimal code. Then, click Save Color to apply it.

Changing Tab Icon

To change the Tab Icon, right-click to access the context menu and select Change Icon.

A pop-up menu will be displayed. Choose or upload the icon you would like to apply to your tab.

Video Tutorial

For a clearer demonstration, you can refer to the following tutorial video.

When accessing your Ragic database, you'll see a list of all available sheets under each tab that you have access rights to on the user Homepage. You can either scroll down to find the sheet that you want to access or navigate from the Tab at the top.

Tab Menu and Tab Manager

As tabs grow, locating the sheets you need might become a problem. This is when the Tab Menu can help. You can categorize tabs into different tab groups (tab menus) such as "ERP", "HR", or "Marketing". Each tab menu will have its homepage.

You can add and edit Tab Menus through Tab Manager, accessible in two ways:

1. Click on the drop-down menu at the top-left of the page and select Default Tab Menu, which contains all uncategorized tabs. Then, choose Create new tab menu set next to the "+" sign.

2. Right-click on any tab at the top and select Tab Manager.

To add a new tab menu, click on Create new tab menu and name it.

Select the tabs you would like to add to the new tab menu and click "Save".

You can also rename or delete the selected tab menu or sort tab menus by clicking the "gear icon".

Access the new tab menu by selecting it from the drop-down menu at the top-left of the page. Only the tabs you added will be displayed on this page. You can also add different logos and banners to each tab menu.

Note:

1. Tabs added to other tab menus will no longer be displayed in the Default Tab Menu.

2. Reports will move along with their source sheets.

Duplicating All Sheet Designs Under a Tab

If you have a "Sales Order Management Tab" and there are linking relationships between sheets within it, and you want to duplicate an identical set of designs (including linking relationships but excluding data), you can use the Duplicate Tab instead of copying individual sheet and manually adjusting the link.

Click on the inverted triangle icon next to the tab name or right-click, and choose Duplicate Tab.

Select which sheets you want to copy.

After submitted, all selected sheets will be copied together! The linking relationships between sheets will be connected to the newly copied set of sheets, not the source ones.

This feature is for duplicating the entire tab and its sheet designs. Unlike Multiple Versions, the data won't be synced after duplication.

Configuring Default Tab Menu Set for Specific User Groups

When your database has numerous tab menu sets and you want to simplify the process of finding sheets for users upon login, you can direct them to commonly used tab menu sets. For instance, users in the "Sales" group can be directed to the "ERP" tab menu set, while users in the "HR" group can be directed to the "HR" tab menu set.

First, open the Tab Manager, select the tab menu set you want to configure, and click the "gear icon" next to it. Choose Set as user group-specific default tab menu set.

In the window, specify which Groups should have this tab menu set as their default, and remember to Save. Once done, users in that group will be directed to this tab menu set upon login.

Note: If users belong to multiple groups, each configured with a different default tab menu set, their default will be determined by the order of these sets. If users can't access any sheets or tabs in a set, they will be directed to the next one. If none meet the criteria, they will be directed to the database's Default Tab Menu.

Sheet Information and Hiding Sheet

Select the sheet you would like to view and right-click to access the Sheet Information.

In the window, you can see the following information.

Hidden Sheet

You can hide a sheet from users who do not have SYSAdmin access rights in Sheet Information. This feature helps to maintain a clear interface by concealing the sheet.

Users can still access this sheet via its URL. If this sheet is a linked sheet within the Link & Load feature, it will remain accessible in those configurations.

Sheet Manager

If you want to change sheet order, you can hover on any tab or sheet and right-click to open Sheet Manager.

Select a sheet and click the horizontal arrows to move it to other tabs, or click the vertical arrows to change its order within the same tab.

Remember to save before leaving.

Deleting a Sheet or Tab

To delete the sheet or tab, hover over it and an "arrow" icon will appear on the right. Click on the arrow, then select "Delete" to remove it. Alternatively, right-click on the sheet or tab you want to delete, then select "Delete".

If the system stops you from deleting a sheet, please refer to this document.

If you accidentally deleted a tab or sheet and would like to recover it, you can check the Recycle Bin.

New Link

You can add URLs as hyperlinks under tabs through New Link. For instance, you can create a hyperlink to your e-commerce website under "Sales Order" tab.

Right-click on the tab and select New Link.

Set the name, URL, and the groups which can access for the hyperlink.

New Action

Since most desktop browsers do not yet support barcode scanning, detailed instructions are temporarily unavailable.

If you would like to add a company logo to your "Quotes" or "Sales Orders" sheets, you can hover on a blank cell and right-click to Insert Image.

In this window, you can upload an image or fill in the image URL and then set the width and length of the picture.

Click on the image to edit or delete it.

After saving the design, it will be displayed on the sheet.

The image will also show on the Printer Friendly page.

Usage of Formulas in Ragic

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.

Formula Generator

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 Formula AI Open Link 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.

Calculation Operators in Ragic

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.

Arithmetic Operators

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

Comparison Operators

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

Strings in Formulas

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.

List of Supported Formulas

Below is the list of the supported formulas and categories in Ragic. Please note that the following formulas are case-sensitive.

Formula Categories

Numeric Calculation Formula

Dates and Times Formulas

Strings Formulas

Conditional Formulas

Subtables Formulas

Multiple Select Field Formulas

Numeric Calculation Formula

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. nper (Required): The total number of payments for the loan pv (Required): The present value or principal fv (Optional): 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, that is, the future value of a loan is 0. type (Optional.): Fill 0 or omitted, if payments are due at the end of the period. Fill 1, If payments are due at the beginning of the period.

Dates and Times Formulas

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

Strings Formulas

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

Conditional Formulas

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.

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. 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 that are not 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.

Multiple Select Field Formulas

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.

Values in Subtables

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.

Calculate Unique Values in Subtable

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:

Return Specific Field Value When Criteria Matched

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 (A2 field in the below example).

returnField: Required. The Subtable field contains the return value (A9 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)".

Calculating Dates and Times

Formulas That Refer to Date Fields

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.

Formulas That Refer to Time Fields

To calculate time differences within a day, you need a Time 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 Time 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:

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

WORKDAY Function

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.

WORKDAY.INTL Function

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.

NETWORKDAYS Function

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.

NETWORKDAYS.INTL Function

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

Formulas in Strings

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 "\\".

Extracting Text Before and After a Specified Character

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.

Conditional Formulas

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

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.

When to add .RAW?

1. Referencing two fields that are equal with the IF function

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

2. Referencing a string field that equals a fixed string value as the condition with the IF function

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

3. Determining Whether a Field is Empty or Not

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.

4. Referencing the numeric value of a free text field and returning it as a text string

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)

Retrieving Text With the IF Function

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.

Referencing TODAY() or NOW() With the IF Function

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.

Date field With the IF Function

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 LOOKUP Function

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.

The AND Function

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.

The OR Function

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.

The NOT Function

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

The COUNTIF Function

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.

The COUNTIFS Function

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.

The SUMIF Function

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.

The SUMIFS Function

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.

The UPDATEIF Function

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.

Nesting Conditional Formulas

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.

IFS()

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

Formulas for Multiple Select Field

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 Recalculation

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.

If you frequently change a formula or apply TODAY(), you also have the option to add a script that will recalculate your formula every day. Please note that, when recalculating records exceeding 5000 through workflow, changes will not be included in the entry history.

Triggering Formula Recalculation on Parent or Related Sheets

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.

The definition of parent and related sheets

Parent Sheets:

In the above example, A and B are parent sheets of C.

The sheet which generates a new sheet from its Subtable (sheet A) is the parent sheet of the new sheet (sheet C).

The sheet which shows references from existing sheets (sheet B) is also the parent sheet of the existing sheet (sheet C).

Related sheets:

B and C are related sheets of A; A and C are related sheets of B.

Two sheets are called related sheets if they share the same Subtable and are not each other's multiple versions (sheet A and B).

Therefore, to sheets A and B, sheet C (which is generated from the subtable in sheet A) is their related sheet.

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 in Description Fields

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.

Math Objects supported in Ragic

About the Math Objects supported in Ragic, please refer to this page.

Suggest a Formula

If you need to use other unsupported formulas, please write to Ragic Support to suggest them.