- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ03-26-2024 03:47 PM
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?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ03-26-2024 05:37 PM
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):
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ03-26-2024 05:37 PM
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):
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ03-27-2024 08:28 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ04-03-2024 12:23 PM
Can confirm, this worked great!

