Business Problem

One of my clients wanted to send email reminders to users one week prior to the project assigned tasks Due Date.

Solution

As you may or may not know, MOSS does not have that built-in functionality, but it is easily accomplished via a workflow created using the SharePoint Designer 2007.

Architecture

Calculated Column
I feel that working with complex date calculations (view my other post for a more date formulas  SharePoint List Calculated Column - Calculate Business Days) are best done on the SharePoint List side.  Therefore, I decided to have a calculated column that would already have the DateTime for when I need to send my Email Alert.

In this scenario it was fairly straight forward and so my formula simply looks like this:


This is what the column looks like when viewing the list items.  If you look closely, you will see that the column Days Prior To Due Date shows 3 days, but my email reminder column says the date to send my email is 6/21/2008.  The reason is that the formula on that column excludes weekend days!   

Workflow

The first Step on the workflow – Set Reminder Time

• I use the Add Time To Date Action, but don’t actually add time, I simply capture the value of the column TaskEmailReminderDate and store it on a variable called ReminderDate for use on my Step 2.
• Store the Task Title on a variable called SubjectTaskTitle, used when sending my email
• So that I can debug, I simply use the Log To History List Action to view the value of the ReminderDate variable

The Second Step on workflow – Send Task Reminder Email

There are no conditions, this step simply uses the Pause Until Date Action and uses the variable on Step 1 called ReminderDate to send out my email.



NOTE:
When I was testing my workflow, I was of course logged in as the “Administrator”, and so the workflow always failed to start automatically!  View this KB article for details.  My resolution was to simply create another account in AD and grant that account admin rights to my portal.  This prevented me from updating the Application Pool Identity for my Web Application.

Well I hope this helps you and let me know if it did!

Cheers,
Oscar

Ran into a nice little scenario where my client wanted to know how many business days were remaining prior to a Task Due Date. Once I had this date, I built a nice DataViewWebPart (see Figure 3) with the SharePoint Designer! This is what I came up with, hope it helps you!

A) Create a calculated column; call it something like "Days Prior to Due Date" in my case.

b) Paste this value into a Calculated Column, formula
=(DATEDIF(Today,[Due Date],"d"))-INT(DATEDIF(Today,[Due Date],"d")/7)*2-IF((WEEKDAY([Due Date])-WEEKDAY(Today))<0,2,0)+1

C) Make the output type "Single line of text"

NOTE: At the time of creating this calculated column, you must have another dummy column called "Today", it does not matter what data type it is. Once you create your calculated column, make sure to delete the Today column or your values will not work!

FIGURE 1 – Shows the formula for your calculated column to count only weekdays.

FIGURE 2 – Shows the sample Due Date and the calculated column output (far right)

FIGURE 3 – Shows you a dashboard with some XSL logic in the background to output an image on the left, and the days remaining calculated column value on the far right

[UPDATE] - I am almost done with this WebPart and look forward to sharing some screenshots! The ability to view recurring events using the CQWP is a definite hit!  More to come :)

Kind of a short post, but thought it would be nice to share with everyone!

- Ability to configure the XSL files used to render it,

- Ability to override the CommonViewFields

- Ability to override the Query being used.

Have you ever gotten a request from your client on having short urls for some SharePoint sites, even if they are deep in the hierarchy of your portal?  Here is one way I have implemented this to allow a site to be accessible via a url such as http://editorial 

NOTE: This is not the same as using a host header Site Collection which you can create using the stsadm utility with a command like the one below.  This workaround is aimed at 'path-based sites'

stsadm.exe -o createsite
-url http://hoster.contoso.com
-ownerlogin contoso\siteowner
-owneremail siteowner@contoso.com
-hhurl http://www.contoso.com

The business requirement

For this particular project, the business users wanted to access SharePoint sub sites like http://portal.invent.com/Marketing/TeamA, or http://portal.invent.com/sites/Legal with a short url such as http://TeamA, or http://Legal.

Information Architecture and URL Planning

Site URL planning is in fact an activity that is part of the overall planning phase on any SharePoint deployment project.  There are many considerations you must take into account when planning URLs.  To learn more about what tasks are included on planning logical design, go here

Some of those include, but not limited to:
•    Portal taxonomy
•    Global Navigation
•    Storage limits for individual sites
•    Restoring Sites or Site content

These considerations affect your decision on exactly what type of site you will create on MOSS/WSS 3.0.  As you may or may not know, there are several types of sites that one can create, each one is created using different methods.

      Types of Sites
      Host-named Site Collections – These are created using the stsadm
       Host-named site collections give you more control over URLs. However, there are tradeoffs.
First, host-named sites are only available through the default zone. Users who are configured to authenticate through alternate zones cannot access host-named sites. Second, the alternate access mappings feature does not work with host-named sites.

Standard Site Collection – These are created using the Central Admnistration > Web Application > Create Site Collection option.

Top Level Site - These are created using the Portal Site Actions > Create Site option.  You can a url like http://portal/sitename

Assumptions
1.    You are familiar with DNS
2.    You are familiar with running and administering IIS Web Sites
3.    You are familiar with administering MOSS 2007 and/or WSS 3.0
4.    These steps assume you already have the portal running on a Web Application that responds to the url http://portal.invent.com for this example

Configuration Steps

1.    Create DNS entry such as editorial.invent.com
2.    Create an IIS Web Site, type http://editorial for the Description (this IIS site is not provisioned by MOSS, it is a simple regular IIS web site)
3.    Type the host header editorial.invert.com (leave the port on 80
4.    Type another entry in the host header area, this time, just with editorial

Your window should now look like this

FIGURE 1 – Shows the host header entries you should have on your IIS Site

And as a last note, we can have both Path-based Sites and Host-named Sites on the same Web Application. You can learn more on Planning for Host-named Site Collections here 

NOTE: Alternate Access Mappings is not used here, because again, MOSS does not know about this IIS Web Site at all, it is not provisioned as a Web Application. 

Cheers,
Oscar

As you may or may not know, there are thousands of ways to accomplish development and customization tasks in MOSS 2007 and WSS 3.0.

At one point or another, as a MOSS Consultant, you will be faced with the task of creating custom application pages.  If you have an extensive web application development experience (much like I do), you will first think of using your web application techniques when faced with this task.  However, there are better and faster ways of accomplishing customization tasks around SharePoint. 

Today I will walk you through one of those scenarios where it would make sense to completely use your typical web application skills and techniques, then show you how to do it the MOSS way!

Scenario

You are creating a custom Site Provisioning Tool for MOSS 2007, and want to allow users to enter specific information so that the new site is created.  Once the site is created, you want to add an entry in a SharePoint List to track all the sites that have been created, and maybe if you are motivated create some custom views on the List to view by Region :) 

Solution

For example, I created some fields on my page that map to the fields on a SharePoint List to track newly created project sites on a List called Projects

This is what the page looks like when browsing to it.

FIGURE 1 - Shows the custom site provisioning application page with all the form field controls rendered

The table below shows you the actual field type used for each field on the form.
SharePoint List Fields

The table below shows you what out of the box SharePoint Form Controls you will use

Field Types and corresponding Field Controls used on ASPX page

So here is the interesting part on this blog entry!

TIP: Throughout the various Application Pages on MOSS, there are samples of how the FormField is used.  Look around, you will be amazed what you can learn just by firing up VS 2005 and viewing the code for these pages! 

Rendering the Region Field (Choice) 

Our task here is to render the Region Field which is a Choice Menu on the SharePoint List. 

The Most Obvious Way of Populating the Choice Field
Now, typically, to render the drop down menu and populate it, you would probably do the following:

a) Drag a DropDownList Control on to the page
b) Wire an event handler on the page load event, and then query the SharePoint List using CAML
c) Iterate through the results and populate the DropDownList

The Rapid Method of populating the Choice Field

a) got to your page code and type code so that you can add a FormField Controls as such

FIGURE 2 - Shows you how you can add an out of the box FormField, set its properties so it gets wired to the appropriate column on the SharePoint List 

NOTE: The FieldName should be the same as the one specified on the List
b) Wire the FormFields's OnInit event handler - this is where we are going to tell the FormField what List we are inserting data into.
c) Write code to get a handle on the List - I used a page global variable and set its value  on OnPreInit event
d) On the FormField's OnInit handler, specify the ListName property

And that's it!  Now if you browse to the aspx application page, you should see the DropDownList populated!


Rendering the IT Owner Field (PeoplePicker)

Have you ever wanted to use that nice User Picker Control that you see on various WSS/MOSS pages?  You can!

I dropped the People Picker onto my ASPX custom application page like so:

FIGURE 3 - How to specify a PeopleEditor Control in a declarative manner

To obtain the values the user has selected, you need to do something similar to my code-behid page below.  Here I am capturing the value (using PickerEntity Class), and inserting a new list item on my SharePoint List.

FIGURE 4 - Shows you how to obtain or capture the values entered in the PeopleEditor Control.

TIP: One more thing I would like to point out in the code on Figure 4.  I am also inserting a value in a Hyperlink or Picture Field Type.  You will notice that after putting the url value, i have an additional piece of information, that is the link label! in this case, 'Go To Site'.  This is how it looks on the SharePoint List

FIGURE 5 - Shows how the link label looks based on the code used on Figure 4 to programmatically populate the Hyperlink or Picture Field Type.

I hope this information is useful to you at some point!

Enjoy,
Oscar

Recently, I was working on a project that required the  creation of an IT Project Management Site Template.  I thought, hmm, pretty straight forward, but wait, I ran into an issue that I am positive, could have been solved in other ways (List Event Handler)

The requirement was to display an image based on a certain condition, and this condition had to do with lookup of other List Column values. 

If you want to display images for a calculated column here are the steps you need to take:

1. Modify the FLDTYPES.XML file(or make a copy of it!)
2. Search for the field definition "Calculated"
3. On the default rendering pattern, paste the following
   
   <Switch>
   <Expr>
   <GetFileExtension><Column/></GetFileExtension>
   </Expr>
   <Case Value="giF">
   <HTML><![CDATA[<IMG SRC="]]></HTML><Column HTMLEncode="TRUE"/><HTML>"</HTML>
   </Case>
   <Default>
   <Column HTMLEncode="TRUE" AutoHyperLink="TRUE"
   AutoNewLine="TRUE"/>
   </Default>
   </Switch>

   Create a new Column of type Calculated - put some logic in it like so
   
   =IF(AND(Impact="3",Probability="1"),"/_layouts/images/KPIDefault-1.giF",
   IF(AND(Impact="3",Probability="2"),"/_layouts/images/KPIDefault-2.giF",
   IF(AND(Impact="3",Probability="3"),"/_layouts/images/KPIDefault-2.giF",
   IF(AND(Impact="2",Probability="1"),"/_layouts/images/KPIDefault-1.giF",
   IF(AND(Impact="2",Probability="2"),"/_layouts/images/KPIDefault-1.giF",
   IF(AND(Impact="2",Probability="3"),"/_layouts/images/KPIDefault-2.giF",
   IF(AND(Impact="1",Probability="1"),"/_layouts/images/KPIDefault-0.giF",
   IF(AND(Impact="1",Probability="2"),"/_layouts/images/KPIDefault-0.giF",
   IF(AND(Impact="1",Probability="3"),"/_layouts/images/KPIDefault-1.giF","other")))))))))
   
   NOTE: The Impact and Probability are columns in the SharePoint List.  Based on this logic, I am re-using the images from the KPIs in MOSS and simply pointing to them.
   
   And that's it!   You now can output images for your List.  Here is how it looks
   
   

4. Technorati Tags: wss 3.0,moss 2007,calculated Fields,caml