Satisfying input schema

When sending data in batch format to a service or database, you may find that there are particular 'input schema' requirements in terms of how the data is structured.

For example a SQL database (e.g. MS SQL) may require that data is sent in a 'flat' format (i.e. no 'nested' objects)

Or a service has a 'batch update' operation with very specific requirements as to how the data is formatted.

Service input schemas

How to determine input schema

When using certain operations you may need to pass data payloads from previous steps as input.

For example, you may have a list of new accounts that you want to add to Salesforce which are in the format of a simple list and need to be transformed into a list of key / value pairs in order to be accepted by the Salesforce API:

The operation to do this is Salesforce's 'Batch create records':

To find out the exact input schema for this operation we can either do a hardcoded test run in the Tray Build UI, or to save time we can head to the Tray Form Builder dev tool

So we can use the Form builder tool to:

1. Choose a service
2. Choose an existing authentication from all workspaces you have access to
3. Choose an operation
4. Enter hardcoded test inputs for the operation
5. Click 'Submit' to send the operation request to the 3rd party service

When hardcoding the inputs you will be able to see the raw json format of the input required by the 3rd party service.

You can copy this and then make use of it in the Transformer tool, as explained below.

Transforming the data

Once we know the input schema we can make use of the data transformer tool

So we can use the data transformer tool to:

1. Enter the initial data payload
2. Enter the desired format of the data payload
3. Click 'Submit' to obtain the transformation script and the builder snippet code
4. Copy the builder snippet code
5. Open your workflow and find the appropriate step where you want to implement the transformation
6. Paste the transformation snippet
7. Map the data to be transformed from the relevant step in your workflow into the transform script
8. Map the output of the transform script to the destination service step
9. Run your workflow to initiate the transformation process and send the desired data to the destination service

SQL database input schemas

Flattening a single nested object

If you have a payload with a nested structure that needs to be flattened into a single layer:

{
  "first_name": "Sara",
  "last_name": "Ross",
  "email": "sara_ross@live.com",
  "address": {
    "house_number": "144",
    "street": "Acacia Avenue",
    "post_code": "NT13 0NH"
  }
}

{
  "first_name": "Sara",
  "last_name": "Ross",
  "email": "sara_ross@live.com",
  "address.house_number": "144",
  "address.street": "Acacia Avenue",
  "address.post_code": "NT13 0NH"
}

Flattening an array of nested objects

Likewise if you had an array of nested objects such as:

[
  {
    "full name": "Kenneth Balistreri",
    "address": {
      "street": "68716 Osinski Port",
      "city": "Fleurus"
    }
  },
  {
    "full name": "Harvey Gusikowski",
    "address": {
      "street": "889 Kilback Rue",
      "city": "Tarxien"
    }
  },
  {
    "full name": "Patrick Wolff",
    "address": {
      "street": "10592 Reed Prairie",
      "city": "Flying Fish Cove"
    }
  }
]

[
  {
    "full name": "Kenneth Balistreri",
    "address.street": "68716 Osinski Port",
    "address.city": "Fleurus"
  },
  {
    "full name": "Harvey Gusikowski",
    "address.street": "889 Kilback Rue",
    "address.city": "Tarxien"
  },
  {
    "full name": "Patrick Wolff",
    "address.street": "10592 Reed Prairie",
    "address.city": "Flying Fish Cove"
  }
]

Working with dynamic payloads