topic Re: How to fetch all matching records using data_table_lookup? in Workato Pros Discussion Board

How to fetch all matching records using data_table_lookup?

Bhagya_pola — Tue, 15 Jul 2025 08:11:36 GMT

Hello Team,

Has anyone worked with the data_table_lookup formula in Workato?

I'm trying to fetch multiple records from a data table based on a condition, but it seems to only return the first matching record, not all.

Here’s an example of my data table:

Student Name Class Course Joined

StudentName	Class	Course Joined
Student1	7th	Python
Student2	6th	Java
Student3	7th	Java
Student4	10th	JavaScript
Student5	10th	HTML & CSS
Student6	10th	Python
Student7	8th	JavaScript

(Assume this table is sorted by Created Time in ascending order.)

When I use the formula below to fetch student names who joined the Python course, I only get the first matching record:

data_table_lookup("<datatable folder path>", "StudentDetails", CourseJoined: "Python")["StudentName"]

This only returns Student1, but I was expecting it to return all matches (like Student6 as well).

I've checked the documentation, but I couldn't find anything helpful for this use case. Would love to know if anyone has come across a workaround or better way to handle this.

Thanks in advance!

Re: How to fetch all matching records using data_table_lookup?

rajeshjanapati — Tue, 15 Jul 2025 08:54:11 GMT

Hi @Bhagya_pola,

As per my understanding and experience:

The data_table_lookup formula in Workato is designed to return only the first matching record, not all matches. This is expected behavior and is by design — it does not support retrieving multiple matching records directly.

Same like lookup tables also, with lookup formula, we are able to retrieve only one value, but there we can make use of the "search entries" action to retrieve multiple values.

If your use case requires fetching all records that match a certain condition (e.g., all students who joined the "Python" course), then you’ll need to use an alternative approach like:

by using SQL Transformations by Workato:

SELECT StudentName FROM StudentDetails WHERE CourseJoined = 'Python'

This will return all matching records, not just the first one.

Re: How to fetch all matching records using data_table_lookup?

Bhagya_pola — Tue, 15 Jul 2025 09:06:56 GMT

Hello @rajeshjanapati ,

Thank you for your input!

My intention behind using the formula was to reduce task consumption and minimize the number of steps in the recipe. Currently, I'm using the Search records action with filter criteria from the data table, and I was hoping to replace that with a formula-based approach.

Unfortunately, it doesn't seem to achieve the desired result in this case.
Thanks again for your response!

Re: How to fetch all matching records using data_table_lookup?

rajeshjanapati — Tue, 15 Jul 2025 09:44:16 GMT

Yes, to reduce the number of steps and tasks, instead of using Search Records, I would recommend using SQL Transformations by Workato application ( with in the query you can pass the conditions as you mentioned in search records action). With this approach, you can query data tables directly within the step. It consumes only one task, and the output can be stored as a file in FileStorage — all within a single task execution.

Regarding the data_table_lookup formula — by default, it's designed to return only the first matching record. I'm not exactly sure why it's built this way, but it might be intended for quick condition checks, similar to how ternary operations work in programming.

Re: How to fetch all matching records using data_table_lookup?

Bhagya_pola — Tue, 15 Jul 2025 10:49:17 GMT

Yes, we can go with SQL transformations as well, but I don’t need that for my use case.

I’m using a scheduler (data table polling won’t work for my scenario), and within that, I’m using Search records as the initial step. So I was looking to set up a trigger condition using the data_table_lookup formula instead.

Re: How to fetch all matching records using data_table_lookup?

rajeshjanapati — Tue, 15 Jul 2025 11:03:26 GMT

Yes, got it! While SQL Transformations are ideal for handling millions of records and applying complex filtering conditions, for your use case, implementing the condition directly in the Scheduler is a smart idea — it helps you save a step (by skipping the Search Records) and also reduces task consumption.

However, unfortunately, the data_table_lookup formula does not support retrieving multiple matching records — it only returns the first match. So in this case, the only viable solution is to proceed with the Search Records step to fetch all relevant entries.

Re: How to fetch all matching records using data_table_lookup?

shivakumara — Fri, 25 Jul 2025 14:59:38 GMT

Hi @Bhagya_pola ,

Just a quick thought:
In Workato, using a logger or a variable typically counts as one task. Similarly, actions like searching records (via data table) or performing SQL transformations also consume one task.

However, there's a key difference:

Logger does not return a usable response for further processing.
Search records or SQL transformations, on the other hand, do provide meaningful output that can be used downstream.

So, it's important to be smart and choose the option that best fits your use case—especially when task optimization and functionality are both critical.

Thanks and Regards,
Shivakumara K A

Re: How to fetch all matching records using data_table_lookup?

Obugari — Wed, 30 Jul 2025 14:13:10 GMT

Hi @Bhagya_pola ,

Using datatable_lookup formula we can get only one record because it will used for get one matched record from table it won't give multiple values but you can apply multiple conditions.

If you want to get the multiple values use search option in data table step and apply filter conditions at there ,it will be best option for your scenario .

Thankyou