One of the joys of working with Duncan

  • Article

I've talked before about our InfoPath form associated with a SharePoint (WSS) library that we use for status reporting (). I use a 'week of' field and custom views for grouping status reports for a specific week. The date needs to be set to the same date for all status reports for that week, otherwise the grouping doesn't work. For example, we use Friday, so the right date for this week's status report is Feb. 13. There's a reminder in the form about setting it to Friday, and it's documented in our process, but invariably there are one or two status reports every week that aren't set to Friday. In this case, I have to either send to nag mails asking the person(s) to change the report, or change it/them myself. Tiresome at best. Obviously I needed to add script validation on the field, but it's pretty low on the list of changes that I want to make to the system, largely because I'd have to dig to figure out the script (remember, I'm not a developer, I'm an “Information Worker“ ;-) and that's probably not the best use of my time at this point.

Last night, though, I decided to ask Duncan and Kent if they'd be willing to help with a validation script for the date field. I fully expected one of them to be willing to help me, since they're both already shown an amazing willingness to help me solve problems in the past. I sent mail to them at 11:24pm, and figured I'd follow up in person in the next couple of days. Much to my surprise, when I came in this morning I found an email from Duncan, sent at 12:52am, providing me with not only the script I needed to include, but also an example InfoPath form showing the use of it! Woo Hoo, Duncan rocks!

(And if you're wondering, Kent also responded that he would write it; Duncan just beat him to it.)

Here's the VB code that Duncan provided (which I added to the OnBeforeChange Event for the datepicker field):

 On Error Resume Next
 eventObj.ReturnStatus = True
 If Not eventObj.IsUndoRedo Then
  If Not IsEmpty(eventObj.NewValue) Then
   If Not Weekday(eventObj.NewValue) = 6 Then
    eventObj.ReturnMessage = "Date selected must be a Friday"
    eventObj.ReturnStatus = False
   End If
  End If
 End If

I've implemented the code in the status report form (more about that in the next post), and it works like a champ! I'm so looking forward to not having to deal with the wrong date in status reports this week, or ever again!