DeviantPoint

NOTE: I posted this about an hour ago but for some reason, it got deleted.

One of the features I looked most forward to working with in SharePoint 2010 is the ability to customize a list's add/edit forms in InfoPath. I created a very simple custom list and the ability to edit those forms was as easy as advertised. However, when I created a document library with all the default settings and without adding any columns, I got the following error:

The following is the full error text:

The SharePoint list form can't be customized with InfoPath because fields of an unsupported data type are marked as required, or because fields are corrupted. In SharePoint, try deleting the columns or editing the column properties to remove the required

Is this supposed to work for document libraries or is the ability to edit these forms in InfoPath 2010 limited to lists only? Like I said, I kept all the default settings and didn't add any additional columns to the document library so if this functionality is supposed to work, I would have assumed it would have worked at the very least in this scenario. But if it truly isn't supposed to work with document libraries in the first place, why does the command to edit the library's forms appear in the ribbon at all?

Here's a really simple T-SQL function for getting a list of business dates (Monday - Friday) that fall within a given date range. For example, say I wanted to find the business dates between the dates 10/21/2009 - 10/30/2009. This function will return the following results:

10/21/2009
10/22/2009
10/23/2009
[10/24/2009 is skipped]
[10/25/2009 is skipped]
10/26/2009
10/27/2009
10/28/2009
10/29/2009
10/30/2009

I just thought this function might be helpful for others since I couldn't find anything else out there that someone else had written to accomplish this (easy as it is). There are plenty of T-SQL scripts that return the number of business days between a date range, I just couldn't find one that actually returned the business dates.

Here's the script:

CREATE FUNCTION BusinessDatesInDateRange
(
    @BeginDate DATETIME, @EndDate DATETIME
)

RETURNS @BusinessDates TABLE (BusinessDate DATETIME)
AS    BEGIN 
        DECLARE @CurrentDate DATETIME         
        SET @CurrentDate = @BeginDate        

        WHILE (@CurrentDate <= @EndDate) 
        BEGIN 
            -- 1: Sunday 7: Saturday 
            IF DATEPART(WEEKDAY, @CurrentDate) <> 1 AND DATEPART(WEEKDAY, @CurrentDate) <> 7 
            BEGIN 
                INSERT INTO @BusinessDates(BusinessDate) VALUES (@CurrentDate); 
            END             

            SET @CurrentDate = DATEADD(D, 1, @CurrentDate)             
        END

    RETURN 
    END

GO

Sample call to execute:

select * from dbo.BusinessDatesInDateRange('10/21/2009', '10/30/2009');

I've been meaning to post this for at least a month but I've been so busy with work that I've had no time to sit down and write this until now. But better late than never, I guess.

A little more than a year ago, I developed a browser-enabled InfoPath form that was used on a public-facing MOSS site so that site visitors could submit their photographs to the organization's yearly photo contest. Along with capturing the photo metadata (title, location, date, etc), one of the fields on the form was for the photo itself, captured through the use of the attachment control. The photo contest reviewers wanted to be able to have a quick and easy way to review the photos and the photo metadata so the original thought was to submit the photo to a document library and use property promotion to display the photo along with the metadata. Unfortunately, we quickly found out that the attachment control could not be promoted to a property. So I wrote a web service instead that would accept this form, extract out the attachment, and save the attachment directly into a picture library (and copy the photo metadata as well). That way, the users were able to see the photo and its metadata as soon as they opened the library.

A few months ago, someone again approached me with similar requirements. They were building a form that would have an attachment field that they'd need to extract to put into another library. Well, that was enough right there to make me realize that I needed to build something that would enable users to build these types of solutions themselves. I didn't want to have to keep writing things over and over to support these types of requirements. I also wanted to remove myself out of the process; they were already at 90% of the entire solution since they were building these forms already, they just needed the last 10% of being able to extract the attachment.

So what I ended building instead was a new workflow activity that could be used in SharePoint Designer as a workflow action. Basically, the action can be used as part of a workflow created in SharePoint Designer that will allow the user to add a step in their workflow for extracting out the attachment and saving it somewhere else. When the action is added to the SPD workflow, it looks like the following:

The 'attachment field name' is where the user specifies the name of the attachment control field in the InfoPath form. The link 'the item' is the SharePoint list item this action should work against. This is going to typically be the current item. The 'document library' is where you want the item to be stored to and the 'filename' parameter is the filename to save the attachment as (this can be driven dynamically as well from metadata of the item or some variable). The sentence '(use attachment name instead: Yes)' actually gives you the opportunity to use the actual filename of the file that was attached, rather than having to specify the filename yourself. When a user attaches a document to an InfoPath form, part of what is stored is the actual file's filename. Though that is not available through SharePoint, it can be extracted out by code, which I do inside of the custom action. By specifying 'Yes' for this field, this indicates the action to ignore the filename parameter and use the attachment's filename instead. The workflow designer can also specify whether or not the file should overwrite any existing file in the document library with the same filename. Finally, when the attachment has been extracted and saved, the action returns the item's new id which can be bound to a variable in the SPD workflow.

As an example of how this works, I created a sample New Hire InfoPath Form. The form contains an attachment field for an employee photo. The forms are submitted to my 'New Hire Forms' document library. Here is a sample of the form as it's been filled out:

Now, let's say that as part of this scenario, I want to give others the ability to be able to reuse that employee photo for other purposes (who knows what for, just go along with the example :)). But other people shouldn't be allowed to look at this form because it could potentially hold sensitive information. I need to move this photo out of this form and into another, less-restricted library. So I created a 'Team Photos' Picture Library for this purpose. Now, all I have to do is create a workflow to do the photo extraction. I can create a workflow and attach it to my New Hire Forms document library and configure it to start automatically when a new item is added. Then, I can configure my new workflow as follows:

I have three actions here. The first action is my custom extraction action. I configured it to pull the photo from the 'employeePhoto' field using the current item in the New Hire Forms document library. I save this photo to the Team Photos picture library (and instead of specifying a filename, I'll just use the attachment's filename). I will overwrite the picture if it already exists. Finally, I store the id of the newly added item in the Team Photos library to the workflow variable 'new item id'.

The second action is actually an Update List Item action. I use this action to grab the last name of the employee (from the New Hire Forms item) and save it to the description field of the item I just saved in the picture library. That's why saving the id is important because you can use it to reference to later or make cross references to (which I'll get into in a second). The following is the configuration of this action:

Obviously, I could have created a workflow variable that was a concatenation of the first name, last name, department, etc and used that to fill out the description but this is just for demonstration purposes.

Lastly, the third action is again an Update List Item action. With this action, I update the current item I'm executing this workflow against so that I can add a cross-reference to the photo that I just extracted and stored somewhere else. One of the columns in the New Hire Forms document library is a lookup column called Employee Photo. I configured the lookup column to point to the Team Photos picture library with the referencing being the ID of the picture item. Then, I just configured the action in my workflow as follows:

That's it for the workflow. Finally, when it runs, here's what I get:

In my New Hire Forms library, you can see the workflow has finished executing and there is a link under Employee Photo to the photo that was extracted. And here's the Team Photos picture library with the extracted photo:

 

So, that's pretty much to it. I think from this example, it's easy to see the value of taking some time and really thinking about coming up with custom workflow activities. Custom workflow activities exposed as SharePoinw Designer workflow actions can really enable your users to build their own solutions, possibly in ways you wouldn't have imagined yourself. That's normally how I try to envision solutions. When someone asks me to build something, I ask myself, can I build something else that will allow them to create their own solution now and in the future.

I attached the SharePoint solution file (.wsp) below as well as the VS2008 project files.

In the future, if I see there's enough interest (through comments), I can go into a discussion of the code.

DeviantPointWorkflowActivities.wsp (7.20 kb)   - Once this solution has been deployed, you need to activate the DeviantPoint Custom Workflow Activities web application feature. Repeat - web application feature, not site collection, not site.

DeviantPoint.SharePoint.Workflow.zip (499.49 kb)