Uploaded image for project: 'Service Packs and Hot Fixes'
  1. Service Packs and Hot Fixes
  2. MNT-21258

APS Data Model - Need support for number with minimum two-point decimal

    Details

    • Bug Priority:
      Category 4
    • ACT Numbers:

      01001811

      Description

      Request

      Need support for money, float, decimal, numeric with decimals for data modeling > number type. 

      [Additional Suggestion]

      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.

      Business reason

      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.

      Issue 

      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) 

      CREATE TABLE [APSTestCase01001811] ( PK INT IDENTITY(1,1) NOT NULL, [OperatingLoanatMonthEnd] [money] NULL, [TotalAccountsReceivable] [money] NULL, [TotalIntergerNumber] INT NULL, [TotalDecimalNullNumber2] Decimal(10,2) NULL, [mytextfield] VARCHAR(432) NULL, [mynumericfield] NUMERIC(10,5) NULL, [balanceduedateonly] DATE NULL, [balanceduedatetime] DATETIME NULL, [balanceduedatetimeoffset] DATETIMEOFFSET NULL )

      2. Insert couple test data to the external data source , which has various different columns of different data types with data of NULL value

      Insert Into APSTestCase01001811 (OperatingLoanatMonthEnd, TotalAccountsReceivable, TotalIntergerNumber, TotalDecimalNullNumber2, mytextfield, mynumericfield, balanceduedateonly, balanceduedatetime, balanceduedatetimeoffset) Values (521213.76,NULL,NULL,236.99,NULL,NULL,NULL, NULL, NULL)

      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. >

      Actual Behavior: 

      Money type field -> Display 521213.76 value as  521213
      Numeric type field -> Display 236.99 value as  236

      Desired behavior:  
      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"

       

       

        Attachments

          Structure

            Activity

              People

              • Assignee:
                custeng Customer Engineering
                Reporter:
                sliaw Seng Liaw
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Structure Helper Panel