Common Parameters - State Solutions

The common parameters are applied against each API and have the ability to influence the outcome of the call being made.

Version 1

There are a number of common parameters that can be applied to each payload. Some parameters do differ slightly depending on the API being called, that will be explained here.

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.

Filter

Filtering the data will be a regular requirement when using the suite of API’s offered.

The syntax required follows the standard WHERE clause approach offered to SQL DB instances. There are numerous resources online for understanding the required syntax. The following links provide a very strong overview.

https://www.w3schools.com/sql/sql_where.asp
https://www.tutorialspoint.com/sql/sql-where-clause.htm
https://docs.microsoft.com/en-us/sql/t-sql/queries/where-transact-sql

When filtering within this framework, the following is recommended.

Always bookend your field names with square brackets. As shown below, Field2 is a property within the resulting dataset. This ensures that the engine recognises it as a complete property. This is especially pertinent in cases where the property name may contain spaces and other special characters.

"filter": "[Field2] <> 'the'"
Code language: JSON / JSON with Comments (json)

Use single quotes when encapsulating string values within the filter property. Given the payload requires the filter expression to be a string, double quotes need to be escaped and this may lead to confusion and ambiguity.

It is possible to use double quotes but is not advised.

"filter": "[Field2] <> 'the'"
Code language: JSON / JSON with Comments (json)

Sort Order

The sortOrder property will change it’s shape when being used between the API’s. i.e. it depending the API, it will float between needing to be a string and an object.

In most cases, it’s required to be an object and if it’s to be a string, it will be called out in the documentation of the API in question.

String

In cases where the sortOrder property only accepts a string argument, the value should be one of two options, ASC (ascending) or DESC (descending).

"sortOrder": "DESC"
Code language: JSON / JSON with Comments (json)

Object

In the case where it’s required to be an object, if the API accepts multiple datasets and the same property exists across more than one of those datasets, you may need to be explicit about which dataset the sorting action needs to be applied to.

The syntax for this is DataSetName.PropertyName.

"sortOrder": {
    "Header.DocumentNumber": "DESC",
    "CostCentre": "ASC",
    "CostElement": "ASC"
}
Code language: JSON / JSON with Comments (json)

In cases where a single dataset is permitted, there will be no need to provide the dataset name as a prefix to the property.

"sortOrder": {
    "DocumentNumber": "DESC",
    "CostCentre": "ASC",
    "CostElement": "ASC"
}
Code language: JavaScript (javascript)

Schema

In most cases, this property is required to be a single level object. If the API accepts multiple tables in the data parameter, the schema object will be a multi-level object, the first level being the dataset name.

"data": [
    {
        "Amount": 124
    },
    {
        "Amount": 96.1
    }
]
Code language: JSON / JSON with Comments (json)

In the example above, the Amount field will be defined as an integer. The reason for this is due to the first item not containing decimal in the value of property.

Without specifically calling it out in the schema, the result of the second item will be a numeric amount without the decimal places.

The way to correct this is to explicitly state that the Amount field be of type Double.

"schema": {
    "Amount": "Double"
}
Code language: JSON / JSON with Comments (json)

When the data parameter accepts more than a single dataset, the schema parameter transforms to being a multi-level object.

For example, this data parameter contains multiple datasets.

"data": {
    "Header": [
        {
            "DocumentNumber": "00001",
            "CompanyCode": "1000",
            "FiscalYear": "2022",
            "DocumentType": "AA",
            "HeaderText": "Test Document 1",
            "Date": "25/05/2022"
        }          
    ],
    "LineItems": [
        {
            "DocumentNumber": "00001",
            "CompanyCode": "1000",
            "FiscalYear": "2022",
            "Vendor": "2600",
            "ItemText": "Vendor line 1",
            "Amount": 110
        },
        {
            "DocumentNumber": "00001",
            "CompanyCode": "1000",
            "FiscalYear": "2022",
            "GLAccount": "607000",
            "CostCenter": "10001000",
            "ItemText": "Expense line 1",
            "Amount": 100
        },
        {
            "DocumentNumber": "00001",
            "CompanyCode": "1000",
            "FiscalYear": "2022",
            "GLAccount": "607000",
            "CostCenter": "10001000",
            "ItemText": "Tax line 1",
            "Amount": 10
        }                  
    ]
}
Code language: JSON / JSON with Comments (json)

Therefore, the dataset needs to be explicitly called out when defining the custom schema object.

"schema": {
    "Header": {
        "FiscalYear": "Integer"
    },
    "LineItems": {
        "FiscalYear": "Integer"
    }
}
Code language: JSON / JSON with Comments (json)

The following types are permitted within the schema option. Anything outside of this group will be treated as a string. Type Array and Object are not permitted.

String
Int/Integer
Bool/Boolean
Date/DateTime
Double
Float

Advanced Options

The advanced options are in place to enable specific control over certain aspects of the framework that ultimately required it and where the framework is unable to make that determination automatically.

cultureName

"advancedOptions": {
    "cultureName": "en-AU"
}
Code language: JSON / JSON with Comments (json)

Is in place to help control (amongst other things) the parsing of dates and formatting of numbers.

"data": [
    {
        "Date": "04/05/2022"
    }          
]
Code language: JSON / JSON with Comments (json)

With the above example, if the culture wasn’t explicitly provided, the date would be interpreted as en-US (English, United States) as that is the default culture of the service the API’s run on.

If it were to be interpreted with that culture, the resulting date would be the 5th of April, 2022 which is interpreted as mm/dd/yyyy.

However, in Australia, the date should be interpreted as the 4th of May, 2022 which is dd/mm/yyyy. Therefore, the culture name should be explicitly stated as en-AU (English, Australia).

This highlights that culture matters and it should be used with extreme amounts of care and consideration as it has the potential to change the outcome of each API with great significance.

isBoolean

"advancedOptions": {
    "isBoolean": [
        "Text"
    ]
}
Code language: JSON / JSON with Comments (json)

The underlying framework has no concept of the true/false boolean type. The equivalent values are represented as 1 (true) and 0 (false).

To overcome this shortcoming, this advanced option is in place to be used to control which values in the resulting dataset should be presented as proper JSON boolean values.

{
    "transformations": {
        "Text": "Contains([Text], 'Test', true)"
    },
    "advancedOptions": {
        "isBoolean": [
            "Text"
        ]
    },    
    "data": [
        {
            "Text": "This is a test"
        }
    ]
}
Code language: JSON / JSON with Comments (json)

Using the example above, explicitly specifying that the Text be treated as a boolean produces the following result.

[
    {
        "Text": true
    }
]
Code language: JSON / JSON with Comments (json)

If the property was not explicitly called out in the isBoolean option, the response would be an integer of either a 1 or 0. In the case of the expression above, it would result in true, and therefore, 1.

[
    {
        "Text": 1
    }
]
Code language: JSON / JSON with Comments (json)