Michael Lotter's little view of the big world of SharePoint and InfoPath

Next week I'll be heading back up to Newark, NJ area to continue my InfoPath 2007/InfoPath Forms Services project and since the NYC SharePoint User Group is meeting next week with my good friend Jason Medero speaking on "Information Rights Management & MOSS 2007" I decided to attend.  This should be a really good user group meeting and I'm hoping to meet some new SharePoint/InfoPath colleagues.  This will be my second time going into the city and I'm hoping I won't get lost this time and make it to the meeting on time.  All the information about the user group meeting is listed below.

NYC SharePoint User Group
10/3/2007  5:30 PM – Microsoft Office – 1290 6^th Ave. (51^st/52^nd)

NEXT NYC SHAREPOINT USER GROUP: Wednesday, October 3

Jason Medero, Microsoft MVP will present on "Information Rights Management & MOSS 2007"

MOSS 2007 is known for its Enterprise Content Management capabilities, but what about when the content leaves the MOSS 2007 environment.  How is the content protected once it leaves the MOSS 2007 environment?  This is where Information Rights Management (IRM) and its tight integration with MOSS 2007 becomes very valuable.  IRM's integration with MOSS 2007 enables administrators to wrap a digital envelope around their digital content.  Once the content is protected inside MOSS 2007 consumers of this content may have specific rights to the content in regards to printing, forwarding or copying this sensitive content.  These restrictions are attached to this content no matter where the content lives or is transported.  This session will cover what IRM is and how it is used in conjunction with MOSS 2007 to provide protection for your sensitive information that is living within your MOSS 2007 environment.

The meeting will start at 5:30 PM with an opportunity to network with your colleagues in other companies

and free food compliments of CorasWorks.

For immediate registration: http://cts.vresp.com/c/?CorasWorks/60fca31a06/d10eefe32c/d0cf740dc8/id=121070

For additional information, directions & other details, visit the NYC SPUG website: http://cts.vresp.com/c/?CorasWorks/60fca31a06/d10eefe32c/b9b69b1445

Each month follows the format below.  Here's the agenda for this month:

**Building Security Requirements: You can expect to have some difficulty & delay getting up to the meeting room if you arrive late. We recommend bringing a copy of your meeting registration confirmation notice with you.

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.

/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

When developing InfoPath forms a common scenario for me is to use a SharePoint List to store dropdown list data.  Most clients like using SharePoint Lists because it gives them the flexibility to update the dropdown list data anytime they want and it doesn't require IT or the form to be republished.  Since I work remotely it is very common for a client to setup a VPN account for me so I can connect to their network and use the SharePoint Lists and other database resources for dropdown list data.

Just recently I was working with the company's new graphic designer helping her to setup a VPN connection for one of our existing clients and then walking through the steps of setting up an InfoPath Data Connection to a SharePoint List.  Over all the process went reasonably well and once the data connection was setup we created a dropdown list control and changed the properties to use the data connection as its data source.  We went to preview the form to make sure everything was working and to my surprise we got the following errors.

The error surprised me because we were able to create the data connection without a problem.  I first thought it was a VPN issue but it wasn't and after sleeping on it for a night the next morning I figured out it was a missed InfoPath 2007 setting.  The setting that needed to be filled in was the Domain field on the Preview category page under Form Options.  If you click Tools->Form Options->Preview you'll get the below screen.

After we filled in the Domain field with the SharePoint site url all the errors went away when previewing the form and the dropdown list displayed the data from SharePoint List as expected.  This was sort of a strange one for me because this was the first time I've had the problem but I'm just glad it was an easy fix.

Cheers

Last week while waiting for my plane to takeoff from Newark airport, I was thinking I was going to start running out of new things to post but I'm finding out that won't be true anytime soon because I keep running into new little things all the time.  Currently most of my posts aren't super technical but more general things but that's only because that's the phase the current project is in. 

Enough of me jabbering and onto what I wanted to post about for this time, while over the past month designing these forms one thing that I've noticed is the font size is not always correctly being set through the InfoPath designer.  The buttons are probably the most common control that I have the problem with and I'm not 100 percent sure why but it's just something I've noticed.  Usually once the form has been graphically designed I do a "ctrl + A" key combination to select everything in the view and then change the font type and size and then go through and change all headings to a bigger font size and I'm done.  Well I've found out this approach isn't full proof and even if the font size in the designer says it's a certain size that's not always true.  Once I recognize it's not the correct size I change it to a different font size and then back to the original font size and then it sets correctly. 

That's all I really wanted to say and I know that's not much but it's something I've had to keep an eye on with all the forms that we are building and it's a pain in my side. Some of you might not think it's a big deal but there is nothing worse than showing a new form to a client and then the client pointing out not all font sizes aren't the same.

Cheers

One of the downsides of my current SharePoint consultanting career is the excessive travel that I do.  I usually travel 80 to 90 percent per year and majority of it is by plane.  Until I switched companies in June most of my travel out of Raleigh was either South or South West and little to the North or North East. Since I switched I pretty much fly North to New York or New Jersey and my current client is in Newark area.  With that being said I've been introduced to the Newark Liberty airport and I must say this has to be the worst airport that I've been to in the last 3 years.  I've flown at least 6 times out of the airport to Raleigh and I have never had a flight leave on time.  Actually I've never had a flight leave an hour half from the actual departure time.  To make things worse the delay is usually not in the airport but on the plan. As I write and post this entry I'm sitting on the runway waiting for my current delay to end and I thought Atlanta was bad.  Sad part is I still probably have 4 to 6 more weeks of this.  Well enough of me ranting about this inconvenience, I just hope no one else is dealing with this and everyone has a great weekend.

Cheers

Since I've been talking about template parts quite a bit of late I wanted to show some examples of the most common ones that we created.  We are not talking about anything fancy but common ones that are used a lot.  With each example I'll give a little explanation on other things that have been done to the template parts that aren't visible from the pictures. 

All the blue examples were built by me and all the nice looking ones were built by our new designer.

As you can tell we took the heading piece from InfoPath layouts a little further and built something little more specific for our situation.  All fields in the template part are automatically pre-populated when a form is opened.  The date field is populated with the "Today" function but the other 3 fields are populated from a secondary data source.  The secondary data source is a web service that queries AD to retrieve the current user display name and company values.  The web service that's used has a parameter that wants current user login name and for this InfoPath "UserName" function is used.  The "Location" dropdown list values are retrieved from a SharePoint List but the active value is populated from the web service.

Things that were done part of the template part were Arial 12 formatting for all text and controls, specifying the data connections for the web service and SharePoint List and the binding of the dropdown list.  The actual queries and setting of values are done in the startup rules of the actual form.  Just the 3 tasks that this Form Template part does saves use a tremendous amount of time when you are talking about hundreds of forms.

When we started to look at the forms they wanted to convert we noticed that most of them had address blocks.  One of their requirements for the address blocks was having the capability of specifying and updating any values for states or provinces and countries or regions.  One nice thing we didn't have to worry about was data validation for any of the fields.  We ultimately decided to use SharePoint Lists for both the State/Province and Country/Region dropdown list fields.

Things that were done part of the template part were Arial 12 formatting for all text and controls, data binding of dropdown lists and specifying the data connections for State/Province and Country/Region.  Both of these dropdown lists values are pulled from SharePoint Lists because the SharePoint Lists gives the customer the flexibility to change the values whenever they want.  The actual queries happen in the startup rules of the actual form.

A couple of things that we didn't do that could have been done in this template part would have been data validation for the Zip/Postal Code and Phone fields but it wasn't necessary at this point.  If it does come necessary then all we have to do is update this template part and then import it back into the InfoPath Designer and re-open each form and then update the control.

The last example that I have to share at this moment is an approval template part.  We use this for allowing people to approve the content of the form instead of using Workflow Tasks.  It was decided to do it this way because then all the information would reside in the forms and not in different locations. 

Things that were done part of the template part were Arial 12 formatting for all text and controls, conditional formatting for the checkboxes, text wrapping settings for the multi-line textbox, conditional formatting and rules for the approve and reject button, and data connection that is used for submitting the form. 

You might be asking why we have an approval form template part and the reason is because the customer didn't want to use workflow tasks and wanted all the information stored in the XML.  The workflows that we use are very simple and usually only send emails to people to approve or reject forms and to the individual who submitted the form to advise them if the form was approved or rejected.

Well those are probably are most common template parts at this point and like I said before nothing fancy but just very common things that are used a lot.

Cheers

Recently had a comment asking for online resources for InfoPath 2007 Template Parts and only posted 2 links because they were easiest to find.  Since then I went looking a little harder and found the following and in my opinion the "Designing and using template parts" is probably the best one of the lot.  It links to about 6 or 7 online MS articles that cover about everything on template parts.

Cheers

Designing and using template parts
http://office.microsoft.com/en-us/infopath/CH101638151033.aspx

Lab 10: Creating and Inserting InfoPath 2007 Template Parts
http://msdn2.microsoft.com/en-us/library/bb267331.aspx

How to: Share InfoPath 2007 Template Parts and ActiveX Controls
http://msdn2.microsoft.com/en-us/library/bb409614.aspx

InfoPath Team Blog – Template Parts 101
http://blogs.msdn.com/infopath/archive/2007/03/14/template-parts-101.aspx

The SharePoint Factory – InfoPath 2007: Form Template Parts
http://spsfactory.blogspot.com/2006/07/infopath-2007-form-template-parts.html

InfoPath 2007: Creating a Template Part
http://blog.funknstyle.com/?p=503

When I first started working with my current client their first form was 6 pages (views) and the client wanted the navigation to be semi smart and very easy to use.  When I say semi smart they wanted the navigation to disabled if a user shouldn't have access to a view.  They immediately said the form needed to be browser based and they didn't want the out of box navigation but something that could be easily updated without the assistance of IT.  We all know that User Roles is not support in browser based forms so a new way had to be created.  So I came up with the ideal of doing breadcrumb navigation at the top of the header of each page and creating an AD group for each item in the breadcrumb.  If the user opening the form was part of that AD group then that breadcrumb button was active.  The details if a user was part of an AD group was done with a .NET Web Service, LDAP query code and just a little extra .NET code to make everything work.

To build the navigation I used buttons for the View names and just ">>" for the separators.

Changing the buttons to be flat and a different color was done by selecting the button and then clicking the "Borders and Shading" menu button.

An opening rule was created to query the web service to return what security groups the current user is part of and then set the breadcrumb buttons visibility accordingly by using Conditional Formatting.  The secondary data source has one record with 5 fields and each field represents a button.  The first breadcrumb button is always available and that's why there are only 5 fields in the secondary data source.

As I said before a Conditional Formatting statement is used to disable the breadcrumb button if the matching field is equal to FALSE.  The only piece that took a little extra time was fine tuning of the LDAP query but after that was done then everything else was pretty easy.

The feedback from the client has been pretty positive and I'll probably use it again because it wasn't difficult to setup.  If anyone else has used another way for navigation in a browser based form it would be great to hear about it.  Opinions are always welcome.

Cheers