Monthly Archives: December 2010

SharePoint 2010 and InfoPath forms

Here are some basic facts about InfoPath. InfoPath is a versatile data-collection tool designed to make the creation of forms within SharePoint extremely easy, efficient, and connected to out-of-the-box or custom workflows.

InfoPath 2010 is available with Office Professional Plus 2010

You can use InfoPath 2010 to customize list forms on SharePoint. Here is an example how to do this How to start customize list form (New/Edit/View) using InfoPath Designer 2010.

More interesting is how we can use InfoPath in business solutions and workflows. Here are a few examples of the types of business solutions and applications that use InfoPath forms:

  • Form-driven applications start with a form that is used to gather data and add in other SharePoint functionality to process and report on the data including workflows, reporting, custom Web Pages, and external lists. Examples include a help desk Web site, a procurement center, or an employee hiring center for a human resources department.
  • Document workflow applications automate document-based business processes. Adding InfoPath forms to workflows lets you gather information from the users at predefined times in the life of the workflow and let users interact with the tasks for that workflow. Examples include contract approval, technical specification review, or processing for legal documents.
  • Business data applications start with data in an external system that is made available in SharePoint Server through Business Connectivity Services. Data in the external systems is accessible in SharePoint Server through external lists and can be added, viewed, updated, and deleted by using InfoPath forms. Examples include customer relationship management and enterprise resource planning.
  • Ad hoc business processes can be managed by using SharePoint lists. The forms used for creating, reading, and updating items in these lists can be customized by using InfoPath 2010. Examples include simple issue tracking or tasks lists.
  • Document information panels use InfoPath forms to display document metadata within the Microsoft Office system client application.

InfoPath consists of the following main components:

  • InfoPath 2010 is the Office system client application that has the following two modes:
    • Microsoft InfoPath Designer 2010 is used to create form templates. Form templates define the data structure, appearance, and behavior of a form. After the design process has been completed, form designers publish the form template to SharePoint Server so that users can start filling out forms based on that template.
    • Microsoft InfoPath Filler 2010 is used to fill out forms that are based on form templates created in InfoPath Designer.
  • InfoPath Forms Services in Microsoft SharePoint Server 2010 enables forms to be rendered in the Web browser. It is available as an Enterprise feature of SharePoint Server 2010. Farm administrators configure settings for InfoPath Forms Services on the SharePoint Central Administration Web site.

There are two main form-filling environments, the Web browser and InfoPath Filler. Any form can be filled out in InfoPath Filler. Only browser-enabled forms can be filled out in a Web browser. SharePoint list forms can be filled out offline by using SharePoint Workspace. Form library forms can be filled out offline by using InfoPath Filler.

SharePoint list form templates cannot contain managed code or repeating or nested data. If the overall solution requires managed code or complex data structures in forms, you must use a form library. Form designers can add managed code to their forms by using Visual Studio Tools for Applications (VSTA). VSTA is an optional installation component available in Setup for InfoPath 2010.

A form library is a SharePoint document library that uses an InfoPath form as its default content type. Forms that are filled out by users are stored as XML files in the library. Form library forms should be used if your solution requires any of the following features:

  • Repeating or nested data
  • Digital signatures
  • Managed code
  • Form data stored as XML

Form designers publish form templates by using InfoPath Designer.

Here is a nice description, how to design the InfoPath form and how to use it with the workflow: InfoPath 2010 with SharePoint 2010: A Walkthrough.

Here are some other useful links on InfoPath:


Use a Three-state workflow – SharePoint Server – Microsoft Office

Use a Three-state workflow – SharePoint Server – Microsoft Office.

Enhanced by Zemanta


Getting Started With ScribeFire – Scribefire: Fire up your blogging

SPD Conditional Formatting and Date comparison

My basic challange was how to conditionally format rows on the SharePoint lists, that fulfil selected criteria. I have a column DueDate. If the task is delayed, I’d  like to render it on the orange background.

Date format in my country (Slovenia) is different from the US format. By the way, Slovene date formatting is very similar to the German one and date formatting of other countries in Europe. So definitelly, they have the same problem.

Let’s go back to the challange. I concluded that the basic reason, why conditional formating doesn’t work, is different date formating. It compares US Format date with the date in Slovene  format.

Besides that I found the basic idea, how to “build” the date comparing function. Idea is to convert both dates to numbers and thaen compare this two numbers. Numbers are bulid according to the following patern: yyyyMMdd. This pattern guarantees that dates are comparable. The post where I got the idea is here:

[number(translate(substring-before(@EventDate,’T’),’-‘,”)) >= number(translate(substring-before($Today,’T’),’-‘,”)) and number(translate(substring-before(@EventDate,’T’),’-‘,”)) <= number(translate(substring-before($Today,’T’),’-‘,”)) + 7]

What I can do now? I went through the available Date and String functions in the “Advanced Condition dialog box” and decided to re-format Slovene date to the US one.

Here are the functions, which I can use.

Function Today, TodayIso, FormatDateTime and Format Date are described here.

You can find LCID (Locale Identifier) values on this site

(LCID for Slovenia = 1060, LCID for USA = 1033)

Here is complete description of functions in ddwrt namespace (available in SPD advanced condition editor).

The biggest problem is how to re-format date. I found that the functions FormatDate and FormatDateTime work very specifically and are bad documented.

Here are some examples of the use of these functions.

Examples of FormatDate function (examples in read use TodayIso function; example in blue uses different LCID):

ddwrt:FormatDate(ddwrt:Today(),1060,1)     16.12.2010

ddwrt:FormatDate(ddwrt:Today(),1060,3)     16. december 2010

ddwrt:FormatDate(ddwrt:Today(),1033,3)     Thursday, December 16, 2010

ddwrt:FormatDate(ddwrt:Today(),1060,4)     0:00

ddwrt:FormatDate(ddwrt:TodayIso(),1060,4)     11:02

ddwrt:FormatDate(ddwrt:Today(),1060,5)     16.12.2010 0:00

ddwrt:FormatDate(ddwrt:Today(),1060,7)     16. december 2010 0:00

ddwrt:FormatDate(ddwrt:Today(),1060,12)     0:00:00

ddwrt:FormatDate(ddwrt:TodayIso(),1060,12)     11:01:15

ddwrt:FormatDate(ddwrt:Today(),1060,13)     16.12.2010 0:00:00

ddwrt:FormatDate(ddwrt:Today(),1060,15)     16. december 2010 0:00:00

ddwrt:FormatDate(ddwrt:TodayIso(),1060,15)     16. december 2010 10:58:41

Then I tried function FormatDate with some string. The function doesn’t like a dot as a delimiter in the date format:

ddwrt:FormatDate(‘3.1.2010’,1060,15)      *** nothing displayed ***

By default the function recognizes the date as US version (MM-dd-yyyy). If the date is not logical e.g. ’13-1-2010′, then the function recognizes it as an european date ‘dd-MM-yyyy’. Very interesting, but nowhere documented. How can we use this function in Europe? The function is useless for us. Perhaps somebody knows something more about this.

ddwrt:FormatDate(‘3-1-2010′,1060,15)       1. marec 2010 0:00:00

ddwrt:FormatDate(’13-1-2010’,1060,15)       13. januar 2010 0:00:00

ddwrt:FormatDate(‘1-13-2010’,1060,15)       13. januar 2010 0:00:00

Function FormatDateTime has different parameters:

FormatDateTime(string szDate, long lcid, string szFormat);

string szDate – either an existing date column or a function (e.g. ddwrt:(Today())
long lcid – a number corresponding to the locale id (e.g. British English is 1033)
string szFormat – e.g. ‘yyyyMMdd’, ‘dd/MM/yyyy’ etc.

Here are some examples on FormatDateTime function:

ddwrt:FormatDateTime(‘1-13-2010′,1060,’yyyy’)           2010

ddwrt:FormatDateTime(‘1-13-2010′,1060,’yyyy_MM_dd’)        2010_01_13

ddwrt:FormatDateTime(‘1-13-2010′,1060,’yyyy-MMM-dd’)      2010-jan-13

I concluded that I can use function FormatDate only with Today and TodayIso functions, but I can’t convert dates in Slovene format. What I had to do is to convert both dates to numbers according to the ‘yyyyMMdd’ patern.

Here is my final solution.

number(concat(substring(string(@DueDate),7,4),substring(string(@DueDate),4,2),substring-before(string(@DueDate),’.’))) < translate(substring-before(ddwrt:TodayIso(),’T’),’-‘,”)

I used orange color for delayed tasks and yellow for the task that has to be completed today.

<%@ Register Tagprefix=”ApplicationPages” Namespace=”Microsoft.SharePoint.ApplicationPages.WebControls” Assembly=”Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> <%@ Register Tagprefix=”WebPartPages” Namespace=”Microsoft.SharePoint.WebPartPages” Assembly=”Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> <%@ Register Tagprefix=”Utilities” Namespace=”Microsoft.SharePoint.Utilities” Assembly=”Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> <%@ Register Tagprefix=”SharePoint” Namespace=”Microsoft.SharePoint.WebControls” Assembly=”Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> 16.12.2010 0:00
Enhanced by Zemanta

Conditional Formatting and date comparisons with SharePoint Designer 2010 «

Conditional Formatting and date comparisons with SharePoint Designer 2010 «.

SharePoint 2010 Workflow problem, The form cannot be rendered.

I received the following error message when I was trying to start the workflow in SharePoint 2010.

You need to install the ‘State Service’ via the Configuration wizard in Central Administration.

Here is the procedure step by step:

  1. Go to Central Administration and select Configuration Wizard

  1. On the next screen launch the Wizard.

  1. On the next screen Start the Wizard.

  1. On the list of many services you will find »State service«. In my case it was already selected on the form, but evidently not activated yet.

  1. Finish the form and wait until the »processing« stops. When the »processing« finisehed, I was offered the following form to create a new top-level Web Site. You can freely skip this step. What we needed was already done.

  2. Refresh your site and you won’t receive the error message again. Workflow finally starts working.

Excel, Sum best N numbers in range

Recently I faced an interesting challenge. How to create an Excel formula for calculating the final sum of the best N scores out of M (where N > M)?

The real situation is as follows. In our chess club we have 16 tournaments per year. The winner is the player who scores the most points in 12 tournaments. So, I have to sum the best 12 scores out of 16.

I was trying with different functions. An interesting one was the RANK.AVG function, which returns the rank of value in the specified range. With some additional formulas and a special transformation table I finally succeded. But …. It was to complicated and I was not happy with it.

As it is usualy so, I found another function after spending to much time.

The right function is LARGE or her sister SMALL:

The final formula in may case was:


(in the first step I sum all values and then subtract the four worst scores)

MS Project, Project Configurator for Cost Calculation

When we regularly run projects, that produce very similar end products, and besides that we use similar sequence of actions (e.g. we use the same implementation methodology), we can speak about typical projets. On a typical project we more or less start with one of the past projects and make some modifications on it. The basic benefit is that we don’t start with the “blank paper”.

The basic idea in my case was to build the project plan and cost estimates for the potential “full vesrion” of the final product, which can serve me as a configurator for the final product. What I need is a tool to to select only those components, which are interesting for the customer. After selecting appropriate components (activities) of the final product your project plan and cost estimates are ready (we can speak of seconds; what a benefit!!!).  In order to achieve this in MS Project, we have to create custom columns for cost calculation according to the selection critetria.

We add a new column by using “Insert Column” command on the context menu of the title row in the spreadsheet.

The “Cost” column is where the cost of activities are automaticaly calculated (depends on whetehr you have assigned resources to the task and a cost rate for the resources).

We will use “Flag 1” column for the selection flag (Yes – activity is included in the project ; No- this activity is not included).

The “Cost 1” column will be calculated according to the selection flag. If flag is “Yes” then cost will be included, otherwise the cost will be zero.

Now we have to configure the “Cost 1” column. Column will be calculated according to the “Flag 1” status. In order to start configuring the column, select the “Custom fields” column from the context menu.

Select “Formula” button.

In the “Formula” dialog box insert the formula: IIf([Flag1]=Yes;[Cost];0)

In case the Flag1 is “Yes”, the Cost1 column has the value of Column, otherwise it is zero.

Some warning dialogs appear when you close the dialog.

Wea are back in the “Custom fields” dialog. In order to “sum” all costs of the selected sub-activities (Flag1 = Yes) into the activity in the higher level, we have to select “Rollup” buton and “Sum” from the drop-down menu.

Here is the final result:

The activity is not selected (Flag1 = No). Consecuently Cost1= 0 €.

Toggle to Yes.

Now, Flag1 = Yes and consecuently the Cost1 = Cost = 1.120,00 €.

(You will receive the cost value <> 0 only if you assigned resources and costs to the selected resources before.)

Enhanced by Zemanta

How to import data into SharePoint 2010 List

My problem was how to migrate some lists from SharePoint 2003 into SharePoint 2010. There is a simple and effecitve way how to do this. At first you have to export data to Excel file. The next step is import Excel data into SharePoint 2010 list. How to do this, you can find very well described in this post:

Import and export data between Excel 2010 and SharePoint 2010

There is one thing I will add to this post. What I liked with this procedure is the fact, that the import function automatically finds the right data types for columns (text, number). Only with the text columns it alway selects “more lines of text”.

Configuring SharePoint 2010 Search Server

How to configure SharePoint 2010 Search Server is very well documented by SharePoint George. Very useful aditional information you can find in the next posts: