cancel
Showing results for 
Search instead for 
Did you mean: 

How to fetch all matching records using data_table_lookup?

Bhagya_pola
Deputy Chef III
Deputy Chef III

 

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

StudentNameClassCourse Joined
Student17thPython
Student26thJava
Student37thJava
Student410thJavaScript
Student510thHTML & CSS
Student610thPython
Student78thJavaScript

(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!

 

7 REPLIES 7

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.

shivakumara
Executive Chef III
Executive Chef III

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

Obugari
Deputy Chef III
Deputy Chef III

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