Tray Platform / Moving Data / Fallback values

Fallback values

Overview

A very powerful Tray.io feature is the ability to set Fallback Values.

This can be used for occasions when you know that data being returned will be inconsistent in some way.

For example, when pulling data from one service to push it into a database, if a particular field is empty for some records, you may wish to set this as ‘not found’ for the purpose of your database.

Or you may find that some services return nothing at all if a particular field has no value - causing errors in your subsequent workflow steps.

The examples given below should give you a good understanding of when you can make use of the Fallback Values feature.

Examples

1 - string fallback value

The following workflow shows how we may make use of fallback values when pulling records from Salesforce:

In this case we are:

  • Using the Salesforce 'Get Records' operation to retrieve Salesforce accounts, including the name and phone number
  • Looping through each account and adding as a new row to a Google Sheet
  • Setting a fallback for the 'Phone' value as we know that it is likely that there are a lot of accounts in Salesforce with no value entered for phone

The fallback value is set by clicking on 'Edit fallback value' as per above screenshot.

You can then enter the required fallback value as a simple string:

So when we run this workflow, the ultimate result in Google Sheets will include accounts with 'Not Found' in the Phone column:

2 - jsonpath as fallback

A similar example imagines a situation where you are dealing with Salesforce Contacts.

In this case, you ideally want to output their 'Mobile Phone' to a Google Sheet.

However, since the 'Mobile Phone' of a contact is not always available, you wish to use their 'Business Phone' as a fallback.

This can easily be done by using another jsonpath as your fallback value:

3 - config data as fallback

You can also use Config Data in Workflow Settings to set a fallback value.

Say, for example, we are wanting to enter a personal email address for each contact. If no email is found we can use a company email that is stored in Config:

This can then be entered as a fallback value in the properties panel, using e.g. $.config.acme_company_email:

Note that in the above situation you would likely be making use of the 'conditional' Branch and/or Boolean connector to determine which company email you will be using (e.g. do you have a company / account ID to identify what company somebody works for?)

4 - service API returns no result

Some services, such as Airtable and Hubspot, will not return a field at all if there is no value stored for it.

This can cause problems in that any subsequent steps which are attempting to pull that field for every record will error if it is not there.

For example if we use the 'List records' operation in Airtable to get the following records:

Then the orders which have no email address recorded will not return the email field at all:

The following workflow illustrates how to deal with this, using Google Sheets as a generic placeholder for any service you may be wanting to push the Airtable data into:

From the properties panel of the Add order as row step you can see that we are including 'email' as a field to be pushed.

We can then also set the fallback value to be e.g.'null' (or 'not found' etc.):

Inspecting the input logs from the debug tab for the Sheets Add order as row step will then show that the email 'null' value has been passed succesfully:

And all orders have been pushed to Google Sheets:

What we have done here is similar to the Object Helper 'enforce object structure' operation

Important Notes

Fallback values won't work on a step that has errored

Fallback values won't work on a step if the third party API returns an error. In this case, you should use the Manual error handling method instead.

To understand this, let's consider an example where you wish to merge data received from two different sources and store the merged data in Google Sheets.

The two sources considered in this example are a Webhook trigger and a Redshift table.

The Webhook trigger is receiving JSON data, and we are fetching records from the Redshift table using the 'Run SQL query' operation.

The next step is merging this data using an Object Helpers connector:

In a situation where the Redshift table is empty or if the table doesn't exist we might try to set an empty object as a fallback value for the 'Target' field.

This would be done by clicking the 'Target' field and 'Set fallback'.

Then the 'Type selector' would be set as 'object' for the Fallback value:

Unfortunately, in this case it won't work!

Fallback values work if, for example, a service returns a payload and a particular field is missing (as explained in the example section above).

In this case however Redshift returns no data and the API is returning an error code. So here we need to use error handling rather than fallback values. Redshift returns the following error message:

{
"message": "Failed to reference: $.steps.redshift-1.result[0]: This step failed. To access it's error output, you'll need to refer to it by '$.errors.<name>' reference."
}

A simple solution to this is to handle the Redshift connector error manually. To do so for the Redshift connector in the properties panel, click 'Handle errors' and set the error handling to 'Manual'.

The way we handle an error from the Redshift step here is:

  • Success: This means Redshift has successfuly returned data that we can merge with the Webhook data and then store it into google sheets.

  • Error: This means Redshift has returned no data. In which case we can ignore Redshift and only use Webhook data to add to google sheets.

PLEASE NOTE: There is no fixed way of working with error handling. This is just one simple example of handling errors. Exactly how you handle errors depends entirely on your specific requirements.