Monday, April 25, 2011

Using Nintex Workflow with Recurring Events in a Calendar (Part 1 - Decoding Recurrence)

Some time ago I was tasked with what I thought would be a simple exercise - create tasks based on recurring items in a calendar list (MOSS 2007). My client uses Nintex Workflow and I assumed that I would easily be able to grab the recurrence and do something with it. Wrong. Google was little help as well. I began sussing out the details around how to make this happen and broke my discovery down into a couple of areas:

  • How do I tap into the recurring info of a calendar item
  • How do I interpret and process the recurrence once I have it
  • How do I create tasks (or send emails or do "something") on the appropriate date

Part 1 of this series discusses how to tap into the recurring data of a calendar item, and what all of the different options are.  The recurrence data is stored in the list item and is accessible by querying your event using the "Query List" action.  It returns XML that has the repeat rules, which is somewhat difficult to breakdown as the XML that is returned varies widely across the different options that are set within recurrence.  Once you break it down, there are essentially 3 things to look at in the XML to process the recurrence:

1.  What kind of recurrence we have (daily, weekly, monthly, yearly)
2.  The varying rules around the recurrence (on a specific date, or a specific day of the week, etc)
3.  When the recurrence ends (never, after x occurrences, on a specific date)

Recurrence XML Examples

  • Daily
    • Recurring every day, with no end date:
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><daily dayFrequency="1" /></repeat>
        <repeatForever>FALSE</repeatForever>
        </rule></recurrence>
    • Recurring every weekday, end after 10 occurrences:
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><daily weekday="TRUE" /></repeat>
        <repeatInstances>10</repeatInstances>
        </rule></recurrence>
    • Recurring every 3 days, specific end date:
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><daily dayFrequency="3" /></repeat>
        <windowEnd>2012-04-26T20:00:00Z</windowEnd>
        </rule></recurrence>
  • Weekly
    • Recurring every 1 week on a specific day, end after 10 occurrences:
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><weekly tu="TRUE" weekFrequency="1" /></repeat>
        <repeatInstances>10</repeatInstances>
        </rule></recurrence>
    • Recurring every 1 week on a specific day, specific end date: 
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><weekly tu="TRUE" weekFrequency="1" /></repeat>
        <windowEnd>2012-04-26T20:00:00Z</windowEnd>
        </rule></recurrence>
    • Recurring every 2 weeks on a specific day, no end date:
      •  <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><weekly tu="TRUE" weekFrequency="2" /></repeat>
        <repeatForever>FALSE</repeatForever>
        </rule></recurrence>
  • Monthly
    • Recurring on a specific date of the month, no end date
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><monthly monthFrequency="1" day="26" /></repeat>
        <repeatForever>FALSE</repeatForever>
        </rule></recurrence>
    • Recurring on a specific day every 1 month, end after 10 occurrences
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><monthlyByDay tu="TRUE" weekdayOfMonth="first" monthFrequency="1" /></repeat>
        <repeatInstances>10</repeatInstances>
        </rule></recurrence>
    • Recurring on a specific date every 3 months, end after 10 occurrences
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><monthly monthFrequency="3" day="26" /></repeat>
        <repeatInstances>10</repeatInstances>
        </rule></recurrence>
    • Recurring on a specific day every 3 months, end on a specific date
      •  <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><monthlyByDay tu="TRUE" weekdayOfMonth="first" monthFrequency="3" /></repeat>
        <windowEnd>2012-04-26T20:00:00Z</windowEnd>
        </rule></recurrence>
  • Yearly
    • Recurring annually on a specific date, no end date
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><yearly yearFrequency="1" month="4" day="26" /></repeat>
        <repeatForever>FALSE</repeatForever>
        </rule></recurrence>
    • Recurring annually on a specific date, ends on a specific date
      • <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><yearly yearFrequency="1" month="4" day="26" /></repeat>
        <windowEnd>2012-04-26T20:00:00Z</windowEnd>
        </rule></recurrence>
    • Recurring annually on a specific day, ends after 10 occurrences
      •  <recurrence><rule>
        <firstDayOfWeek>su</firstDayOfWeek>
        <repeat><yearlyByDay yearFrequency="1" tu="TRUE" weekdayOfMonth="first" month="4" /></repeat>
        <repeatInstances>10</repeatInstances>
        </rule></recurrence>

 ...as you can see - the different options can get overwhelming, however several patterns emerge:

  • First day of the week is always included and is based on settings in the SharePoint environment
  • "Types" of recurrence are distinguishable as:
    • daily
    • weekly
    • monthly
    • monthlybyday
    • yearly
    • yearlybyday
  • When the recurrence ends are encapsulated such that we have 3 options:
    • no end date (which btw...this seems a little backward to me): <repeatForever>FALSE</repeatForever>
    • ends after x occurrences: <repeatInstances>10</repeatInstances>
    • ends on a specific date: <windowEnd>2012-04-26T20:00:00Z</windowEnd>

 

 

Workflow

Alright - so how does this play into a workflow?  Well, we are just scratching the surface.  Essentially, all we can do at this point is gather the recurrence data from the item, isolate the repeat rule, and see what type of recurrence we have.  Once we know what kind of recurrence we are dealing with (daily, weekly, monthly, yearly), we will dive deeper into each segment and discuss how to process the different options within each type and do something with that information.

Here's what the workflow looks like at this point (not visible - branching on the types of recurrence); followed by the settings of the more notable components:
NOTE - the complete workflow is attached and is compatible with Nintex Workflow 2007 and 2010.  Placeholders are in place for "action" items (ie creating tasks or sending emails).  Note that Nintex Worfklow 2010 offers more capabilities to package this process more efficiently, however that is not represented in the attached.

 

 

 

Query List Action - Gather Recurrence Data

The query list action is what is used to query our event and return the recurrence XML.

  • Insert a query list action into your workflow
  • Switch over to the CAML editor to customize the query
    • Paste the following snippet into the CAML Query section - essentially you are telling Nintex to query the calendar list you are in, and return the recurrence data (in expanded format) for the item that you are working on.  Be mindful to make the following updates:
      • the List ID should be representative of the list GUID that you are currently working in (ie - the GUID of the calendar list)
      • Field Ref ID refers to the ID of the current item...this can be a workflow variable, or an item property
      • <Query>
            <Lists>
                <List ID="{AE7FBE30-4201-4C51-AEDF-8E15B4F21C3C}" />
            </Lists>
            <ViewFields>
                <FieldRef Name="RecurrenceData" />
            </ViewFields>
            <Where>
                <And>
                    <Eq>
                        <FieldRef Name="ID" />
                        <Value Type="Counter">{ItemProperty:ID}</Value>
                    </Eq>
                    <DateRangesOverlap>
                        <FieldRef Name="EventDate" />
                        <FieldRef Name="EndDate" />
                        <FieldRef Name="RecurrenceID" />
                        <Value Type="DateTime">
                            <Month />
                        </Value>
                    </DateRangesOverlap>
                </And>
            </Where>
            <queryOptions>
                <QueryOptions>
                    <ExpandRecurrence>TRUE</ExpandRecurrence>
                </QueryOptions>
            </queryOptions>
        </Query>
    • Store the results in a workflow variable of type "Collection", in this case "Output Collection"

 

For Each Action - Isolate the XML returned from the Query

Use a For Each action to isolate the XML response into a variable (note that because we used the ID field to query our list, we should only have 1 response)

  • Insert a For Each Action
  • Set the target collection to the collection you used in the previous step to store the XML response
  • Set the store result in to a workflow variable of type "text", in this case "Output"

 

Query XML Action - Isolate the Repeat Rule

Use the Query XML action to parse the recurrence XML to get the repeat rule

  • Set XML Source to XML
  • Insert a reference to the XML, in this case, the "Output" workflow variable used above in the For Each
  • Process using XPath
    • Parse the following path: /recurrence/rule/repeat
  • Return the result as Inner XML
  • Store results in a workflow variable of type text (in this case, "Repeat"

 

Regular Expression - Get Recurrence Type (daily, monthly, weekly, etc)

Use a regular expression action to extract the key phrase for the recurrence type.

  • Insert a RegEx action
  • Set the Pattern to: monthlybyday|yearlybyday|daily|weekly|monthly|yearly
  • check ignore case
  • Select "Extract"
  • Set the Input text to the "Repeat" rule workflow variable gathered above
  • Store result in a collection field, in this case "Output Collection"

 

 Collection Operation - Set the Recurrence Type

Use a collection operation to isolate the recurrence type extracted in the above step.

  • Insert a Collection Operation action
  • Set the target collection to "OutputCollection" as defined in the previous step
  • Select the "Get" option
  • Set the Index field to a numeric workflow variable
  • Store result in a single line of text field, in this case "RecurrenceType"

 

Query XML - Isolate Repeat Instances

Query the XML repeat rule for the RepeatInstances tag.  Once the RepeatInstance is isolated, follow this action with a condition to test if the variable is empty.  If it is not empty, convert the string to a number and store it in a variable; if it is not empty then set the variable to 0.  We will use this variable as a test later in the process.

  • Insert a Query XML Action
  • Set the XML Source to XML
  • Insert the "Ouput" workflow variable
  • Process using XPath
    • Parse the following path: /recurrence/rule/repeatInstances
  • Return results as "Text"
  • Store Result in "Repeat Instances" a workflow variable of type text

 

Query XML - Isolate Window End

Query the XML repeat rule for the Window End tag.  Once the Window End is isolated, follow this action with a condition to test if the variable is empty.  If it is not empty, convert the string to a date and store it in a variable; if it is not empty then set the variable to 1/1/1900.  We will use this variable as a test later in the process.

  • Insert a Query XML Action
  • Set the XML Source to XML
  • Insert the "Ouput" workflow variable
  • Process using XPath
    • Parse the following path: /recurrence/rule/windowEnd
  • Return results as "Text"
  • Store Result in "WindowEnd" a workflow variable of type date

 

Switch - Define the paths to take based on the Recurrence Type

Use the Switch action to identify the variety of paths to take based on the recurrence type (daily, weekly, monthly, etc).

  •  Insert a Switch action
  • Evaluate the "RecurrenceType" variable isolated in the above steps
  • Enter possible values (ie - legs of the switch) as: daily, weekly, monthly, yearly, monthlybyday, yearlybyday

 

While I realize this is a lot to digest, I felt it was important to document all of the minutia in these steps as it was one of the most difficult to sort out and set up.  Once it was done it seemed simple, but there was a lot of trial and error that went into it.  Next up on the docket will be diving into of these types of recurrence, decoding the recurrence options, and doing something with them.