Calculated Values in Flex DataParts

Calculated values enable dynamic, per-record calculations within Form and Report DataParts. These values are determined in real time when the DataPart loads or when the user interacts with the form by entering or updating other fields. With built-in SQL support, calculated values can use custom SQL expressions to perform advanced logic, reference other fields, or retrieve data from related tables. They can be used for display purposes only or saved directly to your table upon form submission. 

Supported DataParts: 

• Submission Form
• Signup Form
• Update/Details Form
• Tabular Report
• Cards Report

Adding Calculated Values

To add a calculated value field to a supported DataPart, go to the DataPart’s Configure fields view. In the Elements left panel, click Add and select Calculated value.

Saving and Displaying Calculated Values 

Saving Calculated Values to Table Fields

When implemented as form elements within Submission, Sign–Up, or Details/Update Forms, Calculated values can be used to directly save computed data into corresponding table fields. This functionality supports the following field data types: 

• Text (255) 
• Text (64000) 
• Number 
• Integer 
• Currency 
• Date/time 
• Yes/No 

The persistent storage of calculated information enables you to use it in DataParts or analyze further. 

Displaying Real-Time Calculations with Virtual Fields 

Alternatively, you can use Calculated values to display real-time calculations without saving the results to the underlying table. You can achieve this with a dedicated Calculate value element on Reports and with virtual fields on Forms.

SQL Syntax and Data Access in Calculated Values 

SQL Syntax for Dynamic Computation 

Calculated values utilize SQL syntax, providing a powerful and familiar way for defining dynamic computations. This enables you to leverage standard SQL SELECT statements to retrieve data, CASE WHEN clauses for building conditional logic, and a wide array of SQL functions for data manipulation and analysis. 

Data Modification Restrictions 

To maintain data integrity and prevent unintended modifications, Calculated values are restricted from altering data in other tables or modifying the data model. Therefore, SQL statements such as INSERT, UPDATE, and DROP are not permitted within Calculated value expressions. 

Accessing Data Source and User Input 

Using [@field:fieldName] in Calculated values, you can provide seamless access to data source values from the current record . This allows you to incorporate existing record data into your calculations. 

Using [@runtimefield:fieldName], you can access user-entered values from respective form inputs or other calculated values, enabling dynamic computations based on user interactions. For example, you can compare value saved in a table with a new value entered by app user in the form and display the greater one:

CASE  
WHEN [@field:Number] > [@runtimefield:Number] THEN [@field:Number]  
ELSE [@runtimefield:Number]  
END

Accessing Data from Account Tables and Views

Beyond the current record and user input, Calculated values can access any data from tables and views within your Caspio account. This is achieved using SQL SELECT statements, enabling complex data retrieval and integration. For instance, you can use the code below to retrieve specific field value from a selected record:

SELECT fieldName FROM tableName WHERE id=[@field:id]

For the list of supported SQL functions, see Function Reference.

For more information, see the Microsoft SQL Server functions documentation:

• Math
• Text
• Date

Examples of Calculated Value Usage 

Example 1: Using the ROUND() Function for Precise Numerical Display 

The  ROUND() function in SQL Server allows you to round numeric values to a specified number of decimal places. This is useful for presenting financial data, averages, or any numerical results with precision. 

For example, in a field called GrossRevenue, you need to display the value rounded to the nearest cent (two decimal places). Use the following Calculated Value expression:

ROUND([@field:GrossRevenue], 2)

Example 2: Using the DATEDIFF() Function for Time Interval Analysis 

The DATEDIFF() function is crucial for calculating the difference between two dates based on a specified date part (for example, days, months, years). This is essential for tasks like calculating age, determining the duration between events, or analyzing time-based data. 

For example, if you have a StartDate field and an EndDate field, you can calculate the duration in days between these two dates for each record:

DATEDIFF(day, [@field:StartDate], [@field:EndDate])

Example 3: Selecting a Field Value from Another Table by Record ID 

Calculated Values provide the capability to retrieve data from any table or view within your Caspio account using a standard SQL SELECT statement. This is particularly useful when you need to display information related to the current record but stored in a different table. 

Let’s assume you have two tables: Orders and Customers. The Orders table has a foreign key field called CustomerID that links each order to a specific customer in the Customers table. You want to display the CompanyName of the customer associated with the current order in your Orders DataPage. Assuming the Customers table has a primary key field named ID:

SELECT CompanyName FROM Customers WHERE ID = [@field:CustomerID]

Example 4: Dynamic Order Status with Conditional Logic 

Leveraging Microsoft SQL Server’s conditional logic, Calculated values can dynamically display information based on data in your table and the current date. This enables real-time status updates without any manual intervention. 

For example, you have an Orders table with the following relevant fields: 

• OrderStatus (Text): Stores the status of the order (like “Processing”, “Shipped”, “Delivered”, “Cancelled”). 
• ExpectedDeliveryDate (Date/Time): Stores the anticipated delivery date for the order. 

You want to create a Calculated value that displays a more informative order status to the user, taking into account both the stored OrderStatus and whether the ExpectedDeliveryDate has passed. Use the following SQL conditional logic: 

CASE 

    WHEN [@field:OrderStatus] = 'Delivered' THEN 'Your order has been successfully delivered.' 

    WHEN [@field:OrderStatus] = 'Cancelled' THEN 'This order has been cancelled.' 

    WHEN [@field:ExpectedDeliveryDate] < GETDATE() THEN 'This order may be delayed. Please contact support for updates.' 

    WHEN [@field:OrderStatus] = 'Shipped' THEN 'Your order has been shipped and is out for delivery.' 

    ELSE 'Your order is currently being processed.' 

END

Note: The calculation in this example returns text. All text values within your SQL formula (expression) must always be enclosed in single quotation marks (‘). For example, when comparing a text field like [@field:OrderStatus] to a specific value, enter ‘Delivered’, not Delivered. If you do not use the quotation marks, your Calculated Value expression will result in an error.

Limitations of Virtual Fields for Multi-Step Calculations 

When utilizing Calculated Values in Reports or as form elements for Virtual Fields, the final output intended for display must be a text string. 

This means that calculated values cannot be directly used for intermediate mathematical operations passed to subsequent fields. The text value passed will not be recognized as a number, and the formula validation for the next Calculated value will fail. 

As a workaround, the field performing the calculation should be a table field of a known type (like Integer or Number) so that the value passed from the Calculated value can be correctly interpreted and used in subsequent calculations or displays.