Using Flow Schedule Fast Formula to Schedule Extracts

Steps to create Flow Schedule Formula:

Navigate to Setup and Maintenance > Define Fast Formula
Click Create

Provide a name to Fast Formula and select the type as “Flow Schedule” and fill in other parameters and click Continue.

Flow Schedule Formula to run extracts only on weekdays:

INPUTS ARE SCHEDULED_DATE(DATE)
/* Get day number for Saturday. */
A_SATURDAY = TO_DATE('2012-01-07', 'YYYY-MM-DD')
SATURDAY_NUMBER = TO_CHAR(A_SATURDAY, 'D')
/* Get day number for Sunday. */
A_SUNDAY = TO_DATE('2012-01-01', 'YYYY-MM-DD')
SUNDAY_NUMBER = TO_CHAR(A_SUNDAY, 'D')
/* Starting at scheduled_date, get next_scheduled_day by using the day number.*/
NEXT_SCHEDULED_DATE = ADD_DAYS(SCHEDULED_DATE, 1)
SCHEDULED_DAY_NUMBER = TO_CHAR(NEXT_SCHEDULED_DATE, 'D')
WHILE (SCHEDULED_DAY_NUMBER = SATURDAY_NUMBER OR
SCHEDULED_DAY_NUMBER = SUNDAY_NUMBER) LOOP
(
/* Go to next day. */
NEXT_SCHEDULED_DATE = ADD_DAYS( NEXT_SCHEDULED_DATE, 1)
SCHEDULED_DAY_NUMBER = TO_CHAR(NEXT_SCHEDULED_DATE, 'D')
)
RETURN NEXT_SCHEDULED_DATE

Copy this code, Save and Compile the Formula. Once compiled, you are ready to schedule the extracts using this weekday schedule.

We will look into the explanation of this formula now:

• Inputs for this formula type are SUBMISSION_DATE and SCHEDULED_DATE and Outputs are NEXT_SCHEDULED_DATE. SUBMISSION_DATE is the datetime of the date on which the flow is Submitted. Most of the times, we will be using the SCHEDULED_DATE which stores the start date of the schedule.
• First we are getting the Day Number for Saturday and Sunday.
• Next we are getting the  Day Number of the Day Number of SCHEDULED_DATE and adding two to it if the next schedule falls on saturday or 1 to it if the next schedule falls on sunday (using while loop).
• Final outcome would be monday if the next schedule falls over saturday/sunday.
• In this way we can achieve ad-hoc schedules using Fast Formula.

Flow Schedule formula to execute extract for every 2 hours:

/*************************************************************/
FORMULA NAME: Schedule for every two hours
FORMULA TYPE: Flow Schedule
DESCRIPTION: Formula to return a date time.
Returns NEXT_SCHEDULED_DATE;
Formula Results :
NEXT_SCHEDULED_DATE This is a date time value with yyyy-MM-dd HH:mm:ss format.
***************************************************************/
/* Inputs */
INPUTS ARE SUBMISSION_DATE(DATE), SCHEDULED_DATE(DATE)
/* Calculations */
NEXT_SCHEDULED_DATE =ADD_DAYS(SCHEDULED_DATE,2/24)
/* Returns */
RETURN NEXT_SCHEDULED_DATE
/* End Formula Text */

The ADD_DAYS function is used to add the number of days to the next schedule. In this case we need 2 hours to be added and hence 2/24 hours.. so the next schedule will be 2 hours from previous schedule.

Tip: You can create Flow Schedule Formula to cater to complex scheduling patterns. Ex: Extract should run 2 days prior to pay period end date and 2 days after the new pay period starts. You can create a Valueset and formula to achieve such complex requirements.