For anybody that has looked at my blog before knows that I've been working with InfoPath 2007, InfoPath Forms Services and SharePoint 2007 Enterprise and I've been sharing some of my experiences from my projects.  In a recent post I talked about using InfoPath Forms Services and using the Content Type that it creates for Form Libraries and today I would like to continue down that road and talk about SharePoint Site Columns.  The introduction of Content Types and Site Columns in the latest version of SharePoint has brought to the table another thing that needs to be understood when building an Information Architecture.  When you plan to deploy a large amount of forms for your organization it would be ideal to reuse existing columns as much as possible to help keep things more manageable.  What this post will explain is how to use existing SharePoint Site Columns from existing forms for fields that you're promoting from new forms to a Form Library.  Content Types and Site Columns are located on Site Settings page of a SharePoint site and can be found under the Galleries column.

Each Site Column is group by different categories and the one we are interested in is the "Microsoft Office InfoPath" categories.  Everything that is included in this group is currently being used by published InfoPath forms.  If you look at Site Columns and scroll down or change the Show Group drop down to the "Microsoft Office InfoPath" category you'll notice all the promoted columns from your forms.  In the example below you will notice that there is about 12 columns and most of them are unique but some are duplicates.  The reason for the duplicates is because 2 different forms promoted the same field names but didn't reuse the existing column.

Let's take this a little further now.  Let's say you don't have 2 forms but 100 forms and half those forms promote the Divisional Controller field.  Instead of having 2 duplicate fields all the sudden you have 50 duplicate fields.  From a supportability standpoint this would be a nightmare to manage but you might be thinking that still wouldn't be a big deal because nobody comes to this page anyway and it doesn't effect anything in the libraries.  Well this is not the only place you would run into this problem.  You would also have issues in Form Libraries that have multiple forms specified.  The forms would have duplicate columns and these columns would show multiple times even in the form libraries.  The example below shows 3 forms and 2 of the forms reuses columns but the third form doesn't.  Could you imagine what this would look like if you had 4 or 5 different forms specified for this library and didn't reuse existing columns.

Enough of me telling you what could happen if you don't reuse existing columns and let me show you how to reuse columns from InfoPath designer.  In InfoPath there is two ways to promote fields as columns.  One way to promote a field is by using Property Promotion option in Form Options (Tools->Form Options->Property Promotion).

The Property Promotion allows you to specify what fields you want to Add, Remove or Modify.  Whenever you add a new field you get the below popup that lets you choose what field to promote but it doesn't give use the ability to choose from existing columns from a SharePoint site.  But if we look at this popup a little closer it gives you a secret message at the bottom where it could be done from. 

It tells us that other options are available on "Select a Field or Group" screen when publishing the form.  These extra options are what give you the ability to choose existing Site Columns from the Web or Site Collection that you are publishing to.  When you are publishing to InfoPath Forms Services it's really important to add the URL where the form will be activated to even though you are publishing the template initially to the file system.  It's important because this where the list of Site Columns comes from.  If fields have already been promoted then highlight the field you want to change and click the Modify button.  The "Select a Field or Group" popup will appear and now you'll have the "Site column group" and "Column name" fields. 

By default the "Site column group" field will have "(None: Create new column in this library)" and if you click the drop down then it will list all the existing groups.  If you scroll down you'll see "Microsoft Office InfoPath" and when chosen the "Column name" field changes to a dropdown list box and lists all the existing columns that you can choose from.  If you choose one of the existing columns then the form will use it instead of creating a new one when the form is activated.  InfoPath will remember the column properties that you set and it won't be necessary to do it again.  If the column doesn't exist then use the original setting and the column will be created but when you republish the form make sure the settings are updated to point to the existing column.  If not another column maybe created and you'll end up having duplicate fields.

Once the columns are created SharePoint gives the flexibility to create a new group in Site Columns and change the column grouping.  This has been done with several clients in the past and groupings are usually done by line of business or something else that is logical for that company.  Using Content Types and Site Columns has really helped my clients streamline and organize the forms in SharePoint and keep the maintenance down to a manageable level.

As always if somebody has anything to add please post a comment because I'm always looking for opinions.

Cheers

This week has been a pretty interesting week for me so far because I'm training a new guy on InfoPath 2007 and InfoPath Forms Services and we are getting new errors and I wanted to share one of them.  "There has been an error while loading the form" was an error that we got today when launching one of his forms.

After doing a little investigation we discovered it was a old form that he deployed and activated to a Site Collection through InfoPath Forms Services but then decided to remove it from InfoPath Forms Services before deactivating it from the Site Collection.  I was pretty amazed that he was able to successfully remove the form before deactivating all instances.  When I looked for the deployment files in the Features folder they weren't there so it appears it was removed successfully.  To resolve the problem we delete the form entry from the Form Template library on the main site and deleted it from the Site Content Type gallery.

Cheers

Today started out pretty eventful because I got a couple emails from an existing client that was getting really strange errors from there SharePoint 2007 environment.  They were telling me that they were getting the following errors whenever they were trying to add new data to their SharePoint sites. 

Error - Unable to complete this operation.  Please contact your administrator ......

Error - Exception from HRESULT: 0x80040E14 ... ...

The errors that were sent to me did not really tell me anything but when they told me that they were unable to add, change or delete anything that was when I knew it had to deal with SQL being out of space or something like that.  After a little investigation I confirmed that the data drive was completely empty and the reason was because the SQL maintenance jobs.  The old SQL backups were not being deleted and after a period of time they ate up all the hard drive space (276 gigs).

Over the last several years I've seen this problem several times and most of the time it's because of miscommunication with the DBA group or just configuration of the SQL maintenance job. When I'm working with a customer we always sit down and create a database backup strategy for the SharePoint environment if they want or need help with it.  In my opinion this step is just as important as anything else.

Cheers

When using InfoPath forms as Content Types you're bound to run into the error "Content Type is still in use", when trying to remove an existing InfoPath as a Content Type.

From my experience you will not be able to remove any InfoPath form Content Type from a Form Library that has submitted InfoPath forms based off that Content Type.  If the library is using multiple InfoPath form Content Types you can go into the Form Library Settings page and update a property for it not to show.  From the settings page locate the "Content Types" section and then click the "Change new button order and default content type" link.

From the "Change New Button Order and Default Content Type" page uncheck the "Visible" check box for he form you no longer want appearing on the Form Library "New" button.

If this InfoPath form is the only Content Type for the Form Library then you can go and edit the Form Library security and not allow any new forms to be added.  The Form Library security link "Permissions for this form library" is located on the Form Library Settings page under the "Permissions and Management" section.

Earlier this year, I also discovered that you can remove the "New" button from the Form Library toolbar by blanking out the library form "Template URL". This property is located on the "Advance settings" page and is only available when "Allow management of content types?" property is set to "No".  I'm not sure why you're allowed to do this but I thought I would share it because it was something interesting.

Cheers

The South Carolina Code Camp is coming up on Saturday, October 13^th in Charleston and I'm happy to say that I will be doing a couple of presentations at code camp.  The presentations are titled "Pre-populating Active Directory User Information InfoPath 2007 and C# and create a workflow with SharePoint Designer for routing" and "Retrieving and submitting data with InfoPath 2007 and C#".  Both of these presentations will be based off work that I did for recent projects.  Information on attending the code camp can be found at http://www.sccodecamp.com/

I'm sort of nervous about speaking at this code camp because it will be my first public speaking engagement.  I've done a lot of informal class room type of training in the past and I'm hoping it will be a lot like that but I'm not 100% sure.  If anybody has any tips or examples on how to format Power Points for code camp presentations or anything else, I would greatly appreciate the advice or examples.

I'm really excited about this opportunity, attending this code camp and meeting some of the area InfoPath and SharePoint colleagues.  Hope to see you there.

Cheers

Earlier this year I mentioned that I wrote a chapter for a soon to be released book and I'm happy to say it was recently released.  This book is comprised of 11 different authors and covers a large range of topics on SharePoint 2007.

http://www.oreilly.com/catalog/9780596529581/

This is really exciting for me because it's my first book to be officially named on and I'm just happy it's finally published.  I would also like to thank everyone that helped me out on my one little chapter.

Cheers

After doing the date calculations, it got me thinking on how some of the other formatting would work and below are some of my random thoughts.  Its not much but just wanted to share it because it might help somebody starting out with SharePoint date calculations.

The value that I used for "Start Time" was 09/17/2007 and the blod date is the output and the reset is the calculation.

Changing the Month 

September 2007  =TEXT([Start Time],"mmmm")&" "&TEXT([Start Time],"yyyy")

Sep 2007  =TEXT([Start Time],"mmm")&" "&TEXT([Start Time],"yyyy")

09 2007  =TEXT([Start Time],"mm")&" "&TEXT([Start Time],"yyyy")

9 2007  =TEXT([Start Time],"m")&" "&TEXT([Start Time],"yyyy")

Changing the Month and Year 

September 07  =TEXT([Start Time],"mmmm")&" "&TEXT([Start Time],"yy")

Sep 07  =TEXT([Start Time],"mmm")&" "&TEXT([Start Time],"yy")

09 07  =TEXT([Start Time],"mm")&" "&TEXT([Start Time],"yy")

9 07  =TEXT([Start Time],"m")&" "&TEXT([Start Time],"yy")

Changing the Day

September Monday 2007  =TEXT([Start Time],"mmmm")&" "&TEXT([Start Time],"dddd")&" "&TEXT([Start Time],"yyyy")

September Mon 2007  =TEXT([Start Time],"mmmm")&" "&TEXT([Start Time],"ddd")&" "&TEXT([Start Time],"yyyy")

September 17 2007  =TEXT([Start Time],"mmmm")&" "&TEXT([Start Time],"dd")&" "&TEXT([Start Time],"yyyy")

Date Format 

Monday September 17^th, 2007   =TEXT([Start Time],"dddd")&" "&TEXT([Start Time],"mmmm")&" "&TEXT([Start Time],"d")&"th, "&TEXT([Start Time],"yyyy")

Quarter Calculation 

1^st Quarter 2007, 2^nd Quarter 2007, 3^rd Quarter 2007 or 4^th Quarter 2007

=IF(AND(MONTH([Start Time])>=1,MONTH([Start Time])<=3),"1st Quarter"&" "&YEAR([Start Time]),IF(AND(MONTH([Start Time])>3,MONTH([Start Time])<=6),"2nd Quarter"&" "&YEAR([Start Time]),IF(AND(MONTH([Start Time])>6,MONTH([Start Time])<=9),"3rd Quarter"&" "&YEAR([Start Time]),IF(AND(MONTH([Start Time])>9,MONTH([Start Time])<=12),"4th Quarter"&" "&YEAR([Start Time])))))

Over the last few weeks I had several different people ask me what were the differences between SharePoint 2007 Standard and SharePoint 2007 Enterprise and unfortunately I didn't know.  All the clients that I've had in the past all wanted SharePoint 2007 Enterprise because they wanted either Excel Services or BDC so it was never a problem.  I got tired not knowing the answer, so I found this nice document on the Microsoft Office site that explains everything.

 http://office.microsoft.com/en-us/sharepointserver/HA101978031033.aspx

The Excel document compares the Windows SharePoint Services 3.0, SharePoint Portal Server 2003, Office SharePoint Server 2007 for Search, Office Forms Server 2007, Office SharePoint Server 2007 Standard CAL and Office SharePoint Server 2007 Enterprise CAL or for Internet Site products in the categories of Collaboration, Portal, Search, Content Management, Business Process and Forms, Business Intelligence, Management and Platform.

Recently I was asked to create new Monthly and Quarterly DVWPs for the "Time Card Management" template.  One of the tasks was to create new columns for the Month and Quarter for DVWPs to group the information by. The Year value was tacked on the end to allow for separate year grouping. Below are the formulas for the columns.

Month-Year

=MONTH([Start Time])&"-"&YEAR([Start Time])

Quarter-Year

=IF(AND(MONTH([Start Time])>=1,MONTH([Start Time])<=3),"1"&" "&YEAR([Start Time]),IF(AND(MONTH([Start Time])>3,MONTH([Start Time])<=6),"2"&" "&YEAR([Start Time]),IF(AND(MONTH([Start Time])>6,MONTH([Start Time])<=9),"3"&" "&YEAR([Start Time]),IF(AND(MONTH([Start Time])>9,MONTH([Start Time])<=12),"4"&" "&YEAR([Start Time])))))

The calculation for the Quarter column was a little tricky because I wanted to determine what Quarter the month was in but I was able to accomplish it by using the SharePoint help and the below blog entry from Mark Kruger.

http://www.sharepointblogs.com/mkruger/archive/2007/06/26/howto-using-sharepoint-calculated-columns-to-display-a-list-item-as-quot-x-quot-days-old.aspx

The SharePoint help helped with the fundamentals and Mark Kruger blog entry helped with the syntax for the Quarter calculation.  Since Mark's entry helped me figure out my problem I figured I would post my calculations hoping it would do the same for someone else.

Cheers