Inconsistent JSON Data Type Handling in Warewolf Workflow Output, Breaking PowerApps

When executing a workflow in Warewolf that retrieves data from a SQL Server database and generates a JSON output, the application modifies the data types in the JSON payload. This behavior leads to inconsistencies that break PowerApps integration, as PowerApps requires consistent data types for proper functioning.

The key issues observed are:

1. String Fields:
   • String fields (FldVarchar) are sometimes output as numbers when the data from SQL is numeric (e.g., 1 instead of "1"). This results in inconsistent data types, causing errors when PowerApps processes the JSON.
2. Float Fields:
   • Float values (FldFloat) are being formatted with a locale-specific decimal separator (",") instead of the JSON standard dot ("."). For example, 1.123 in SQL is output as "1,123", which PowerApps cannot interpret as a valid numeric value.
3. Null Values:
   • Null values in the database are being output as empty strings ("") in the JSON payload for both string and numeric fields. PowerApps expects numeric fields to use null to signify missing data, and empty strings cause parsing errors or unexpected behavior.

Steps to Reproduce:

1. Create a stored procedure in SQL Server that returns a dataset containing strings, numbers, floats, and nulls:

   sqlCopyEditCreate Procedure usp_GetValues AS
   Declare @TempTable TABLE
   (
       FldVarchar varchar(10),
       FldNumber int,
       FldFloat float
   )
   Insert into @TempTable Values('String', 1, 1.123), ('1', 1, 1.123), (null, null, null)
   Select * from @TempTable
   

2. Use the Warewolf Low Code tool to create a workflow that calls the stored procedure using the SQL Server Database connector.
3. Map the output fields (FldVarchar, FldNumber, FldFloat) to a recordset in the workflow.
4. Run the workflow and observe the JSON output.
5. Attempt to integrate the JSON output with PowerApps.

Expected Behavior:

• FldVarchar should consistently output string values (e.g., "String" or "1").
• FldFloat should use a dot (".") as the decimal separator (e.g., 1.123 instead of "1,123").
• Null values should be represented as null in the JSON payload, not as empty strings ("").

Actual Behavior:

• FldVarchar alternates between string and numeric data types depending on the SQL value, breaking PowerApps' ability to process the field.
• FldFloat uses a comma as the decimal separator, which PowerApps cannot parse as a number.
• Null values are converted to empty strings, causing errors or unexpected behavior in PowerApps workflows.

Impact on PowerApps:
PowerApps relies on consistent and standard JSON data types for proper functionality. The inconsistent handling of data types in Warewolf is breaking workflows and causing significant delays in application development and deployment.

Proposed Solution:

1. Ensure that Warewolf outputs consistent data types in JSON:
   • Always serialize strings as strings.
   • Maintain numeric values in their standard JSON representation (e.g., dot as the decimal separator).
   • Represent null values correctly as null.
2. Provide an option in Warewolf to enforce strict type adherence when generating JSON outputs.

Attachments:

• Screenshot of the Warewolf workflow.
• Example of the problematic JSON output:

  jsonCopyEdit{
    "Values": [
      {
        "FldFloat": "1,123",
        "FldNumber": 1,
        "FldVarchar": "String"
      },
      {
        "FldFloat": "1,123",
        "FldNumber": 1,
        "FldVarchar": 1
      },
      {
        "FldFloat": "",
        "FldNumber": "",
        "FldVarchar": ""
      }
    ]
  }
  

• Expected JSON output:

  jsonCopyEdit{
    "Values": [
      {
        "FldFloat": 1.123,
        "FldNumber": 1,
        "FldVarchar": "String"
      },
      {
        "FldFloat": 1.123,
        "FldNumber": 1,
        "FldVarchar": "1"
      },
      {
        "FldFloat": null,
        "FldNumber": null,
        "FldVarchar": null
      }
    ]
  }