Load Lookup Table Into Collection

[michael-zachar]

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?

[gary1 · Accepted solution]

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):

[michael-zachar]

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.

[michael-zachar]

Can confirm, this worked great!