Data Management Reference
Complete reference for data management functions, field types, and troubleshooting.
Formula Functions Reference
Date Functions
| Function | Syntax | Description | Example |
|---|---|---|---|
| DATE | DATE(year, month, day) | Create a date | DATE(2024, 1, 15) = 2024-01-15 |
| DATEDIF | DATEDIF(start, end, unit) | Calculate difference | DATEDIF("2024-01-01", "2024-01-15", "D") = 14 |
| DAY | DAY(date) | Extract day | DAY("2024-01-15") = 15 |
| DAYS | DAYS(end, start) | Calculate days | DAYS("2024-01-15", "2024-01-01") = 14 |
| HOUR | HOUR(time) | Extract hour | HOUR("14:30:00") = 14 |
| MINUTE | MINUTE(time) | Extract minute | MINUTE("14:30:00") = 30 |
| MONTH | MONTH(date) | Extract month | MONTH("2024-01-15") = 1 |
| NOW | NOW() | Current date and time | NOW() = 2024-01-15 14:30:25 |
| SECOND | SECOND(time) | Extract second | SECOND("14:30:45") = 45 |
| TODAY | TODAY() | Current date | TODAY() = 2024-01-15 |
| WEEKDAY | WEEKDAY(date, [type]) | Day of week | WEEKDAY("2024-01-15", 1) = 2 |
| WEEKNUM | WEEKNUM(date, [type]) | Week number | WEEKNUM("2024-01-15", 1) = 3 |
| YEAR | YEAR(date) | Extract year | YEAR("2024-01-15") = 2024 |
Logical Functions
| Function | Syntax | Description | Example |
|---|---|---|---|
| AND | AND(expr1, expr2, ...) | Returns TRUE if all are true | AND([Temp] > 80, [Pressure] > 50) |
| FALSE | FALSE() | Returns FALSE | FALSE() |
| IF | IF(condition, true_val, false_val) | Conditional logic | IF([Score] > 60, "Pass", "Fail") |
| NOT | NOT(expr) | Logical negation | NOT([Alarm_Active]) |
| OR | OR(expr1, expr2, ...) | Returns TRUE if any is true | OR([Status] == "Fault", [Status] == "Error") |
| TRUE | TRUE() | Returns TRUE | TRUE() |
Text Functions
| Function | Syntax | Description | Example |
|---|---|---|---|
| CHAR | CHAR(number) | Convert to Unicode character | CHAR(65) = "A" |
| CONCATENATE | CONCATENATE(str1, str2, ...) | Join strings | CONCATENATE("Line", "1") = "Line1" |
| LEFT | LEFT(text, [count]) | Extract left characters | LEFT("ABCD", 2) = "AB" |
| LEN | LEN(text) | Get string length | LEN("ABCD") = 4 |
| LOWER | LOWER(text) | Convert to lowercase | LOWER("ABCD") = "abcd" |
| MID | MID(text, start, length) | Extract substring | MID("ABCD", 2, 2) = "BC" |
| REPLACE | REPLACE(text, pos, len, new) | Replace substring | REPLACE("ABCD", 1, 2, "XY") = "XYCD" |
| RIGHT | RIGHT(text, [count]) | Extract right characters | RIGHT("ABCD", 2) = "CD" |
| TEXT | TEXT(value, format) | Format value as text | TEXT(TODAY(), "yyyy-MM-dd") |
| TODATE | TODATE(text) | Convert text to date | TODATE("2024-01-15") |
| TRIM | TRIM(text) | Remove extra spaces | TRIM(" ABC ") = "ABC" |
| UPPER | UPPER(text) | Convert to uppercase | UPPER("abcd") = "ABCD" |
Numeric Functions
| Function | Syntax | Description | Example |
|---|---|---|---|
| ABS | ABS(number) | Absolute value | ABS(-5) = 5 |
| AVERAGE | AVERAGE(val1, val2, ...) | Calculate average | AVERAGE(10, 20, 30) = 20 |
| COUNT | COUNT(val1, val2, ...) | Count numeric values | COUNT(1, 2, 3, 4, 5) = 5 |
| COUNTA | COUNTA(val1, val2, ...) | Count non-empty values | COUNTA("A", "B", "", "C") = 3 |
| MAX | MAX(val1, val2, ...) | Maximum value | MAX(10, 20, 30) = 30 |
| MEDIAN | MEDIAN(val1, val2, ...) | Median value | MEDIAN(1, 2, 3, 4, 5) = 3 |
| MIN | MIN(val1, val2, ...) | Minimum value | MIN(10, 20, 30) = 10 |
| RAND | RAND() | Random number (0-1) | RAND() = 0.42 |
| RANDBETWEEN | RANDBETWEEN(min, max) | Random integer | RANDBETWEEN(1, 100) = 58 |
| ROUND | ROUND(number, digits) | Round to digits | ROUND(3.14159, 2) = 3.14 |
| ROUNDDOWN | ROUNDDOWN(number, digits) | Round down | ROUNDDOWN(3.99, 0) = 3 |
| ROUNDUP | ROUNDUP(number, digits) | Round up | ROUNDUP(3.01, 0) = 4 |
| SUM | SUM(val1, val2, ...) | Sum values | SUM(10, 20, 30) = 60 |
Special Functions
| Function | Syntax | Description | Example |
|---|---|---|---|
| PREVALUE | PREVALUE(field) | Get previous record value | PREVALUE([Temperature]) |
| CHANGE | CHANGE(field, range) | Calculate change from previous | CHANGE([Counter], 1000) |
PREVALUE Example:
[Current_Value] - PREVALUE([Current_Value])
Calculates the difference between current and previous record.
CHANGE Example:
CHANGE([Cumulative_Count], 10000)
Returns the difference from previous value. If negative (counter reset), automatically adds the range value.
Field Types and Rules
Field Types
| Type | Description | Use Cases |
|---|---|---|
| Numeric | Numbers (integer or decimal) | Temperature, pressure, count, duration |
| Text | Strings (letters, numbers, symbols) | Status, product code, operator name |
| Date | Timestamps | Start time, end time, record time |
Grouping Rules (Aggregation Tables)
Numeric Fields:
- Same Value: Group records with identical values
Text Fields:
- Same Value: Group records with identical values
Date Fields:
- By Year: Group by year (2024, 2025)
- By Quarter: Group by quarter (Q1, Q2, Q3, Q4)
- By Month: Group by month (January, February)
- By Week: Group by week number (Week 1, Week 2)
- By Day: Group by day (2024-01-15, 2024-01-16)
- By Hour: Group by hour (00:00, 01:00, 02:00)
- By Minute: Group by minute (00:00, 00:01, 00:02)
- Same Value: Group by exact timestamp
Aggregation Rules (Aggregation Tables)
Numeric Fields:
- Sum: Total of all values in the group
- Average: Mean of all values in the group
- Maximum: Highest value in the group
- Minimum: Lowest value in the group
- Distinct Count: Number of unique values
- Count: Number of records
Text Fields:
- Distinct Count: Number of unique values
- Count: Number of records
Date Fields:
- Earliest: First timestamp in the group
- Latest: Last timestamp in the group
Storage Management
Retention Guidelines
| Archiving Frequency | Recommended Retention | Reason |
|---|---|---|
| Every second | 3-6 months | High data volume |
| Every minute | 1-2 years | Moderate data volume |
| Hourly | 3-5 years | Low data volume |
| Daily | 5-10 years | Very low data volume |
System Requirements
- Storage: SSD required for data archiving
- RAM: 8GB minimum, 16GB+ recommended
- Archiving Capacity: 5,000 records/second (standard configuration)
Troubleshooting
Data Not Recording
Symptoms: No new records appear in the data table.
Possible Causes:
- Data Service is not running
- Trigger conditions are not met
- Tag values are not updating
- Table is disabled
Solutions:
- Click Run Settings → Verify Data Service is Running
- Check trigger configuration:
- For Tag Trigger: Verify expression evaluates to true
- For Scheduled Trigger: Verify schedule is correct
- For Field Change Trigger: Verify field value is changing
- Check tag values in Tag Manager → Start Debug
- Right-click table → Verify it's Enabled
- Review Data Service logs for errors
Formula Returns Empty Values
Symptoms: Field shows empty values instead of calculated results.
Possible Causes:
- Formula syntax error
- Data type mismatch
- Referenced fields are empty
Solutions:
- Click Edit Field → Validate Formula
- Check for syntax errors (missing brackets, incorrect function names)
- Verify field types match formula expectations:
- Numeric formula → Numeric field type
- Text formula → Text field type
- Date formula → Date field type
- Test formula with sample data
- Check if referenced fields have values
Aggregation Table Not Updating
Symptoms: Aggregation table shows old data or no data.
Possible Causes:
- Aggregation trigger not configured
- Source table is not updating
- Grouping/aggregation fields misconfigured
Solutions:
- Verify source table is recording data
- Check aggregation trigger configuration:
- For Real-time Update: Verify it's enabled
- For Scheduled Trigger: Verify schedule is correct
- Manually trigger aggregation: Right-click table → Refresh Aggregation
- Check for grouping/aggregation field configuration errors
- Verify at least one grouping field and one aggregation field exist
Storage Space Running Out
Symptoms: Disk space warning, slow system performance.
Possible Causes:
- Retention period too long
- Archiving frequency too high
- Too many tables archiving data
Solutions:
- Review table sizes: Right-click table → Properties
- Reduce retention period in Storage Settings
- Enable Auto Cleanup and Auto Backup
- Manually export and clear old data
- Consider reducing archiving frequency
- Upgrade storage (SSD recommended)
Performance Degradation
Symptoms: Slow data recording, UI lag, high CPU usage.
Possible Causes:
- Too many tables archiving simultaneously
- High archiving frequency
- Insufficient system resources
- Real-time aggregation on large tables
Solutions:
- Monitor system resources (CPU, RAM, Disk I/O)
- Reduce archiving frequency for non-critical tables
- Disable real-time aggregation for large tables
- Schedule aggregations during low activity periods
- Upgrade system hardware (RAM, SSD)
- Distribute archiving across multiple tables
Cannot Delete Table
Symptoms: Error message when attempting to delete a table.
Possible Causes:
- Table is used as a source for aggregation tables
Solutions:
- Identify dependent tables (error message lists them)
- Delete dependent aggregation tables first
- Then delete the source table
- Or keep the source table and only delete aggregation tables
Next Steps
- Data Management Examples - Real-world production scenarios
- Creating Data Tables - Back to table creation
- Configuring Triggers and Formulas - Set up data collection