In this article, we will showcase Workato’s strong abilities in manipulating data, such as sorting and grouping records. In order to manipulate data, Workato makes it really simple. Either you can write Ruby code to manipulate the data, or more interestingly, you can use the SQL Collection by Workato connector. This connector basically transforms your data into a SQL structure where you can just write normal SQL statements against it, which is a brilliant idea. Here, we’ll showcase the SQL Collection by Workato connector option.
We will build on the same Workator Recipes we’ve been creating in the previous articles. Let’s go!
Step 1: Create a Collection from Our List of Records
First step is to create a collection from our previously created list of shipments variable. To do that, open the process shipment confirmation recipe function and add an app after the Compose CSV step.
Select SQL Collection by Workato:
Then Create list from CSV file since our data is in CSV format as of now.
Define the step as follows. Here we are basically setting what’s the input to populate the collection, what’s the collection fields, and what’s the collection name.
Now we are ready to query the collection and manipulate the data the way we want
Step 2: Query to Sort & Group the Data
Repeat the previous steps to add a SQL Collection by Workato app, but select the Query lists action now
Define the action as follows. Most importantly, note the SQL statement that we have written. This is nothing but a standard SQL statement that can be used to select, sort, group, join data, etc.
One more tiny change before we test, let’s change the logging step to log the output of the query collection step now.
Testing
That’s it from a development perspective. Now we can hit the Test Recipe button to test the recipe and make sure our logic is correct.
Conclusion
As you saw in this article, Workato makes it really simple to manipulate data by leveraging the well-known standard SQL language. We see this as a very clever way by Workato that builds on a universal language that is common and known by a large number of people, who might not be only integration specialists. That’s it for now, happy learning with ihub4us!