I have a step that searches for items in QuickBase that which stops at 100 items found. There should be more items in this query. Is there a limit to the number of items that can be returned in this search function?
Best Answer
A
Allan Teng
said
over 6 years ago
Hi Scott, yes. There is a limit to all search results. You should segment your database such that the search query you are making is a subset of the entire table.
What were you trying to achieve?
Answer
Allan Teng
said
over 6 years ago
Hi Scott, yes. There is a limit to all search results. You should segment your database such that the search query you are making is a subset of the entire table.
What were you trying to achieve?
S
Scott Talarico
said
over 6 years ago
I created a way in QuickBase to query all future events and update my google. Goole calendar is shared with my subcontractors which allow them to know the information on the event. This query exceeded the limit. The minimal search I need to do is 14 days from the scheduled event to update the google so my subcontractors have accurate information. The issue is that in certain 14 day spans it will exceed 100.
I can create separate searches (1 day before the event, 2 days before the event....14 days before the event) but that will require me to clone the steps and repopulate the "for each" for the individual searches. Since there is a higher chance of error when I clone the steps I was looking for a way to have one set of "for each" steps.
Are there any suggestions without having to go through making 14 separate "for each" steps.
1 person likes this
C
Cameron Blackmon
said
over 6 years ago
I had no idea that a search only returns 100 records! That's really unfortunate... I'm going to have to go back through my recipes and add loops that adjust the dates for each loop, although I have no idea how to do that. Any suggestions Workato team?
CJ Wee
said
over 6 years ago
Hi Scott/Cameron,
Unfortunately, there is no work around for this. The search steps will need to be more specific to keep the number of records returned below the limit.
M
MedSource
said
about 6 years ago
Actually, there is a solution to this, but the Workato Product Team doesn't seem to understand the need. The solution is to allow a more complex filter in the Search Action, rather than to require it be setup in the Repeat Action. The Quick Base API (https://help.quickbase.com/api-guide/index.html#do_query.html#Request_Parameters) allows for standard query parameters to be sent using the call API_DoQuery, but Workato's Search Action limits us. Not only is the current structure in-efficient from a user perspective, it's bad development. You're performing 2 actions when you could perform 1.
If I have a database of 1000 records and I want to query a subset of those records, I have to create a field in Quick Base that will identify the subset that I need to query. This requires me to do 1 of 2 things. First, I can setup a Parent table to the table being queried and start my Workato Search Action on the Parent table. Or, I can create a formula that would need to utilize the information in each record to self-identify if it should be queried. For most of us, this would typically be a feat of psychic proportions - literally. If I am querying a table based on information that is dynamically determined, rather than static, how can my formula possibly work?
Here's my real-world example:
Every month, I need Workato to generate Invoices for a given period based on a webhook that passes the Start Date and End Date for the period. We utilize Work Weeks to segment Time Sheets and determine to which Invoice to assign the Timesheet. I need to be able to query the Work Weeks table for records between the Start & End Dates of my Invoice - as provided in the webhook payload, which I would then use to create an Accounting Week (Work Week + Client). Unfortunately, my Work Weeks table has 52 weeks for every year and currently 250 in total.
Now, this is a fairly straightforward example and one that I can actually create a formula to identify which records to query - I created a checkbox that shows True if the Start Date is less than 180 days ago. I've used this checkbox in the Search Action which keeps my return results under the 100 limit, but I should not have to do this. I also know that the 100 record limit is an arbitrary number that is determined by the subscription level of your account, with the highest account levels allowed a return of 1000 records. Still, I should be able to setup a filter / query in the initial Search Action so that only the records that I'm looking for are returned, rather than having to setup the filter in the Repeat action. Right now, without my formula, the initial Search Action would pull the first 100 records it came across and would then do a 2nd action on those records to compare the Start Date of each Record to the Start & End Dates provided by the webhook. Most likely, the 2nd action would never find the records that it was looking for, so I would have to then figure out a way to loop my search so that all records are searched. In the end, we would end up with at least 3 API_DoQuery calls to Quick Base for the initial search and then numerous actions within Workato to do the date comparisons.
Scott Talarico
QuickBase Search Limits
I have a step that searches for items in QuickBase that which stops at 100 items found. There should be more items in this query. Is there a limit to the number of items that can be returned in this search function?
Hi Scott, yes. There is a limit to all search results. You should segment your database such that the search query you are making is a subset of the entire table.
What were you trying to achieve?
Allan Teng
Hi Scott, yes. There is a limit to all search results. You should segment your database such that the search query you are making is a subset of the entire table.
What were you trying to achieve?
Scott Talarico
I created a way in QuickBase to query all future events and update my google. Goole calendar is shared with my subcontractors which allow them to know the information on the event. This query exceeded the limit. The minimal search I need to do is 14 days from the scheduled event to update the google so my subcontractors have accurate information. The issue is that in certain 14 day spans it will exceed 100.
I can create separate searches (1 day before the event, 2 days before the event....14 days before the event) but that will require me to clone the steps and repopulate the "for each" for the individual searches. Since there is a higher chance of error when I clone the steps I was looking for a way to have one set of "for each" steps.
Are there any suggestions without having to go through making 14 separate "for each" steps.
1 person likes this
Cameron Blackmon
CJ Wee
Hi Scott/Cameron,
Unfortunately, there is no work around for this. The search steps will need to be more specific to keep the number of records returned below the limit.
MedSource
Actually, there is a solution to this, but the Workato Product Team doesn't seem to understand the need. The solution is to allow a more complex filter in the Search Action, rather than to require it be setup in the Repeat Action. The Quick Base API (https://help.quickbase.com/api-guide/index.html#do_query.html#Request_Parameters) allows for standard query parameters to be sent using the call API_DoQuery, but Workato's Search Action limits us. Not only is the current structure in-efficient from a user perspective, it's bad development. You're performing 2 actions when you could perform 1.
If I have a database of 1000 records and I want to query a subset of those records, I have to create a field in Quick Base that will identify the subset that I need to query. This requires me to do 1 of 2 things. First, I can setup a Parent table to the table being queried and start my Workato Search Action on the Parent table. Or, I can create a formula that would need to utilize the information in each record to self-identify if it should be queried. For most of us, this would typically be a feat of psychic proportions - literally. If I am querying a table based on information that is dynamically determined, rather than static, how can my formula possibly work?
Here's my real-world example:
Every month, I need Workato to generate Invoices for a given period based on a webhook that passes the Start Date and End Date for the period. We utilize Work Weeks to segment Time Sheets and determine to which Invoice to assign the Timesheet. I need to be able to query the Work Weeks table for records between the Start & End Dates of my Invoice - as provided in the webhook payload, which I would then use to create an Accounting Week (Work Week + Client). Unfortunately, my Work Weeks table has 52 weeks for every year and currently 250 in total.
Now, this is a fairly straightforward example and one that I can actually create a formula to identify which records to query - I created a checkbox that shows True if the Start Date is less than 180 days ago. I've used this checkbox in the Search Action which keeps my return results under the 100 limit, but I should not have to do this. I also know that the 100 record limit is an arbitrary number that is determined by the subscription level of your account, with the highest account levels allowed a return of 1000 records. Still, I should be able to setup a filter / query in the initial Search Action so that only the records that I'm looking for are returned, rather than having to setup the filter in the Repeat action. Right now, without my formula, the initial Search Action would pull the first 100 records it came across and would then do a 2nd action on those records to compare the Start Date of each Record to the Start & End Dates provided by the webhook. Most likely, the 2nd action would never find the records that it was looking for, so I would have to then figure out a way to loop my search so that all records are searched. In the end, we would end up with at least 3 API_DoQuery calls to Quick Base for the initial search and then numerous actions within Workato to do the date comparisons.