Calculated Fields in Tabidoo

Calculated fields allow you to dynamically compute and display values based on other fields or expressions in your table. This section provides detailed information on how to utilize calculated fields effectively, including supported expressions, common use cases, and limitations.

What Are Calculated Fields?

Calculated fields are custom fields where the values are derived from calculations involving other fields, constants, or built-in functions. These fields are updated automatically when the data they depend on changes.

Key Features of Calculated Fields

Dynamic Updates: Values are automatically recalculated whenever relevant data is modified.

Built-in Functions: Support for functions like Math, Date, and string manipulation.

Cross-field Operations: Use data from multiple fields within the same table.

Integration with Linked Tables: Access and display data from connected tables.

Common Use Cases

1. Interval Calculation

Example: Calculate the number of hours between two dates.

Using the built-in formula editor:

DIFF(Date from, Date to, 'hours')

Date from: The starting date field.

Date to: The ending date field.

'hours': Specifies the unit of measurement.

The result will show the interval in hours.

For example:

Date from: 12/23/2024 09:25 AM

Date to: 12/30/2024 12:25 PM

Hours: 171

2. Unique Record Identifier

Example: Create a unique invoice number based on the DUZP date and an auto-incrementing number.

'FA' + YEAR(DUZP) + Number?.toString().padStart(4, '0')

DUZP: A date field (e.g., the date of taxable supply).

Number: An automatically incrementing numeric field.

YEAR(DUZP): Extracts the year from the DUZP date.

padStart(4, '0'): Ensures the number is at least 4 digits, padded with leading zeros.

For example:

DUZP: 2024-12-30

Number: 25

Result: FA2024025

3. Display Field from a Linked Table

Example: Show a related field from a connected table.

When working with a field of type Link to Table, you can use the dropdown to select a specific field from the linked table to display in your calculated field. For example:

Linked Table Field: Select the field you want to reference from the dropdown list in the formula editor.

Example:

Steps:

Ensure the linked table is configured properly.

Use the dropdown in the formula editor to select the field you wish to display.

Save the calculated field configuration.

Additionally, if your application includes a Parameters Table, any defined parameters will also appear in the dropdown list of available fields. This feature simplifies the process of referencing application-wide parameters in your calculations.

This setup allows you to easily display values from a connected table without additional scripting.

Supported Expressions

Operators

Arithmetic: +, -, *, /

Example: 3 + 2 results in 5

Example: 10 / 2 results in 5

Conditional: if, ? :

Example: if (true) { 'yes' } else { 'no' } results in 'yes'

Example: true ? 'yes' : 'no' results in 'yes'

Constants

Numbers: e.g., 0, 1, 3.14

Strings: e.g., 'text'

Boolean: true, false

Text Functions

concat: Concatenate strings.

slice: Extract a portion of a string.

toUpperCase: Convert text to uppercase.

toLowerCase: Convert text to lowercase.

padStart: Pad text to a specified length.

Mathematical Functions

ROUND: Round a number to the nearest integer.

MIN: Get the minimum value.

MAX: Get the maximum value.

AVG: Calculate the average.

ABS: Absolute value.

ROUNDUP: Round a number up.

ROUNDDOWN: Round a number down.

PERCENTAGE: Calculate the percentage.

Date Functions

NEWDATE: Create a new date object.

ADDDAYS: Add days to a date.

ADDMONTHS: Add months to a date.

ADDYEARS: Add years to a date.

MINDATE: Find the minimum date in a range.

MAXDATE: Find the maximum date in a range.

DIFF: Calculate the difference between two dates in specified units.

Example: 
DIFF('2024-01-25', '2024-01-27', 'days') → 2

YEAR: Extract the year from a date.

MONTH: Extract the month from a date.

DAY: Extract the day from a date.

HOUR: Extract the hour from a date.

MINUTE: Extract the minute from a date.

WEEKDAY: Get the day of the week.

WEEKDAYISO: Get the ISO day of the week.

TODATESTRINGISO: Convert a date to ISO string format.

TODATESTRINGAPP: Convert a date to application format string.

TODATETIMESTRINGAPP: Convert a datetime to application format string.

Limitations

Cross-Table Operations: Calculations can reference linked tables but cannot directly perform operations across multiple tables.

Complex Logic: Extensive logic should be handled outside the calculated field using automation scripts.

Performance: Excessive calculations may impact performance for large datasets.

Use Case Examples

• Display field from a Linked Table.
• Unique Record Identifier.
• Display Email type field.
• Interval Calculation