Advanced Scheduling beyond CRON Expressions

[vkelly]

I'm looking for suggestions on how to rebuild a recipe. 

Senario:  Client needs us to feed data to their SAP server on the following schedule:  Mon - 11am, Tue - 11am, Wed 6am

I have tried 2 different methods:

1:  Create a schedule trigger using a CRON expression that triggers every Mon, Tue & Wed at 6am and 11am.  I then put a conditional filter on the trigger to only proceed through the recipe if the run time is Mon at 11am, Tue at 11am or Wed at 6am.  While this works to trigger the recipe at the correct cadence, I need to run a SQL query and use the data pill 'Last Job Time' to filter the delta records between the last run and now.  While this should work in theory, the Last Job Time data pill considers each trigger as 'Last Job Time'.  This results in the Monday 11am run only pulling records since 6am Monday vs the previous Wednesday at 6am, same on Tuesday, it only looks back to 6am that day. This results in missed new records.

2. I rewrote the recipe to trigger only on Mondays at 11am then after querying the data back to 125 hours (the Wednesday 6am run the previous week) writing to the SAP server then pausing for 24.  I then built out the recipe further so after the pause/wait it runs the same query but looks back 24 hours (since the Monday run at 11am) pauses for 19 hours and then runs a 3rd query checking for records in the past 19 hours.  While this method works I have to block each of these 3 steps for error handling and ad-hoc runs and recipe updates have become tedious. 

3.  After reading up on a suggestion for a similar but simpler issue posted here, I decided I would create 3 seperate recipes that merely called the main recipe with the query.  This has proven to also be a challenge since I no longer have the data pill 'Last Job Time' to use in my query.

Am I really stuck with option 2 or does anyone have a better suggestion.  This is just one client example - we have many other clients that have similar timing requirements for delta records.  We plan to build out our APIs but at this time we are only able to query the data.

.

[vkelly]

Correction to #2/current method used.  The Monday run looks back 125 hours, not 158.  Not that this is relevant to the question.

[gary1]

Options 3 is really the way to go. Where are you storing the "Last Job Time"? This seems like the only blocker to adopting option 3, and also the easiest problem to fix.

[vkelly]

In the images below, the 2nd image shows I can pull the Last Job Time from the recipe properties.  In the 1st image you will see when I create the recipe as a Function Call, I do not have the data pill available.

[gary1]

That's an easy fix then! Simply don't use that data pill and log your actual last job time elsewhere instead.

You can create a lookup table in Workato and manually enter the last job time to get things started. Update your recipe so it queries the LUT to get the time, and then you can write the new time at the beginning or end of your recipe (your choice, maybe write it after it completes successfully).