Data Calculation
Data Calculation is used to define a default value, a formula or an RDBMS expression.
- A default value for a property is calculated when an object is created. For example, you may want to set the state for a customer to Active by default when a new customer is created.
- A formula is calculated whenever data are read or modified. For example, you can calculate the age for a person.
- An RDBMS expression is calculated when data are read. RDBMS expressions are typically used to calculate measures used when aggregating data.
The available data calculation options for a property depends on the settings for the type and data binding:
| Data Binding / Type | Data | Function |
| Unbound | None Default | Formula |
| Bound | None Default Formula | RDBMS Expression |
Note that you can specify formulas for properties of type Data. This gives you the opportunity to store calculated values, for example to use for searching and in reports. It is however, important to be aware of a specific situation that may occur ff your formula uses properties from other object classes, or properties that are updated outside Genus, for example through a database interface. Since the formula is evaluated whenever the object is read, a user may see a value which is correctly calculated based on current values in the database. The calculated value is however, not stored to the database until the object is explicitly saved.
Specifying a Default Value or Formula
- To specify a default value, click Default Value, and then in the list, select how to calculate the default value.
- To specify a formula, click Formula, and then in the list, select how to calculate the value.
| Option | Description | Available in Default Calculation | Available in Formula Calculation | Required Data Type and Interpretation |
|---|---|---|---|---|
| Active User Account Stamp | Assign a reference or the name of the currently signed in user account. This value can not be overwritten. If you want to assign a default value which the user can overwrite, click Select Field or Group, and then select a field from one of your Account Classes. | Yes | Yes | * Account Class * Character or String |
| Custom Id Generator | Assign a unique id from an Identifier Domain when an object is created. Typically used for properties which represents a code used as part of the visual identification of an object, for example a department code. Note that if the default value for two or more properties are calculated based on an Identifier Domain, each property must calculate its default value based on a different Identifier Domain. | Yes | No | * 32-bit Integer (Sequential Counter or Pool) * Character or String (Pool) |
| Enter a Conditional Formula | Calculate a value using a conditional formula. A conditional formula gives you the opportunity to define one or more formulas with a conditional statement. The conditional statement is used to determine which formula to apply when calculating the value. | No | Yes | * Any data type |
| Enter a Formula | Calculate a value using a formula which combines functions, references to other fields, static values (such as a number) and mathematical operators. | Yes | Yes | * Any data type |
| Enter a Number | Assign a constant numeric value. | Yes | Yes | * Numeric data types |
| Enter a Text | Assign a constant text value. | Yes | Yes | * Character or String * Binary data types with interpretation Free Text |
| Enter a Time Function | Assign a date and time relative to the current date and time. | Yes | Yes | * Date * Date and Time * Integer data types with a date interpretation. |
| GUID | Assign a Globally Unique Identifier. If the data type for the property is Character or String, the property must be at least 32 characters long. If the data type for the property is GUID, this option is only available if the property is defined as the primary key in the Object Class. | Yes | Yes | * Character or String * GUID |
| Internal Id Generator | Assign a unique numeric id to a property defined as primary key in an Object Class. | Yes | No | * 32-bit Integer |
| Look Up Objects | Assign an object based on a condition. Not available if the interpretation refers to a Code Domain. Members for Code Domains are expanded in the list. To assign a specific member, click the member in the list. | Yes | Yes | * Any data type with a complex interpretation. |
| Now | Assign the current date and time. | Yes | Yes | * Date * Date and Time * Integer data types with a date interpretation |
| Select a Date | Assign a specific date and time. | Yes | Yes | * Date * Date and Time * Integer data types with a date interpretation |
| Select Field or Group | Assign a value from another field. | Yes | Yes | * Any data type |
| Time Stamp | Assign the current date and time. This value can not be overwritten. If you want to assign a default value which the user can overwrite, select the option Today, Now, Enter a Time Function or Select a Date. | Yes | Yes | * Date * Date and Time * Integer data types with a date interpretation |
| Today | Assign the current date. | Yes | Yes | * Date * Date and Time * Integer data types with a date interpretation |
| True / False | Assign a boolean value. | Yes | Yes | * Integer data types with interpretation Boolean. |
Entering an RDBMS expression
Click RDBMS Expression, and enter a valid RDBMS expression returning data of the same data type as specified for the property. An expression is a combination of constants, SQL functions, column names and SQL operators. Please refer to your database vendor documentation for more information on how to write an expression.
If the expression contains references to columns located in other tables, you can use a Class Connection to access the data. In the Placeholder Data Source list, select the Class Connection to use. Columns located in the last junction for the the Class Connection, must be prefixed with a question mark and a period, "?.". Columns located in any other junction must be prefixed with a question mark, a negative offset indicating the relative position of the junction to the last junction, and a period. For example, "?-1.".
For example, if an Object Class contains data for sales price in different native currencies, you may want to convert the sales price to a common currency. In order to do this, you need to use a Class Connection which connects your sales data with an Object Class containing exchange rates, where the Object Classes are connected by currency and date:
?-1.sales_price * ?.exchange_rate
Note
A property using a RDBMS expression with a placeholderdata source can only be used in contexts where SQL queries can be executed. For example, such a property cannot be used to define the display name for an object class. In addition, the property cannot be joined in a select statement for a referencing object class (e.g. a table column bound through a reference property to the property which contains the RDBMS expression).