How To: Add the field to list


How To: Extract values from the text value


To extract values you can create a calculated field and use these fields in workflows and CAML queries. The last three values of the text value are fixed length. The other values are not guaranteed to contain any value or to be of any fixed length.
  • The "Recurrence" column in the screenshot represents how the field is rendered in list views and display views.
  • The "Next Occurrence" column is a calculated column: =LEFT(RIGHT(Recurrence,38),22).
  • The "Last Occurrence" column is a calculated column: =LEFT(RIGHT(Recurrence,62),22)
  • Not shown is the "Next Occurrence" column formatted as "yyyyMMddHHmmss" which can be retrieved: =RIGHT(Recurrence,14).

I did not realize that SharePoint would not properly recognize the dates for the calculated fields for the "Next..." and "Last..." segments using the formulas above. The following formula will correctly display the calculated value as a datetime:
  • Next Occurrence: =DATEVALUE(LEFT(RIGHT(Recurrence,38),10))+TEXT((LEFT(RIGHT(Recurrence,27),11)),"hh:mm")
  • Last Occurrence: =DATEVALUE(LEFT(RIGHT(Recurrence,62),10))+TEXT((LEFT(RIGHT(Recurrence,51),11)),"hh:mm")

How To: Update the field's "Last Occurrence" value

Whenever the "Last Occurrence" segment of the text is changed, and the item is subsequently updated, the field will auot calculated the "Next Occurrence" value.

In order to update the "Last Occurrence" segment you need to convert the text to an array. I believe the easiest way to convert the text to an array is to use the SPFieldMultiColumnValue.

Dim mcv As New SPFieldMultiColumnValue(item("Recurrence").ToString)
mcv(8) = DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt")
item("Recurrence") = mcv.ToString

How To: Get items that are are "due" to occurr

This field came as a result of our need to run workflows on a schedule. It evolved into a Central Administration Timer project which runs every 15 minutes and pulls items from a list stored on the site collection's root web page. In order to know which items are due to occur, it executes a CAML query and then iterates those items.

In the following example my site is located at http://dev. On that site I have a list titled "Settings List" which stores items containing a recurrence field titled "Recurrence" and a calculated filed called "DateFilter". The calculated field's formula is =RIGHT(Recurrence,14) and is set to a numeric field. I also created a Yes/No field titled "Test Mode". This field allows me to test the code and make sure that it runs on schedule. The "Test Mode" field is not required.

        Using site As SPSite = New SPSite("http://dev")
            Using Web As SPWeb = site.OpenWeb
                Dim settingsList As SPList = Web.Lists.TryGetList("Settings List")
                Dim query As New SPQuery
                query.Query = String.Format("<Where><Or><Leq><FieldRef Name='DateFilter' /><Value Type='Calculated' >{0}</Value></Leq><Eq><FieldRef Name='Test_x0020_Mode' /><Value Type='Boolean'>1</Value></Eq></Or></Where>", DateTime.Now.ToString("yyyyMMddHHmmss"))
                Dim listItemCollection As SPListItemCollection = settingsList.GetItems(query)
                For Each item As SPListItem In listItemCollection
                    'do work

                    'Update item's last occurrence segment
                    Dim mcv As New SPFieldMultiColumnValue(item("Recurrence").ToString)
                    mcv(8) = DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt")
                    item("Recurrence") = mcv.ToString
            End Using
        End Using

How To: Change how the date is represented

Does the display work if you change the calculated field to a Date and Time value? If not, then I do not currently know. Sorry.

How To: Understanding how the next occurrence is calculated

The recurrence field, when in edit mode, allows you to modify the "Last Occurrence" date and time. The reason for this is so that you can set it to a past date time and have the "Next Occurrence" generate a time to occur.

An important understanding for how the field calculates "Next Occurence" is that it jumps to the next frequency. For instance:
  • If you select Daily at 5:00 PM, and set the "Last..." to 8/21/2012 4:00 PM, the field will see that it ran on the day of 8/21 and set "Next..." to 8/22/2012 5:00 PM.
  • If you select Weekly on Friday at 5:00 PM, and set the "Last..." to 8/19/2012 8:30 AM, the "Next..." will generate as 8/31/2012 5:00 PM, skipping the upcoming Friday because it already ran in that week.

It's also worth mentioning that the "Next..." value does not update until the item is saved. So do not look for the "Next..." to update while in edit mode.

Last edited Aug 27, 2012 at 4:37 AM by c_manboy, version 9


jeremyfauth Feb 20, 2013 at 1:30 PM 
Very nice work!