start a workflow at a specific time (effective date/time)

[scottcornthwait]

Hello All,

I'm looking for validation that what i'm planning on doing isn't a big NO NO in terms of SN development. I have a requirement that we have workflows for a custom table that start at a specific time. The problem is, the start time/effective date needs to be able to be modified right up until the point when workflow begins. What has been proposed in the past is that we set a timer in the beginning of the workflow that waits until the time outlined on the record hits. The problem with that is the timer is started as a scheduled job in the background, and once it's set, the only ways to update that time is by having admin access to update the scheduled job, or cancel the workflow and start it again (neither of which sound very maintainable to me when we're dealing with a higher volume table).

So here's my thought,

When our record gets created in the custom table with its effective date/time, we have a business rule to create a record in the sys_trigger table (scheduled job) as a run once job. This job would have a script on it that will trigger off the workflow via script methods. In the custom table record, we'll have a reference field back to the scheduled job record, and if the effective date/time changes, we'll have an easy reference back to update the execution time on the scheduled job via a business rule.

Is this overkill to another way possible? The scope of this is with respect to account administration efforts (creating accounts, updating accounts, disabling account, etc), hence the need for effective dates.

Opinions, suggestions for caution areas when creating sys_trigger records, and recommendations welcome

[Michael Ritchie · Accepted solution]

Its certainly one approach, but I recommend a slightly easier one that doesn't involve managing a bunch of schedule records.

Create a scheduled job that runs every minute looking for records in your custom table that are ready to go and then have it launch the workflow via script for each record that is ready.   Or another option is to have your workflow launch when a State type field gets set to a certain value on your custom table.   So again scheduled job look for records ready to go and then simply change the State field from "Ready" to "Go".

[scottcornthwait]

A bit harder on the server resources as you're running the job every minute looking for items to run, but would definitely be a simpler approach. I like it.

I should have mentioned above that I am planning on having the workflow trigger off based on a status field switching from Scheduled -> Running, so i'm glad i'm on the right track with that approach.

[Michael Ritchie]

True on the resources but as long as you index the date field, it won't make any difference.   There are many examples of this out of the box.

[matteoking4]

Hi,

I am facing the same challenges as you did, could you share the way you went forward implementing this. And could you maybe share some scripts that made this possible? 

Thanks.