Working with Variable JSON Responses

I had a project where I had to integrate an event app in Workato and it proved a challenge because each event had their own keys so I couldn’t use datapills. They used long random strings for their key names instead of a standard name each time. For instance, all of our events have a field called ‘Tradeshow Name’ but I couldn’t use the ‘Tradeshow Name’ datapill because for one event the key would be  ‘text0745285851’ and for another it would be ‘text5295746837’.

Without being able to use datapills, I came up with a solution that is almost painless although it does require a little bit of work and it involves using lookup tables. Now the only work I need to do is upload a csv to the lookup table anytime we have a new event with the proper event field keys and the standard field names.

Lookup Table

I created a lookup table with three columns:

• Workato Field: The standard field name that we use for all events
• Event Key:  The field name from our event app for a specific event
• Event ID: An individual event ID that we can use to specify which key to lookup

With those values in place, we can use a formula in the fields for the app where we want to send our data. In my example, I used the ‘Values’ key as a datapill as it has the nested objects I would want to extract. Then it was a simple inline lookup.

In this example, ‘Event Fields’ is the name of the lookup table and I lookup to see if the Event ID has a value for that particular field, if it does it will add that value as the key.

With this solution in place, there could be multiple events going on at one time but we could still use one Workato recipe because the key would be determined by the lookup table which made this extremely scalable.