Are You Frustrated Because Your Workato SQL Collection List Isn't Updating?
Workato has a few different applications that allow you to work with lists of items. One of the lists is contained in the variables application.
Another is in the SQL Collection application. The SQL Collection Application comes with the ETL package so it is possible that you might not have it. If after reading this article you realize that you need it, contact your Workato representative.
In an interesting architectural implementation, Workato has almost no native functionality. Instead it has a few primitive control structures (If and loops) and everything else is implemented as applications. So Variables by Workato is an application that allows you to have the functionality of variables including a list type. Another application could exist that enables packages with variables that work a different way.
The Variables by Workato application has the following actions available:
Let's Dive Into the Workato SQL Collection Package
The SQL Collection is an optional package that allows you to treat a list as a database and run a limited set of SQL queries on the list. This conveniently lets you use SQL to filter and create sub lists of items.
Unfortunately, the purpose of this article isn’t to go into the basic functionality of the lists, but instead to highlight how the lists may not function exactly how you imagine and how the two types of lists work differently when they are updated.
Step 1
Here is a short sample recipe. The comments explain what each line of code is doing.
Step 2
In Step 2 we pull 10 records back from the postgres database. You can see from the Job history that 10 records are retrieved.
Step 3
In step 3 we create a Variables by Workato List using the Postgres data as a source. Because we use the Rows field, a batch create is done and all the rows from the Postgres query are loaded into the list.
When we look at the Job data, we can see that the Budget Variables List has the same 10 items.
Step 4
In Step 4 we log a message using a field from Step 3 that shows how many elements are in the list.
The Jobs data shows that step 3 has 10 elements as expected.
Step 5
In step 5 we add a single item to the Budget Variables List.
The Job data returns the value of 11, indicating that the list now has 11 elements.
Step 6
In step 6 we log the list size by referring to the list size field in step 3.
The Job report shows that step 3 is updated to 11 elements.
What this means is that as you update the list in step 3, you can always refer to the fields in step 3 to get the most recent size and elements.
It turns out that SQL Collection Lists do not work the same way as Variable by Workato Lists which is confusing. As you update the SQL Collection List, the original step where the list was defined is immutable. Instead you always must query the list to get the most up to date state.
Step 7
In step 7 we create a SQL Collection List using step 2 as the source
In the Job report we can see there are 10 items.
Step 8
In step 8 we refer to the list in step 7 to log the number of rows.
The Jobs report reflects 10 elements.
Step 9
In step 9 we query step 7 and we get 10 rows back.
Step 10
In step 10 we delete some of the items.
On a delete the Jobs report doesn’t show anything interesting.
Step 11
In step 11 we again log the number of elements in step 7 which has had some items deleted.
The Jobs report shows that step 7 still has 10 elements.
Step 12
In step 12 we query step 7.
The Jobs report shows 5 elements.
Step 13
Step 13 logs the number of rows in the query and it shows 5 rows.
So as you can see the original step 7 is immutable. However the underlying data object does get updated if you use inserts/deletes. To get access to its updated state you must always requery using a SQL Collection Query. In addition, the queries are also immutable AND their underlying data is immutable as well. This means that if you run a query and then you want to requery it, you must create a new SQL Collection List and use the query as the source. Once you have that SQL Collection List you can create a SQL Collection Query against it.
