topic Re: Load Lookup Table Into Collection in Workato Pros Discussion Board

Load Lookup Table Into Collection

michael-zachar — Tue, 26 Mar 2024 22:47:29 GMT

I'm trying to take data from a lookup table and put it into a collection for complex searches, but I cannot for the life of me figure out how to get from querying all entries from the lookup table to getting them into the collection. The lookup table query returns a list that does not allow me to access the entries datapill to use as the datasource for the collection. Any example recipes that show how to do this?

Re: Load Lookup Table Into Collection

gary1 — Wed, 27 Mar 2024 00:37:08 GMT

Wow, I never realized the lookup table output was structured this way. If you don't need the Workato IDs, the below steps will work. If you do need the Workato IDs, let me know and I'm sure there's another way.

Use the Lookup Table action to get the whole table
Add a JSON parser with this as the schema (you could adjust the data types as needed, but prob not necessary)

[{ "col1": "2", "col2": "two", "col3": null, "col4": null, "col5": null, "col6": null, "col7": null, "col8": null, "col9": null, "col10": null }]

In the JSON parser document field, enter this in formula mode: [entries datapill].pluck('entry').to_json
Add a SQL Collection action and use the "Array" output list from the JSON parser as input
Query the Collection

It should look like this:

JSON parser:

Query:

Query output (as CSV):

Re: Load Lookup Table Into Collection

michael-zachar — Wed, 27 Mar 2024 15:28:57 GMT

Thanks, I'll try that out later this week. Currently I'm using a workaround where I made the table into a CSV and just pull it in from our SFTP site while I do testing around the rest of the recipe.

Re: Load Lookup Table Into Collection

michael-zachar — Wed, 03 Apr 2024 19:23:57 GMT

Can confirm, this worked great!