I recently needed to write a SharePoint Designer workflow to send a reminder email 12-hours before the end of a multiple-day event. It’s easy to use the Pause Until Date activity to pause until the necessary time, but it’s not so obvious how to calculate the date and time ithe workflow needs to pause until.

The easiest way to calculate the Pause Until date and time was to create it as a calculated column in SharePoint. Note: although the screenshots in this post are from SharePoint 2010, the principles are the same in SharePoint 2007.

In the Name and Type section, for the name of the column name I used DateTime for PauseUntil. I selected the Calculated (calculation based on other columns) data type.

In the Additional Column Settings section, I temporarily entered a formula of =[End Time]-1, selected Date and Time as the data type, and selected Date & Time as the format.

You’ll notice in the screenshot here that there are three different End Times and the calculated column subtracts exactly 24 hours for the =[End Time] –1 formula.

If we divide 1 day by 24 hours and divide 24 hours by 60 minutes, we get 1/24/60 = 0.00069444444. So, if you change the formula to =[End Time]-1/24/60…

you’ll notice that the dates and times in the Date for PauseUntil column are exactly one minute before the dates and times in the End Time column.

What this means, is that to add or subtract a certain number of minutes from a date and time field, we just need to multiply 1/24/60 by the number of minutes we want to add or subtract. In my problem, I needed to subtract 12 hours which is 12*60 or 720 minutes. Let’s see if that works…

Yep, all the calculated times are exactly twelve hours before the end time; and if you’re wondering, crossing midnight times doesn’t cause any problems for these calculations.

So, if you need to add or subtract a certain number of hours or minutes in a calculated column in SharePoint, you can simply multiply 1/24/60 by the number of minutes you need to add or subtract to achieve any value you need.

Special thanks goes to Mike Smith for his response in this discussion board thread for originally sharing this tip. I wrote this post in an attempt to break it down a little bit more and add screenshots in hopes that some others might find the information a little more understandable.

These are some notes from a helpful session today at the SharePoint Conference 2009 in Las Vegas delivered by Christophe Fiessinger and Jan Kalis from the Microsoft Project Server team.  The notes are as organized as I can make them while I’m sitting here in the room, but they will of course not be as polished as I’d like them to be.  I’ve decided to err on the side of more information – less polish.

What exactly is Project Server 2010, anyway?  To put it as simply as possible, it is meant to provide project and portfolio management on a large scale.  That means collaboration, schedule and resource management, and reporting.

We got some good news and some bad news.  Project Server now requires the full version of SharePoint 2010, not just the free Foundation product.  However, out of the box, the Project 2010 Professional client program can sync with any SharePoint task list.  So, you may not always need Project Server to share and collaborate on projects and integrate with the full client.  Project Server includes the ribbon interface to simplify matters, and allows you to do much of the simple day-to-day entries and changes through the web interface.  The new enhancements in Excel Services, Performance Point Services, and other SharePoint reporting pieces provide excellent BI capabilities.

Project Server now functions as an add-on service application in SharePoint 2010.  There is therefore a lot of flexibility in farm topology, and it is now fully compatible with load-balancing and high-availability.  It no longer uses ActiveX, meaning you don’t have to touch desktops for upgrades and updates.

System Requirements:

• Same as SharePoint 2010, plus
• SharePoint 2010 Enterprise
• Project Professional 2007 / 2010
• Excel 2007 / 2010
• IE 7 or 8 (other browsers not supported)
• Exchange 2007 (optional, but server to server integration is now available)
• Team Foundation Server 2010 (optional, but it is now integrated OOTB too)

Deployment Process

1. OS / updates
2. SQL Server / updates
3. SharePoint Server 2010 (not included with Project Server anymore)
4. Project Server 2010
5. SharePoint Configuration Wizard and Farm Configuration Wizard
   (Minimum Service Applications:  Project Server, Excel Services, Performance Point Services, Secure Store Service, State Service (for charting))
6. Central Admin Configuration
7. Project Web Access Admin Configuration
   (Time reporting periods for time tracking, base security by roles, Cube Building Service, Exchange integration)

PowerShell is supported through the entire process.

Should SharePoint and Project be run in separate farms or separate?  Depends…

Together:

• Server consolidation
• Administration and maintenance
• Leverage high availability and line of business integration
• But, update testing is more complex
• SharePoint Admins need more training
• Additional licenses required if the farm is large (Project will require a license on every server in the farm)

Separate

• Isolated update requirements
• No dependency on intranet farm availability
• Change management is simpler
• Security isolation
• Can still share a SQL server
• But, additional administrative tasks
• Duplicate governance

Capacity planning will not be terribly different than the 2007 version, although the new service applications that are available may affect the performance if you enable them all.

Upgrading

Do not skip the normal, boring analysis of your existing environment.  Project Server is complex and needs to be tested.

Project Server 2003 needs to be migrated via 2007, although it does not have to be brought all the way to production.

Project Portfolio Server 2006 / 2007 has 3 options:

1. Map functionality, develop desired functionality on 2010, and then use the gateway to transfer the data.
2. Finish existing projects in PPS 2007, and start new projects in 2010.
3. Use side-by-side with Project Server 2010, syncing via the gateway.

Project Server 2007 is direct and streamlined OOTB, with two options:

1. In-place Upgrade (remember 64-bit is required for SharePoint 2010, though)
2. 4 or 5 Database Attach (the upgrade occurs during PWA provisioning)

Backward Compatibility Mode is automatically enabled in all cases after upgrade, which allows 2007 Project Pro SP2 clients to work seamlessly.  Project Pro 2003 or older clients are just out of luck.  Multiple client versions on the same PC are supported as long as they are not running at the same time.  Backward Compatibility Mode makes it possible to attack the server and client upgrades separately.  You should always do the server first, because the 2010 client will not work with the 2007 server product.  Once all clients are up to 2010, backward compatibility can be turned off so that full functionality becomes available.  Here’s what is suppressed in Backward Compatibility Mode:

• Manually scheduled tasks are not available on the server or client
• Tasks cannot be set to Inactive
• Font strikethrough not available
• All Departmental custom fields enforced on Project Professional 2007

The 2010 file format has changed, and older client versions will not be able to open the new files.  The only way to convert or use them is to download the Project Pro 2010 Trial product.  This is due to the fundamental changes the Manual Scheduling introduces into project plans.  Opening these files in prior versions would be confusing or misleading to 2007 users, even if a converter was provided.

These are some notes from a REALLY great session today at the SharePoint Conference 2009 in Las Vegas delivered by Ryan McMinn, an Access Services guy at Microsoft.  Lots of good, detailed information here.  The notes are as organized as I can make them while I’m sitting here in the room, but they will of course not be as polished as I’d like them to be.  I’ve decided to err on the side of more information – less polish.

This is a follow-up to a more basic session on building apps yesterday (which I didn’t attend).  Access has long been valued by business users for its rapid application development capabilities.  It’s also been hated by many IT people for its lack of security, management, and disaster recovery.  Since the 2007 version, Access and SharePoint have been friendly with each other.  Now however,fully integrating Access via SharePoint 2010’s Access Services addresses IT’s concerns and actually makes it more powerful for business users at the same time.

A big emphasis has been put on providing database templates to help people get started, including community templates uploaded to Microsoft from you and me.  One of the OOTB ones is a ‘Web database’.

Miscellaneous Access Client Improvements

Building table schema in datasheet view is easier now, as the column headings allow you to pick the data type on the fly.

Data Models are groups of fields you can create and insert as a group.

Data Types allow you to insert common calculated fields along with the fields they reference.

The Macro writer has been amped up to be almost like working in the VBA editor, but still maintaining the low-security-required environment for web publishing.  This adds enhanced capabilities to do full If Then Else structures, comments, parameters with full Intellisense.

The new Web Browser Control allows you to show web content in your forms by parameter-based URLs.

Publishing to Access Services

You do this in Backstage (Office menu > Info; or Office menu > Share)

Tables turn into lists, forms turn into aspx pages, reports turn into rdl files (Rptg Svcs), and macros turn into workflows.

It creates all of this in its own sub-site.

Full web functionality is only available on native Access lists that are moved up to SharePoint.  External lists are usable in the full Access client, but not via web forms.  This is true even if you are leveraging BCS to bring that data into SharePoint.

You can also save your database design as templates that get saved into SharePoint as wsp solution files.

Macros are great for:

Change validation

Change tracking

Notification

Workflow

Aggregates (doing them this way gives better performance that queries)

Bulk updates

Custom calculations

There are Before * data macros that trigger before the database is updated.  They are meant to be quick, and are good for validation.

After * data macros trigger after the change is committed to the database.  They can be much longer, looping, firing other macros…

Named data macros are not triggered automatically, but are called by other macros or button clicks.  Functionality is expanded like the After * data macros.

These are some notes from a REALLY great session today at the SharePoint Conference 2009 in Las Vegas delivered by Daniel Kogan, the Metadata Service Program Manager at Microsoft.  Lots of good, detailed information here.  The notes are as organized as I can make them while I’m sitting here in the room, but they will of course not be as polished as I’d like them to be.  I’ve decided to err on the side of more information – less polish.

Our content types are now free to roam throughout the enterprise!  We also now get the ability to standardize and manage our meta-data tagging easily across different content types, usage scenarios, and storage locations.  Now, tags can be hierarchical just like your site or folder structure.  They can also be localized by language.

There are several questions we need to consider when we begin to contemplate this type of standardization.  They are similar concerns to what we had to consider when planning content types and site columns within a site collection, but obviously the issues can be far more complex when the scope increases across the enterprise.

Is it the same?

Does this have the same structure, policies, defaults everywhere in the enterprise?  Are things of content type “A” always the same?

What is it?

Do I trust it, recognize it?

Where is it?

Searching, navigating…

What happens to it?

Retention, workflow…

Terminology:

Hub – A site collection designated as a “souce” from which we share content types

CT Syndication – Publishing, sharing, pushing one or more content types across site collection, web app, and farm boundaries

Taxonomy – a hierarchical tree

Folksonomy – informal flat list of adhoc values

Term Store – DB that contains taxonomies

Groups – a security boundary within the term store and above the term set

Term set – a hierarchical section in the term store that contains many individual terms – e.g. Months, or Regions. (max 1000 total per store)

Term – a node in the taxomy with an ID and many text labels (30,000 per term set, max 1million total)

Tagging – applying metadata to an item (authoritative or social)

Keywords – not just a text string, a reference to a node in the hierarchical term store.  This is particularly powerful, standardizing tags for future use.  As you enter keywords, a suggestion list displays showing the entries in the hierarchy.

Content Type Syndication

• Each Metadata Service app can publish through a maximum of 1 hub
• It is not a requirement to syndicate or consume from the service
• Content Type with all corresponding columns, policies, and workflow association
• From the hub, you can publish, unpublish, republish, and roll up errors
• From the consuming side you can extend, derive, view errors, refresh from the hub

If you are working on the service in Central Admin, it is created with whatever name you like – configuring where the hub site collection is.  Then a connection service is used to consume from the original service.

If you are working in the hub, you can simply go to the Content Type gallery, click the type and choose to Manage publishing…  In the background, the Metadata Service creates a cab file that is versioned.  So the consuming web apps, site collections, and farms can check regularly for new versions of subscribed content types.

On the consuming side, it shows up like a normal content type – but it’s read-only.

Applying Metadata

Web Browser – Edit Properties, by a business process, location-based (inherit by virtue of where it is stored)

Rich Client – DIP, Backstage (Office menu > Info).  The Office client fetches the term set when it opens the document, so that recently added terms are available.

Once the meta-data has been entered using the term store, the column headings AND left-hand navigation displays the tag taxonomy as a means of filtering or pivoting the library listing.  If that doesn’t create a mental picture for you, it’s kind of like automatic, faceted, dynamic folder trees based on tags.

Managed Metadata columns are like a ‘super choice’ field that looks up in the hierarchical term store and filters exclusively or inclusively.  The user can even add entries to an ‘open term set’, while ‘closed term sets’ are owned by someone and closed to others.

Managed Keyword columns are like a ‘super text’ field where the user can type, and they are given suggestions from the term store.  If their entry doesn’t exist, the system will add it automatically.

The Term Store Management Tool

You find this at Home > Site Settings > Site Administration > Term Store Management

This tool allows you to access  all available term stores, to copy, reuse, merge, delete… individual terms.  Merges ripple through all the millions of documents that use them, and the two are now synonyms of each other (including any natural abbreviations).

SharePoint 2007 came with 23 out-of-the-box actions to fit many of the most common tasks needed in workflows. SharePoint 2010 comes with 44 out-of-the-box actions. All the original actions are still there, but you now have 21 more! Note: This post and all screenshots are from the Technical Preview and things may change before SharePoint 2010 is released to the public.

In SharePoint 2007, if you wanted to get approval from a user for something, you usually had to use the Collect Data from a User action, store a reference to the task ID for the task that was created, and then lookup the user’s input later in your workflow as needed. Microsoft has streamlined this process in SharePoint Designer 2010 with the actions for Assign Item for Approval, Assign Item for Feedback, and General Task Process. In this article, we’ll take a look at how these three actions work.

The first thing you will notice when you add any of these three actions to your workflow is that in the Editor, they all look identical. In the following screenshot I have added an Assign Item for Approval action to Step 1, and Assign Item for Feedback action to Step 2, and a General Task Process action to Step 3.

So, how can you tell them apart? You have to click on the Approval Process link for the action. When you do this, you will see a new page where you can define and customize that overall task process.

Let’s work our way around this page to explain what each section does.

Task Information

In the Task Information section there are only two options, you can change the name of the Approval Process and the Owner of the task.

Both of these can be changed by clicking on the information you want to change. The Name is a meaningful name to you for this particular process. I recommend changing this to a descriptive name that will help you remember later what this particular process is for and to keep the various approval processes in your workflow distinct. The Owner is the owner or this process; you’ll see where this comes into play when we get to the Settings section.

When you change the Name here it also changes the name in the workflow editor.

Task Outcomes

The Task Outcomes section, you can define the various outcomes for this particular process. If you selected the Assign Item for Approval action, you will already have the options of Approved and Rejected listed here.

If you want to add other options, you can click the New button to add a new option. In the example here, I have added a Skipped option.

The items you list here will be listed as separate buttons on the task form and will be displayed in the Sequence order you select.

Task Form Fields

The Task Form Fields section allows you to collect other information in the task form. For example, if something is approved or rejected, you probably want the person who is doing the approving or rejecting to enter some notes about why the chose to approve or reject it.

By clicking the New button, you can add your custom data fields to the form. There is a short wizard that will walk you through the process.

If you click the Choose existing field button, you will get a dialog box where you can choose from any of the Site Columns that are already available on your site. This can be incredibly handy since you can define the column one time and then reuse it as needed.

Settings

There are only three options in the Settings section.

If you check the box for Only allow task recipients and process owners to read and edit workflow tasks then those are the only people who can do those activities. Do you remember when you could set the process owner in the Task Information box? Well, this is where that process owner is used.

There are also boxes to allow users to reassign the tasks and to change requests.

Customization

I’ve saved this section for last because it is probably the most complex of the five configuration sections.

Clicking on the Return to Workflow link simply closes the Approval Process editing window and returns you to the workflow Editor.

If you click on Change the completion conditions for this task process you will be taken to the Check Exit Conditions section where you can see the underlying conditions that are part of this activity. This is probably where you begin to see how powerful these new Approval Actions really are. Here is a screenshot of the default page without making any changes:

You can add more conditions here if needed, add steps and generally modify the process as you need.

If you click on Change the behavior of a single task you will be taken to a place where you can see and change the behaviors for On Task Assigning, On Task Pending, On Task Expired, On Task Deleted, and On Task Completed. Within each of these, you can add your own conditions, actions, and steps. Here is a screenshot of the default page:

If you click on Change the behavior of the overall task process you will be taken to a place where you can see and change the behaviors for On Approval Started, On Approval Running, On Approval Canceled, and On Approval Completed. Once again, within each of these, you can add your own conditions, actions, and steps. Here are screenshots of the various sections of the default page:

Whew! That’s a lot of options and configurations that you can do! But were not yet finished! Remember that these were just he settings for the process. If you return to the workflow editor you’ll see that there are still two more options we haven’t looked at yet.

Item to Be Approved

You probably expect that the only item on which you can perform the Approval action is the current item. However, if you click on Current Item, you can change that to any list or library item you want.

Assigned to Users

Another thing that I found frustrating in SharePoint 2007 was that I couldn’t customize the task and emails that they sent. When you click on the link in SharePoint 2010 to add users, you get a dialog box where you can customize task request, including the title and body that can include lookups. You can even add serial and parallel recipients and have multiple assignment stages.

Well, there you have it. You’ve gotten a general overview of the options for the new Approval Actions in SharePoint Designer 2010. If you are overwhelmed, I’m not surprised. In this post, we’ve only just barely scratched the surface of the great things you can do with SharePoint Designer Workflows in SharePoint 2010. You’ll probably need to take a class to learn it all. J

I may have a new favorite web part in SharePoint 2010—at least it’s my favorite for a while. J I am a bit of an Excel fanatic. I love drilling down into data to gain obscure business intelligence. I love pivot tables, and I love graphs—although I’ll admit I’m much better at pivot tables than I am graphing. It’s easy to get caught up in displaying your data in Excel—it becomes much more like playing Excel than working with Excel. The new Chart Web Part is one of those fun web parts that you can get caught up in playing with but it also solves the common business problem of, “How can I quickly display a chart of some data on my SharePoint page?” Note: This post and all scresnshots are from the Technical Preview and things may change before SharePoint 2010 is released to the public.

Excel services is a great way to render charts in SharePoint, just set up the chart in Excel and then add an Excel Services web part to display the named chart from your published spreadsheet. But with the Chart Web Part, you can not only chart information from your Excel Services spread sheets, but also from any SharePoint list. And best of all, you configure everything in your browser!

Adding the Chart Web Part to a Page

You’ll find the Chart Web Part in the Miscellaneous category and you add to your page just like you would any other web part by selecting it and clicking the Add button.

You will see that SharePoint 2010 adds the web part and it displays a new chart based on some random data.

Configuring the Chart Web Part

With most web parts, you expect to configure them in the tool pane, although you can certainly do that by clicking on Modify Shared Web Part, you will probably want to use the wizards to configure it. You can access the list of wizards by clicking on Wizard List.

The Chart Web Part Wizards

There are two wizards: one to Customize Your Chart, and one to Connect Chart to Data. We’ll begin by using the Connect Chart to Data wizard and then we’ll customize our chart later.

The Connect Chart to Data Wizard

The first step in the wizard is to choose a data source. Notice that you have the options to Get Data From Another Web Part, Connect To a Site Definition List, Connect to Business data Catalogue, and Connect to Excel Services. This is a nice set of options. I have some Sales Data in a SharePoint list and that’s what I want to chart, so I’ll select the option to Connect to a Site Definition List.

In the second step of the wizard, I select the site where my data is stored and the list. Both of these are displayed in drop-down list boxes and I can select lists and libraries anywhere in my site collection—not just the one I’m currently working in, which is a very nice feature.

In the third step of the wizard, it shows me a preview of the data that is available for charting. Here is what it shows me for my 2009 Sales list:

Here is what the SharePoint list looks like if you view the default view directly:

In the fourth and final step of the wizard, I bind the chart to the data. You’ll notice that there is an impressive set of options—you can configure this connection in almost any way you can imagine.

For this blog post I used 4 series that were each set up to display a unique quarter (Q1 Sales, Q2 Sales, Q3 Sales, and Q4 Sales). You can’t see these all at once, so I’m not adding screenshots. I didn’t add any other Data Point fields, Formulas, or change the Advanced Properties. At this point, I had finished the Connect Chart to Data wizard. I could see my data displayed in the Chart Web Part.

It was nice to see my chart rendered so quickly, but I wanted to change the way it looked, so I ran the Customize Your Chart wizard.

The Customize Your Chart Wizard

This is the wizard that will allow you to change the way your chart looks. Once again, you have a very impressive number of options here.

The first step is to select a chart type. I counted 158 different types of charts! You have two dimensional and three dimensional charts in types of column, bar, area, line, point/bubble, financial, pie, radar, polar, gantt/range column, range, error bar, box plot, funnel, and pyramid. I know that with this set of options, it’s kind of boring, but I selected the Column with Perspective as my chart type. It just seemed appropriate for this kind of sales data.

In step two I changed the appearance of the chart. There are 12 themes you can choose from: Bright Pastel, EarthTones, LightSteelBlue, GreyScal, SemiTransparent, ExcelLike, LightBeige, Berry, Chocolate, Fire, GreenBlue, and Custom. I selected the LightBeige just because I liked it. For Drawing Style, you can select from Default, Cylinder, Emboss, LightToDark, and Wedge. I selected Cylinder. Transparency can be set from 0% – Solid to 100% – Invisible. I left it at the 20% default. For the Size and Format I could set a cusome chart width, chart height, and even image format (JPG, BMP, or PNG)—I left all three of these set at the defaults. One of the things I really like about the Chart Web Part is that it generates an actual image on the page and not something that is rendered by AJAX or ActiveX or SilverLight. Since it’s an actual image, I can easily copy and paste it into another document, or email, or even save the image if I want.

At this point I’m finished with both of the wizards and I can see my finished chart on the page

Advanced Properties

The only thing I haven’t shown you yet is the Advanced Properties. If you click on this link, you can view a properties dialog where you can change various properties of the image itself like antialiasing, shadows, border line colors, and much more.

So, what are you waiting for? You know you want to play some at work, so go ahead and add a Chart Web Part to your SharePoint 2010 site and start playing around. You can tell your boss I said it was OK. J

In my opinion, lists and libraries are the heart and soul of SharePoint. When I first installed the Microsoft SharePoint 2010 Technical Preview, the first thing I did was create a new list just to see what the most obvious changes were. Note: This post and all scresnshots are from the Technical Preview and things may change before SharePoint 2010 is released to the public.

I use custom lists for lots of things, so I started by creating a new custom list. The creation process wasn’t very different from the process in SharePoint 2007.

The List Ribbon:

Very quickly I noticed that the familiar toolbar at the top of the list had been replaced by a ribbon, similar to the one in the Office 2007 applications.

The default view of the ribbon after creating a list is the List ribbon. Although I’m used to using the menus, the ribbon allowed me to more quickly create views, create columns, change list permissions, go to list settings, and much more.

The New Item Form and the Edit Ribbon

To add a new item to the list, I switched to the Items ribbon and clicked the New Item button. Instead of switching to a new page to add my new item, a new form appeared in front of my list and it appeared the lights were dimmed on SharePoint and new form had a spotlight on it. The new item form had a ribbon itself with quick access to Cut, Copy, and Paste commands, a button to attach a file, a spell check button, as well as the expected Save and Cancel buttons.

The Items Ribbon

When I checked the box beside one of the items I added in my list, I saw options to work with that item in the Items Ribbon. From here I could view the item, edit it, delete it, and change its permissions. I cold have done more if my simple custom list had more functionality.

Allow Duplicate Values?

One of the things that has always frustrated me is that I didn’t have a good way to keep people from entering the same item multiple times in a list. In the Additional Settings section for the Single line of text, Number, Currency, Date and Time, Lookup, Person or Group, Business data, and Managed Metadata there is a setting for Allow Duplicate Values.

If you select to not allow duplicate values for a particular column, and you try to add a new item with an existing value in that column, you will receive an error and have to fix the error to proceed.

Column Validation

In the past, we didn’t have any way to validate a user’s input in lists and libraries. When you add new columns of type Single line of text, Choice, Number, Currency, or Date and Time you will see a section called Column Validation. Here you can enter Excel type formulas that must evaluate to True before the user can add the item to the list.

If the user’s input doesn’t validate according to your formula, the User Message you entered will be displayed to the user.

There are also validation settings on the list itself that work similar to the validation settings on the columns.

Metadata Navigation

In SharePoint 2010 we’ll be able to better locate our data based on its metadata. This can be configured separately on each list and is a whole other blog post in itself. I thought you might want to see a screenshot of the settings though, so here you go!

Form Settings

In the past, if you wanted to change the way a form looked in SharePoint for entering new information or editing information, you had to create a whole new form using SharePoint Designer. Now, all those forms are InfoPath forms and selecting the option to edit the form opens it in InfoPath where you can make any changes you want, then just republish the form.

Conclusion

I’m sure that as we all dig deeper SharePoint 2010 we’ll find even more goodness there, but these were the first things that caught my eye and I wanted to share these with you. I think these are all significant advances over lists in SharePoint 2007. What do you think?