The current Search Action limits anyone not on the highest subscription to a return of 100 records. This requires a substantial increase in complexity of the Recipe and possibly of the Quick Base application being queried. Additionally, since the Recipe would need to loop numerous times to parse through most Quick Base applications, it causes unnecessary API calls to Quick Base and results in an inefficient Recipe.
Changing the Search Action to allow for more robust filtering - rather than requiring the filter be setup within a subsequent Repeat Action - would clear up the current inefficiencies, reduce the calls to Quick Base by up to 84%, and improve performance on the Workato servers.
Example:
I have a recipe that receives a webhook payload that includes a Start and End date. Using that information, I need to query a Quick Base table - we'll call it WEEKS for our example - for records where the Date is within the Start/End date range. For each record found, I need to create a record on another Quick Base table. For this example, let's assume that there are 4 records that match those date range criteria.
In the current setup, I must add additional layers of hierarchy to my Quick Base structure such that I have fewer than 100 records at the highest level, with each subsequent level also being limited to 100 or fewer records related to each record above it. 10k records would require at least 2 levels and 3 levels would support up to 1M records. I would then have Workato start at the top and loop through multiple sets of 100 records, based on their relationship to the higher-level records.
Quick Base Changes
To set this up, I would need to change the app so that there is a Parent table above the Weeks table. To make this easier and scalable, in this example I would build a Months table and a Years table, as we are dealing with date values.
I would then setup my Recipe as follows:
·Trigger - Webhook from QuickBase w/ Payload of Start & End dates.
·Step 1 - Search QuickBase – Years Table, Year = [Start].strftime(“%Y”) Yn = API calls
·Step 2 – For each Record in Step 1
·Step 3 – Search QuickBase – Months Table, Year = [Year – Step 1] 12 = API calls
·Step 7 – IF Start=[Week – Step 5],Create a QuickBase Record - Second Table 1 API call
·Step 8 – For each Record in Step 5
·Step 9 – IF Start>[Week – Step 5] AND End<[Week – Step 5], Create a QuickBase Record – Second Table 2 API calls
·Step 10 – For each Record in Step 5
·Step 11 – IF End=[Week – Step 5], Create a QuickBase Record – Second Table 1 API call
Pros: Limits the # of records returned in Steps 1, 3, & 5
Cons: Requires the user make modifications to the QuickBase structure; requires a total of 11 Steps in the Recipe; with data that is not parsed as clearly as this (based on date) or larger datasets, it may become necessary to make additional modifications to Quick Base and subsequent changes to the Workato recipe as the dataset grows
Total Calls to QuickBase – Yn + (Yn * 12) + API_AddRecord calls – For our example, we’ll assume 2 years and 4 weeks per month, with only 4 weeks meeting the final criteria, giving us 30 API calls to Quick Base (2 + (2*12) +4 )
After Search Action Improvements
With a more robust query function in the Search Action, I can setup my recipe as follows:
·Trigger - Webhook from QuickBase w/ Payload of Start & End dates.
·Step 1 - Search QuickBase – Weeks Table, [Week] >= Start AND [Week] <= End 1 API Call
·Step 2 – For each Record in Step 1
·Step 3 –Create a QuickBase Record – Second Table 4 API Calls
Pros: Requires only 3 Steps in the Recipe; Requires no structural changes to Quick Base app; eliminates 84% of the necessary API calls
Cons: Requires modification of the current Search Action code to utilize all parameters available with API_DoQuery from Quick Base
Total Calls to QuickBase –For our example, we’ll assume only 4 weeks meet the given criteria, giving us 5 API calls
1 person likes this idea
C
Charles S Murawski
said
about 7 years ago
Great post Does this limit apply to the SDK as well?
MedSource
Improved Search Action Filtering for QuickBase
The current Search Action limits anyone not on the highest subscription to a return of 100 records. This requires a substantial increase in complexity of the Recipe and possibly of the Quick Base application being queried. Additionally, since the Recipe would need to loop numerous times to parse through most Quick Base applications, it causes unnecessary API calls to Quick Base and results in an inefficient Recipe.
Changing the Search Action to allow for more robust filtering - rather than requiring the filter be setup within a subsequent Repeat Action - would clear up the current inefficiencies, reduce the calls to Quick Base by up to 84%, and improve performance on the Workato servers.
Example:
I have a recipe that receives a webhook payload that includes a Start and End date. Using that information, I need to query a Quick Base table - we'll call it WEEKS for our example - for records where the Date is within the Start/End date range. For each record found, I need to create a record on another Quick Base table. For this example, let's assume that there are 4 records that match those date range criteria.
In the current setup, I must add additional layers of hierarchy to my Quick Base structure such that I have fewer than 100 records at the highest level, with each subsequent level also being limited to 100 or fewer records related to each record above it. 10k records would require at least 2 levels and 3 levels would support up to 1M records. I would then have Workato start at the top and loop through multiple sets of 100 records, based on their relationship to the higher-level records.
Quick Base Changes
To set this up, I would need to change the app so that there is a Parent table above the Weeks table. To make this easier and scalable, in this example I would build a Months table and a Years table, as we are dealing with date values.
I would then setup my Recipe as follows:
Pros: Limits the # of records returned in Steps 1, 3, & 5
Cons: Requires the user make modifications to the QuickBase structure; requires a total of 11 Steps in the Recipe; with data that is not parsed as clearly as this (based on date) or larger datasets, it may become necessary to make additional modifications to Quick Base and subsequent changes to the Workato recipe as the dataset grows
Total Calls to QuickBase – Yn + (Yn * 12) + API_AddRecord calls – For our example, we’ll assume 2 years and 4 weeks per month, with only 4 weeks meeting the final criteria, giving us 30 API calls to Quick Base (2 + (2*12) +4 )
After Search Action Improvements
With a more robust query function in the Search Action, I can setup my recipe as follows:
Pros: Requires only 3 Steps in the Recipe; Requires no structural changes to Quick Base app; eliminates 84% of the necessary API calls
Cons: Requires modification of the current Search Action code to utilize all parameters available with API_DoQuery from Quick Base
Total Calls to QuickBase –For our example, we’ll assume only 4 weeks meet the given criteria, giving us 5 API calls
1 person likes this idea