Currently trying to help solve a problem and i think the lookup tables might be able to solve it. We have an automation the runs in a clients ERP and sends a call to another system. The trigger that kicks everything off could send one call or could send 100. The issue is that if it is a large number the receiving system will attempt to process them all at once and gets hung. What i was thinking of doing is using apiant to catch the calls and store the payload in the lookup tables. Then in a second automation, grab X at a time to process every 15 minutes or something. I haven’t done much with the tables so I wanted to see how best to go about that and how i would grab just the top X records.
Hi Brent,
Yes the Lookup Tables approach should work for your case.
Like you describe, store the info into a lookup table. Should be best to use a keygroup starting with “temp_” so the records will automatically be purged from the database after 60 days.
Then build a custom polling trigger that uses the Keyvalue Storage - Select module to fetch all records from the lookup table.
The trick to having the polling trigger only trickle out so many new records is to set this value in the Trigger - Emit New Items module to the desired value:
In this example, the polling trigger would only emit up to 5 new records each time the polling automation is run. Records that are not emitted would then be emitted the next time the polling automation is scheduled to run.
In that manner, you should get the desired slow trickle of records to be processed.
Let us know if you have any trouble getting it working as needed!
Robert
I got this working well but wanted to tweak it if possible. so when the second automation runs to release the records i have it running every few minutes to release a handful each. is it possible to do two things.
- when the catch side receives a call, change the release automation execution to run every X minutes.
- when the release automation runs our of new records, change the interval to a longer time or turn off completely
not sure if either of those are possible. i just dont want the release automation running constantly if there isnt anything to release
Perhaps you could have a 3rd polling automation that has no trigger, with the actions being to read the count of items from the lookup table and if greater than zero then use an “execute automation” action to invoke the release automation?
So I am trying to accomplish two things here. first reduce the constant pinging overhead. I assume it is a pretty minimal call but why run if unnecessary. second, when it runs out of records to process i want to perform another action to ping the source system and verify that all records were sent originally and if any remain, update them to trigger the catch process again to store the value in the table. Because of the nature of the API we dont want to ping it constantly unless needed because it charges after a certain point.
Ideally, we catch 100 records. the release runs say 5 at a time every 2 minutes. (to this point it is all working exactly this way). After it runs them all, it pings one last time to see if any are remaining in the source system and if so, triggers another automation to kick it off again. Any other time it just sits idle without pinging out, just waiting to catch a call. then when it catches another batch, the release ramps back up.
I can only imagine it working as 3 automations:
-
The catcher webhook automation that receives work and places into a lookup table queue for later processing. The catcher would need to clear a lookup table flag for the controller automation to check to see if a final ping is needed.
-
The releaser automation that reads from the lookup table queue and processes a small number of records at a time. This automation is turned off and doesn’t run on its own.
-
A polling controller automation that reads from the lookup table queue. If the queue is not empty, it invokes an “execute automation” action to run the releaser automation. Is ok if this action executes while the releaser is still running, the system will by default not run the releaser in parallel. If the lookup table queue is empty, it then reads a flag from a lookup table to see if a final ping needs to be performed. After performing the ping, if more work needs to be performed it is added to the queue. Otherwise set the final ping flag in the lookup table so the integration becomes idle until a webhook is received.
1 Like