Transform - State Solutions

Gives the calling application the ability to take a dataset and use combinations of the built in functions for transformative purposes.

Version 1

HTTP Request
POST /ado/v1/Transform

Header

Parameter	Description
Ocp-Apim-Subscription-Key	The subscription key you received when you purchased a plan.

Request Body

Mandatory

Parameter	Type	Description
transformations	object	Each property name represents a field that will be output in the resulting dataset. The value of that property should be a a fully formed reference to an existing field in the provided dataset which is able to be encased with any of the functions available to the framework.
data	object[]	Array of complex objects.

Optional

Parameter	Type	Description
preserveAllProperties	Boolean	If `true`, all properties not specifically called out in the `transformations` property will be preserved in the resulting dataset. If `false`, all properties not included in the `transformations` object will be dropped from the resulting dataset. Default is `false`.

Common

Further Documentation: Common Parameters

Parameter	Type	Description
filter	String	A fully functioning SQL based WHERE statement that will filter the outgoing dataset to the records it determines as being included.
sortOrder	Object	Sorts the resulting dataset by the criteria provided. The object should contain property names that represent properties contained within the resulting dataset with a value corresponding to the desired sort direction (i.e. `ASC` or `DESC`).
schema	Object	You can use this parameter to override the inferred schema for properties in the incoming dataset(s). A field will be inferred unless specific explicitly within this object.
advancedOptions	Object	Is an object with the following properties. cultureName (String) The specified culture determines the behaviour for aspects related to formatting numeric values and dates. Is extremely important when converting strings to dates, e.g. 05/03/2022 will be treated differently between locales. For more information on the accepted values for this property, please consult the documentation from Microsoft … https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo?view=net-6.0#culture-names-and-identifiers isBoolean (string[]) A list of strings that are the names of all properties in the resulting dataset that should be treated as boolean values. By default, the framework treats boolean values as 1 (true) or 0 (false). In order to differentiate the integer as a boolean, the property in question needs to be called out explicitly in this list.

Notes

There are in excess of 100 built in functions available to use when transforming data.

Please refer to the documentation for further information.

Examples

Complete

Preserve All Properties

This example demonstrates the ability to replace a prefix on a cell value and append with leading characters. It also demonstrates the ability for formatting numeric values along with conversions for values from (in this case) string to integer.

Request

{
    "transformations": {
        "CostCentre": "PadLeft(Replace([CostCentre], 'CC_', ''), '0', 10)",
        "CostElement": "PadLeft(Replace([CostElement], 'CE_', ''), '0', 10)",
        "FiscalYear": "Int([FiscalYear])",
        "FormattedAmount": "Format([Amount], '#,##0.00')"
    },
    "sortOrder": {
        "CostCentre": "ASC",
        "CostElement": "DESC"
    },
    "data": [
        {
            "CostCentre": "CC_1000",
            "CostElement": "CE_6000",
            "FiscalYear": "2022",
            "Amount": 844488.2364556
        },
        {
            "CostCentre": "CC_1000",
            "CostElement": "CE_6001",
            "FiscalYear": "2022",
            "Amount": 103965.84654654
        },
        {
            "CostCentre": "CC_1000",
            "CostElement": "CE_6002",
            "FiscalYear": "2022",
            "Amount": 729006.6564564
        }
    ]
}
Code language: JSON / JSON with Comments (json)

Response

[
    {
        "CostCentre": "0000001000",
        "CostElement": "0000006002",
        "FiscalYear": 2022,
        "FormattedAmount": "729,006.66"
    },
    {
        "CostCentre": "0000001000",
        "CostElement": "0000006001",
        "FiscalYear": 2022,
        "FormattedAmount": "103,965.85"
    },
    {
        "CostCentre": "0000001000",
        "CostElement": "0000006000",
        "FiscalYear": 2022,
        "FormattedAmount": "844,488.24"
    }
]
Code language: JSON / JSON with Comments (json)

This example demonstrates how preserveAllProperties is set to true, the other properties not explicitly called out in the transformations property are still retained in the resulting dataset.

Request

{
    "transformations": {
        "CostCentre": "PadLeft(Replace([CostCentre], 'CC_', ''), '0', 10)",
        "CostElement": "PadLeft(Replace([CostElement], 'CE_', ''), '0', 10)"
    },
    "preserveAllProperties": true,
    "data": [
        {
            "Text": "This is a test",
            "CostCentre": "CC_1000",
            "CostElement": "CE_6000",
            "FiscalYear": "2022",
            "Amount": -12.34
        },
        {
            "Text": "This is a test",
            "CostCentre": "CC_1000",
            "CostElement": "CE_6000",
            "FiscalYear": "2022",
            "Amount": 844488.2364556
        }        
    ]
}
Code language: JSON / JSON with Comments (json)

Response

[
    {
        "Text": "This is a test",
        "CostCentre": "0000001000",
        "CostElement": "0000006000",
        "FiscalYear": "2022",
        "Amount": -12.34
    },
    {
        "Text": "This is a test",
        "CostCentre": "0000001000",
        "CostElement": "0000006000",
        "FiscalYear": "2022",
        "Amount": 844488.2364556
    }
]
Code language: JSON / JSON with Comments (json)