Need support for money, float, decimal, numeric with decimals for data modeling > number type.
In addition to fixing the underlying issue (adding decimal/NULL support see MNT-21257), it is also suggested the following intermediate steps that would be quick to implement:
1. The data model feature has an table column and data type auto-discovery feature where it imports table fields as attributes, including auto-populating their type. I suggest that this feature not auto-map types other than non-NULLable character, date and integer types as these are the only ones it can actually handle. This would help users not use the import feature and assume that successfully imported fields will work as expected and then maybe experience data loss when they use the feature. Just to be cautious.
2. The data model feature uses the string "number" to describe its number-related type support when the actual behavior is that the only numeric type supported is integer. It would provide better user clarity to change this display string to be "integer".
3. Add to the documentation (e.g. https://docs.alfresco.com/process-services1.10/topics/data_models.html) on what database types the Data Model currently supports.
There are lots of common use case as customer may have data column carrying data such as currency/money/amount or numeric data require more precision (measurement) etc.
There is a requirement for decimal support in general (and more than 2 digits also, but that's a separate issue). It is assume this truncation to integer behavior in the data model feature will occur with any database non-integer numeric type, not just MONEY. The customer use the MONEY type but this is a legacy type and for new projects, they use DECIMAL (20,6) for financial data.
Since the underlying Activiti engine supports the Java double type, that would be a reasonable mapping type to use and readily available to the product team. Strictly speaking, BigDecimal would be even better, but that would be more engineering work.
Currently, we support string, number, date type for data model. But it is unable to display any numerical date with decimal point, as all decimal numbers were getting truncated when displayed in Task form.
Here is a video recording demo'ing the issue: https://alfresco.zoom.us/rec/play/vpB8dez7_G43T9yRuASDU6d8W425eq6sg3UdrKdbz0mxV3BQYFGjN7oXMOVyqjrHpQShomigp8qfpIAC?continueMode=true
Steps to reproduce
1. Create an external data source (Azure SQL Server DB in this example)
2. Insert couple test data to the external data source , which has various different columns of different data types with data of NULL value
3. Navigate to APS 1.10 > activiti-app > Identity Management > Tenants > Data Sources > configure to add your external data source. Test connection and make sure it connects fine.
4. Create new data model > see my example attached : test data model 3.json
- Do note that when I imported the attributes, for whatever reason, the 'datatimeoffset' type field is recognized as 'String' instead of 'date' type. I have to manually change this to 'date' field to avoid sql error in later stage.
- Do note that all number field type (e.g. money, decimal, numeric, integer) they are recognized as "number field'. However, all decimal numbers will be display as integer/whole number with decimal getting truncated. There is another existing feature Jira requesting support for Decimal/Currency number fields.
5. Create new app with process model and mapping variables > see my example attached : test data model 3 app.zip
6. Publish the app and start the process.
7. Enter PK row number : 11 in my example.
8. In Display Data task -> all values was fetch from external data source, but all NULL value for field with number and string type are display incorrectly than it was created in the actual database table. >
Money type field -> Display 521213.76 value as 521213
Numeric type field -> Display 236.99 value as 236
Money type field -> Display 521213.76 value as 521213.76
Numeric type field -> Display 236.99 value as 236.99
> See screenshot: "Display currency or decimal value incorrectly - truncated to Integer.png"
9. This truncated decimal value issue then leads to an even more bigger problem when next task in process, which is a store entity task being executed to save back to DBMS, it saved the incorrectly displayed no decimal values back to the external data source table columns and then overwrite the original decimal value stored in these affected columns/fields. This has then cause further data loss as their original database data is being overwritten.
> See screenshot: "AfterStoreEntityTaskRun_decimal values being truncated when save back.png"