cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Load Lookup Table Into Collection

michael-zachar
Deputy Chef II
Deputy Chef II

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?

1 ACCEPTED SOLUTION

gary1
Executive Chef III
Executive Chef III

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:

gary1_2-1711499727714.png

JSON parser:

gary1_1-1711499675258.png

Query:

gary1_3-1711499748691.png

Query output (as CSV):

gary1_4-1711499776705.png

 

 

 

 

View solution in original post

3 REPLIES 3

gary1
Executive Chef III
Executive Chef III

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:

gary1_2-1711499727714.png

JSON parser:

gary1_1-1711499675258.png

Query:

gary1_3-1711499748691.png

Query output (as CSV):

gary1_4-1711499776705.png

 

 

 

 

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.

Can confirm, this worked great!