Have been hearing a lot from users they wanted to have a custom unique ID for project, though project name itself is unique and the Project GUID, but for most cases Project GUID doesn’t makes sense & name sometimes isn’t that effective, in my case it all goes back to old 2003 project server system where in there were 2 disparate systems being maintained one for creation & selection of project (custom sharepoint based solution) and once the project was selected for execution it was pushed to project server for execution & monitoring, now to have a correlation between both the systems we used an auto generated numeric identifier, which has been base for couple of reports too and these numeric codes were carried over in 2007 system when we migrated, with 2010 system we consolidated the two systems and now everything is being managed into one system but however end users were reluctant to give away the unique numbering system 🙂 hence we had to devise a way to auto generate these numbers, i know which can be easily done by customizing and having a web part, but our problem was a little more complex, end users wanted this number to be generated on the very first initial (New Project ) page and should be saved and to be made available in project center as soon as a project is saved and created, ok so that was the background now coming to the solution 🙂
Couple of things to note we made a decision to have these unique ID entries in a custom SQL table, reason being we are using it for other purposes, you might want to tweak it as per your requirement
1. Create custom webpart, place it on the new project PDP page, Custom webpart on load searches through the custom table and allocates an unused unique ID to the project which is shown on the PDP page, the webpart itself renders the ID, it also checks if there is already a unique ID assigned, if yes just display and donot set, if not available set the value, this way this web part can be used in multiple places on different pages throughout the workflow
2. Have a custom field created for Unique ID, add it to the PDP page in hidden mode, as soon as the custom webpart is loaded it writes the value in custom field using javascript 🙂
Note:: Always place custom web part below the unique ID custom field web part
Now once the Unique id has been set in the UniqueID custom field, you can insert corresponding Project UID anywhere in the workflow in the custom table against the unique ID generated, to correlate with other LOB system, or you can just leave it if not required, since you already have this value in the project level custom field J
now getting to the actual code, should be simple enough 🙂 first couple of snapshots, before i proceed to the code 🙂
1. Create a table
/****** Object: Table [dbo].[tbl_ProjectUniqueID] *****/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tbl_ProjectUniqueID]( [UniqueID] [bigint] IDENTITY(2000,1) NOT NULL, [ProjectUID] [uniqueidentifier] NULL, CONSTRAINT [PK_tbl_ProjectUniqueID] PRIMARY KEY CLUSTERED ( [UniqueID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
2. Create Visual web part project in visual studio and use the code 🙂
Java script function to copy unique ID generated in Project Custom field, place this in control.ascx file 🙂
<script type="text/javascript"> function SetUniqueID(ProjectUniqueCFGUID, UniqueID) { try { var arr = document.getElementsByTagName('input'); for (var i = 0; i < arr.length; i++) { if (arr[i].type == 'text' && arr[i].GUID == ProjectUniqueCFGUID) { arr[i].value = UniqueID; break; } } WPDPParts[0].IsDirty = true; } catch (ex) { } } </script>
/////////////////////CODE BEHIND FOR VISUAL WEBPART/////////////////////
using System; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using Microsoft.Practices.EnterpriseLibrary.Data.Sql; using System.Data.Common; using Microsoft.SharePoint; namespace CreateUniqueID { public partial class CreateUniqueIDUserControl : UserControl { public CreateUniqueID CreateUniqueIDWebPart { get; set; } protected override void OnPreRender(EventArgs e) { base.OnPreRender(e); if (this.CreateUniqueIDWebPart != null) { if (this.CreateUniqueIDWebPart.isMandatory == true) this.lblFieldName.Text = this.CreateUniqueIDWebPart.FieldName + "<span class="ms-alerttext">* </span>"; else this.lblFieldName.Text = this.CreateUniqueIDWebPart.FieldName; this.lblFieldDesc.Text = this.CreateUniqueIDWebPart.FieldDesc; } } protected void Page_Load(object sender, EventArgs e) { if (Request.QueryString["ProjUid"] == null) { if (!Page.IsPostBack) { int UniqueID = GenerateUniqueID(); if (UniqueID > 0) { string UniqueIDGuid = “xxx-xxxxx-xxxxxxx-xxxx”; lblUniqueID.Text = onboadID.ToString(); ScriptManager.RegisterStartupScript(this, GetType(), "initializeVar", " SetUniqueID('" + UniqueIDGuid + "' , '" + UniqueID.ToString() + "');", true); } } } } private int GenerateUniqueID() { try { String Connect = “Enter connection string here”; SqlDatabase dbepm = new SqlDatabase(Connect); DbCommand cmdSubmit; cmdSubmit = dbepm.GetStoredProcCommand("usp_CreateProjectUniqueId"); // You can use sql command / text here to execute int Id = Convert.ToInt32(dbepm.ExecuteScalar(cmdSubmit)); return Id; } catch (Exception ex) { return 0; } } } }
Hi epmXperts,
Will this Code work in an environment where SSL is being used ??
Yes absolutely it will, it has been tested in SSL environment
Hi epmXpert,
I was working on the similar requirement, and your blog is useful. It would be great if you can provide more information on the same.
Thanks,
Shivraj
Hi Shivraj
Let us know, how can we help with further information
Hi epmXperts,
I have created the table with the above SQL script, and I was trying to create a visual web part, I am getting an issue “CreateUniqueID is a namespace and is used like type”. If you can help me with more information about how to create the visual web part and use it in PDP page in project server that would be of great help.
Thanks,
Shivraj
Hi Shivraj
You don’t have to use the same namespace, create a new Visual Webpart project in visual studio, name it whatever you want, and just override the pre render event and use the code from post within the pre render segment and you should be fine
Hi epmXperts,
Can I have contact details,so that I can touh base with you and discuss about the requirements and other details also.
Thanks,
We are working on a similar requirement. Can you please tell me where is this table created? is it within projectserver database?
Yes its created within the reporting DB
[…] is with reference to my earlier post wherein i had explained on how you can have a custom webpart which would in turn generate unique ID […]
Hi epmXpert,
Can you please point us to the location of control.ascx file !!
Thanks
Sorry for the late reply but Control.ascx doesn’t has anything much than label and a text field, do you still want that
epmXperts,
Thank you the write up. This is great. I have question though. We have added the custom field after 3 months we have had few projects already created in the system. With my limited knowledge on C#, Is this script going to work only for the newly created projects? If i open the project in edit mode can i make this code work for existing projects?
I built this a long back so exactly don’t remember but i think it will work if you put this web part on a non-new Project PDP page
Let me know how it goes, else i can help you out figure a way to get this done, it should be relatively easy to fix
OK…i will let you know how it goes..in couple of days. Thank You.
thanks for your post. Good insight. can someone confirm if this worked for existing projects? For new project this might do well, but I would to know for existing projects, do we need to open all the projects and do check-out, check-in to get it worked? thank you – Srinivasan Venkatesan
Hi Srinivasan
For existing projects it will not work, as i have a logic built in code where in it checks, if ID already exists donot generate, so in this case alternate will be to empty all projects with existing ID and then reopening the page will work
Let us know, how it goes