Friday, 18 December, 2009

How to auto-generate a custom sequential number list field?

I recently came across a requirement where in customer wanted to auto generate a custom sequential number with current date as a part of this sequential number on a SharePoint list.


Although SharePoint has an inbuilt “ID” field and Issue Tracker list can Issue ID. But they simply just provide running numbers like 1, 2, 3, 4.What if you need some prefix and also a choice from where the sequential numbers should start.

You can achieve this through different approaches, like using an event handler etc., but I think the following is the simplest of all.

Here is the step by step instructions on how to get this done.

There are two parts for this solution.

Part 1: Getting the desired sequential number

1. First of all we need to create a list which will help us in generating the numbers, let us call this list as “Unique Numbers”

2. Add one number field, "LastNumber" and one calculated field "NextNumber" to this list. The formula to be used for NextNumber is "[LastNumber]+1"(Note: The data type returned from this formula should be number)

3. Let us add a new Item to this list with the following information

Title : ORDERNO


LastNumber : 99

4. Now we need to create the second list I will call it “Orders”

5. Add a number field “OrderNo” to this new list, now we are ready to build the workflow to generate unique sequential numbers

6. Open your web site in SharePoint Designer (SPD) .Create a new workflow. In the File menu, select New->Workflow...

7. In the dialog that appears, give the workflow a name, e.g. "Assign Order Number". In the drop-down list below, choose to connect the workflow to the list you want to assign auto sequential numbers to (In this case, this would be a list called Orders). Then check the middle option of the three check boxes below, "Start this workflow automatically when a new item is created".

8. On the same dialog, there is a button at the bottom called "Variables". Click it. Choose "Add", name it "mNumber" and make it of type "Number". Click OK, and OK again. Now, click "Next" in the dialog.

9. You can leave the step name as is ("Step 1"). Leave the "Condition" empty, and click the "Actions" button. In the menu that appears, select "Set workflow variable". This will add an action to the list to the right of the Actions button. Now you need to set up this action. Click the link "Workflow variable", this will open a small dropdown list. Select "Variable: mNumber". Then click the next link, "value". This will display a textbox and a small button in place of the link. Click the button ("Show databindings"). This will bring up a new dialog, where you will select the value to assign to the "mNumber" variable. In the "Data Source" drop down, select "Unique Numbers". In the drop down just below ("Field:") select "NextNumber". Now, the dialog will be expanded with a "Find list object" section. In the drop down "Field", select "Unique Numbers:Title", and in the value box below, type ORDERNO. Click OK. SPD will now give a warning that the look up might return more than one value, just select "Yes" to continue here.

10. Now you add one more action. Click the "Actions" button and select "Set field in current object". Click the "field" link in the new action, and in the list that appears, select the field in your list that is the target of the autonumber (e.g. "OrderNo"). Then click the "value" link, and once again click the little button that appears ("Show databindings"). This time, in the "Data Source" drop down, select "Workflow data", and then in the next drop down, select the variable "mNumber". Click OK.

11. Add yet another action, this time choose the "Update list item" action. Click the link in the new action, "update list object in this list". This brings up another dialog, in the "List" drop down, select "Unique Numbers". Click the "Add" button just below to the right, and in the new dialog's first drop down ("Set this field:"), select "LastNumber" and to the right of the "To this value:" text box, click the formula button. Again, a new dialog appears, in the "Data Source" dropdown, select "Workflow data" and then in the "Field" drop down, select "Variable: mNumber". Click OK twice to get back to the "Update list item" dialog. Now, once again in the "Find list object" section, select "Unique Numbers:Title", and type in the value ORDERNO. SPD will give a warning again, click "Yes".

12. That's it. Click the "Finish" button, and if all is well, the workflow will be created.

13. Now when you create a new item in the Customers list you will see that a unique sequential “OrderNo” is generated

Now we have achieved our first goal of generating a desired sequential number. Now on to Part 2

Part 2: Adding a prefix to the sequential number, in this case current date in YYYYMMDD format

1. Create a field “Today’s Date” this should be of “Date and Time” type and default value should be today’s date.

2. Create another field “OrderID” of calculated type and use this formula

=TEXT(Today’s Date,"yyyymmdd")&OrderNo

Now when you create a new item on Orders list you will see that a new OrderID is generated .

Note: The field names mentioned here for example purpose, you can use any name with the same logic to make it work.

Wednesday, 4 November, 2009

Top 10 Benefits of MOSS

1) Provide a simple, familiar, and consistent user experience.

MOSS 2007 is tightly integrated with familiar client desktop applications, e-mail, and Web browsers to provide a consistent user experience that simplifies how people interact with content, processes, and business data. This tight integration, coupled with robust out-of-the-box functionality, helps you employ services themselves and facilitates product adoption.

2) Boost employee productivity by simplifying everyday business activities.

Take advantage of out-of-the-box workflows for initiating, tracking, and reporting common business activities such as document review and approval, issue tracking, and signature collection. You can complete these activities without any coding. Tight integration with familiar client applications, e-mail, and Web browsers provide you with a simple, consistent experience. Modifying and extending these out-ofthe-box workflow processes is easy.

3) Help meet regulatory requirements through comprehensive control over content.

By specifying security settings, storage policies, auditing policies, and expiration actions for business records in accordance with compliance regulations, you can help ensure your sensitive business information can be controlled and managed effectively. Tight integration of Office SharePoint Server 2007 with familiar desktop applications means that policy settings are rendered onto client applications in the Microsoft Office system, making it simpler for employees to be aware of and comply with regulatory requirements.

4) Effectively manage and repurpose content to gain increased business value.

Business users and content authors can create and submit content for approval and scheduled deployment to intranet or Internet sites. Managing multilingual content is simplified through new document library templates that are specifically designed to maintain a relationship between the original version and different translations of a document.

5) Simplify organization wide access to both structured and unstructured information across disparate systems.

Give your users access to business data found in common line-of-business (LOB) systems like SAP and Siebel through Office SharePoint Server 2007. Users can also create personalized views and interactions with LOB systems through a browser by dragging configurable back-end connections. Enterprisewide repositories help your organizations store and organize business documents in one central location.

6) Connect people with information and expertise.

Enterprise search in Office SharePoint Server 2007 incorporates business data along with information about documents, people, and Web pages to produce comprehensive, relevant results. Features like duplicate collapsing, spelling correction, and alerts improve the relevance of the results, so you can easily find what you need.

7) Accelerate shared business processes across organizational boundaries.

Without coding any custom applications, you can use smart, electronic forms–driven solutions to collect critical business information from customers, partners, and suppliers through a Web browser. Built-in data validation rules help you gather accurate and consistent data that can be directly integrated into back-end systems to avoid redundancy and errors that result from manual data re-entry.

8) Share business data without divulging sensitive information.

Give your employees access to real-time, interactive Microsoft Office Excel® spreadsheets from a Web browser through Excel Services running on Office SharePoint Server 2007. Use these spreadsheets to maintain and efficiently share one central and up-to-date version while helping to protect any proprietary information embedded in the documents (such as financial models).

9) Enable people to make better-informed decisions by presenting business-critical information in one central location.

Office SharePoint Server 2007 makes it easy to create live, interactive business intelligence (BI) portals that assemble and display information from disparate sources, using integrated BI capabilities such as dashboards, Web Parts, scorecards, key performance indicators, and business data connectivity technologies. Centralized Report Center sites give users a single place for locating information.

10) Provide a single, integrated platform to manage intranet, extranet, and Internet applications across the enterprise.

Office SharePoint Server 2007 is built on an open, scalable architecture, with support for Web services and interoperability standards including XML and Simple Object Access Protocol (SOAP). The server has rich application programming interfaces (APIs) and event handlers for lists and documents. These features provide integration with existing systems and the flexibility to incorporate non-Microsoft IT investments.

Wednesday, 21 October, 2009

40 feature areas that Microsoft is highlighting with SharePoint 2010

Missed SharePoint Conference 2009, here is another opportunity

To all SharePoint Pro's out there if you wanted to be part of SharePoint conference 2009 but could not here is another opportunity just register yourself at SharePoint 2010 Virtual Conference & Expo. The only thing you will miss is traveling to Las Vegas