How to: Out of box Approval Workflow issue and solution - Part 2

Hi all,

If you have noticed that SharePoint Approval workflow, you will get an issue. Please refer to my part 1 blog.

PROBLEM:
Apparently, when an item is attached to Approval workflow to control content approval for a user with contribute permissions, they can approve their own major version of an item through the approve button in the workflow.

There a no way to configure the workflow so the site members (contribute permission level) can not approve their own major versions of an item. Its really a MAJOR drawback of out of the box Approval Workflow. 

For e.g.  
Suppose we have a document library called as "Business Services". We attach an OOTB SharePoint "Approval" workflow to it. Permissions to this document library has 2 users:
i) vm\local1 (with contribute permissions)
ii) vm\approver (with approver permissions).

Testing for vm\local1: 


>> vm1\local1 logs in.
>> Goes in to "Business Services"
>> Uploads a document.
>> Check in the document, right click and choose "Publish a major version"
>>  Approval workflow kicks in. local1 enter the approver name and hit the "Start" button.
>> This document will be in "Pending" status and workflow status will be "In Progress".
>> a) Right click on the document, choose the "Approve/Reject" from the ECB. We will receive Error Access Denied. EXPECTED BEHAVIOR
     b)  Click on the "In Progress" link, Go to the task list, try to approve this document. Approval workflow will be completed and version 1.0 approval status "Approved". NOT EXPECTED BEHAVIOR


Workaround: 


In my previous blog, I had NOT touched the OOTB Approval workflow, but I changed TASK list's behavior. But in that blog, actually checking the permission for the TASK and not for the "Business Services". In this blog, I corrected the behavior. This time, I have verified permission for "vm\local1" on the "Business Services". 

My Task's event handler: 


public override void ItemUpdating(

SPItemEventProperties properties)
       {
           base.ItemUpdating(properties);

           this.DisableEventFiring();

           using (SPWeb currentTeamSite = properties.OpenWeb())
           {
               // Delcare variables/Objects
               bool isApprover = false;                            //Flag to check permission
               string currentUser = string.Empty;              //Get current user (For e.g. vm\local1)

               // Get the current item
               SPListItem currentItem = properties.ListItem;

               /* STEP1:

                   We need to get the current logged in the user. In our case, its vm\local1. Bear in mind that, we have hooked the "TASK" list as the event handler. through event properties we can extract the current logged in the user

               */

               // Get the value display name of the current logged in user and Created by Field of the current item
               currentUser = currentItem["Author"].ToString(); // For e.g. 21;#vm\local1
               SPUser user = currentTeamSite.AllUsers.GetByID(properties.CurrentUserId); // extract the ID. such as 21

               /* STEP2

                   THIS PART THE MOST IMPORTANT PART OF THE CODE. We get the currentUser. But we need to parent list which is "Business Services" and get the permission for the user on that document library. In the task list, we always have reference to the workflow.  Therefore, currentItem has workflowListId and through which we can get the parent list.

               */

                  // Get the Parent list (such as Business Services, Development etc. currentItem is from Task lists)
               SPList parentList = currentItem.ParentList.ParentWeb.Lists[new Guid(currentItem[SPBuiltInFieldId.WorkflowListId].ToString())];

          

               /* STEP3: 

                  Now, we have to get permission of current logged in user on the "Business Services".

               */
               // Get the permission of current user for the "parentList"
               foreach(SPRoleDefinition definition in parentList.AllRolesForCurrentUser)
               {
                   Console.WriteLine(definition.Name);
                   if( (definition.BasePermissions & SPBasePermissions.ApproveItems) != 0)
                   {
                       isApprover = true;
                       break;
                   }
               }        

                /* STEP4:
                    If the current logged in user has "Contribute" permissions, then we show an error message to the user.

                */

                // if user is NOT an approver show the error message
               if (!isApprover)
               {
                   properties.Cancel = true;
                   properties.ErrorMessage = "You dont have access to this operation because of your access rights.....";
               }
           }

           this.EnableEventFiring();
       }

 Cheers, 
--Aroh


Reference: 
Geek hubkey
Social MSDN SharePoint forum

How to: Converting List Item to SPUser object

Hi All, 

Many times we need to cast a list item to a SPUser object. It can easily done using these steps: 

Step1: Get the current item

SPListItem currentItem = properties.ListItem; 

Step2: Get the value display name of the current logged in user and Created by Field of the current item

currentUser = currentItem["Author"].ToString();

Step3: Get the user id as integer
int userid = Convert.ToInt32(currentUser.Substring(0, currentUser.IndexOf(";#")));

Step4: Convert SharePoint user which matches the ID
SPUser user = currentTeamSite.AllUsers.GetByID(userid);

There is one more case where we have number of users. In this scenario, 

we can loop the users: 

--------------------------------------------------------------------------------

// get a reference to the "Alerts" list as an instance
 SPList alerts = spWebRoot.Lists["Checklists Alerts"];
 

// get the query object for user alert
 SPQuery queryUserAlert = new SPQuery();
 

queryUserAlert.Query = "<Where>" +
      "<Eq>" +
               "<FieldRef Name='Team' />" +
               "<Value Type='Choice'>" +    teamName + "</Value>" +
       "</Eq>" +
 "</Where>";

 SPListItemCollection noOfUsers = alerts.GetItems(queryUserAlert);

//Get all the users in that team (such as Desktop Support - Daily)
foreach (SPListItem item in noOfUsers)
 {
        //Use "GetUser" function for List "item" into a SPUser ojbect 
        SPUser user = GetUser(item, item.Fields["User"]);

        // REMAINING LOGIC

} 

private SPUser GetUser(SPListItem item, SPField userField)
 {
     string currentValue = item[userField.Title].ToString();
     SPFieldUser field = (SPFieldUser)userField;
     SPFieldUserValue fieldValue = (SPFieldUserValue)field.GetFieldValue(currentValue);
      return fieldValue.User;

  }

Happy programming, 

Cheers

--Aroh 

Bug on SharePoint Server 2007 SP2 and Hotfix

Hi All, 

PROBLEM:

If we try to update the SharePoint 2007 service pack 2, you may encounter an error: 

"Trial Period Expired"

Apparently, we are using Project Server 2007 and few months back we installed SharePoint 2007 SP2. a Product expiration date is improperly activated. Project server expiry is for 180 days and when you install SP2, after 180 days, you will get this error message.

RESOLUTION: 

Microsoft rushed with hotfix. The good news is that customer's data, configuration, and application code is not affected. These are the steps to apply the hotfix

Step1: Download the hotfix.

Step2: Install the hotfix and choose the default settings. 

Step3: Reboot the system. 

And you are done!!. There is no need to apply the Product ID number (PID) on the Convert License Type page in Central Administration.

Cheers, 

--Aroh

SharePoint 2007 content database and its maximum size

Hi all,

The SharePoint product and technology recommends 100 GB of content database per site collection as the best practice for WSS v3 and MOSS 2007 deployments. In one of SharePoint conference Joel Oleson recommended 150 GB for a content database per site collection

Q. What happens if our content database is more than 100 GB or 150 GB?
A. Well, SharePoint 2007 and Content Database are built on SQL server & has tight cohesion with SQL tables. If content database goes beyond 150 GB, this is likely to happen:

• SharePoint 2007 database team chose an approach where they use few tables and not using many small tables. Now, using this approach the whole SharePoint Database deisgn is prone to table-locks if content database exceeds 100-150 GB limit. Its to be noted that its NOT A LIMIT but its a recommendation.
  
  Moreover, SharePoint 2007 database schema has large tables (Lists, Site-Collections with huge tables) and the performance were affected by SQL Server locks. Microsoft also recommend 2000+ items for the lists
  
  This is commonly referred to as the “100GB content database size limitation".
  
  NOTE: in SharePoint 2010, 100 GB recommendation for the current database is not a problem any longer. Its due to the major architecture changes are done to the database. Please refer this article to get more information about SharePoint 2010 database changes. This time they have moved the content from single table to several tables. Hence, we can store million of items in the lists and can a huge collaborative project in just one site collection.
  
  
• May hit the Performance SharePoint-sites.
  
  For an instance, we have a site-collection which contains 300 sub-sites. If the content database exceeds 150 GB or more, and users are doing some kind of insertion/update etc then that particular site collection table will be locked and subsequently other sub-sites.
  

If have a very large collaboration SharePoint (like 350 GB), its always to split into different site collections. Each site collection has its own content database. Therefore, in this particular case we can split 2 site collections with each site collection with its separate content database.

Microsoft has a tool for splitting site collection and move across content database: "SharePoint Administration Toolkit".

I have read some blogs where people have deployed their productions server with content database around 350 GB and growing (VSS-based backup solution in place).

NOTE:
Also, I have seen blogs where some people suggest never touch SharePoint database and they recommend to use SharePoint API (Object Model) to do any kind of updates. Its quite a valid argument but at times we need to modify the SharePoint database if a new solution or some feature get messsed up but developer MUST NOT delete any row or table if he/she does not know the usage of that table.
For an instance, look my blog, where I had developed a timer job solution and forgot to deactivate the feature, changed the scope of feature directly and upgraded the solution. My entire SharePoint environment got screwed up.
Apparently, SharePoint UI cant help . I have to modify the Database (SharePoint Configuration database ).

In fact, Sql server can go to terabyte level, but SharePoint 2007 database schema has limitations. Practically speaking, the recommendation is based primarily on two significant factors:

1. 
   Service Level Agreement (SLA) requirements for a given organization may dictate that backup operations for the SharePoint databases must be executable in a limited amount of time. The size of the content databases will have a direct impact on how long it takes to execute that backup.
   
   
2. 
   The storage subsystem must be robust enough to handle the disk I/O requirements of the SharePoint solution that it serves.
   
   

There is useful article by Joel Oleson about How large for a single SharePoint content database?

Cheers,
--aaroh

References:
1) Inspecting SharePoint Database
2) How big is SharePoint Database
3) Split large collaboration site
4) Understanding SharePoint database
5) Limitation of ECM solutions

SharePoint 2010 Beta is now available!!

Hi all,

SharePoint 2010 beta is available now!
Get it here.

Have fun ,,

cheers,
--aaroh

New SharePoint 2010 videos

Hi all,

A new series of SharePoint 2010 developers videos are available on Channel 9.

Here is the link.

These videos will talk about:

• Getting started with SharePoint 2010
• SharePoint 2010 developer road map
• VS 2010 with SharePoint 2010 tools
• UI enhancements
• Lists and schema
• Client object model
• Workflow
• ECM
• Sandboxed solutions
• SharePoint 2010 Security

Cheers

--aaroh

How to: Creating Infopath forms as content types and publish forms in SharePoint

Hi all,

InfoPath 2007 (electronic forms) and SharePoint 2007 fit together very well. However, developing an effective electronic forms solution has been quite challenging.

Issues with InfoPath 2003:

a) Difficult to develop data-driven solutions as the data was not sync with the data in the form.
b) Not easily published without changing the original form designer.

InfoPath 2007 overcame these issues:

a) Form templates can now be attached to content types and thereby we have power to redeploy the templates.
b) Form data fields are bidirectional and therefore developers can promote selected fields and have those fields presented as form library that end users can edit.

In this post, I will talk about a site collection, and few site.

Site-collection: http://comanyname/asia
Under this site collection I have to 2 sub-sites which are on the same level:

Site1: http://comanyname/asia/it
Site2: http://comanyname/asia/tools

Under site1, I also have a sub-site called as it/ITDocCenter where all IT documents are stored as templates (InfoPath templates).

http://comanyname/asia/it/ITDocCenter (sub-site of Site1)

I also have a Site2 (tools), where all IT department people will be filling out the InfoPath forms and these forms are based on templates which are stored in ITDocCenter.

Therefore, I needed content types in "tools" and refer these templates on "tools" sub-site.
I will step-by-step procedures:


1. We first design on InfoPath 2007 templates on local computer. Right click on InfoPath template and open the InfoPath template. On the toolbar, go to Tools >> Submit Options













2. On the "Submit Options" prompt,
>>Check the "Allow users to submit this form"
>>Select the "Send from data to a single destination"
>>From the drop down choose "SharePoint document library"
>> Click on "Add" button.

What we are doing here is that we are selecting a document library (System Checklists InfoPath) which will be on the "tools" sub-site. So, anyone you want to fill out an InfoPath form will be submitting that form to the "System Checklists InfoPath" document library.

















3. On next screen, we define the full URL of that document library. (System Checklists InfoPath).

For the file name, I have used a formula which as some predefined conditions with today's date.














4. Final screen will give a summary of what we have selected. Click on OK button.


















5. Go to "Design Tasks" >> "Design Checker" >> and check the "Design a form template that can be opened in a browser or InfoPath". Save the template now.

















6. Go to "Design tasks" >> "Publish Form Template" >> Choose the first option.














In next screen, we have to enter the SharePoint site and in this case it will be http://comanyname/asia/tools














7. On this screen we will create a new content type. Select the "Create a new content type" and click "Next" button.















8. Type in the name of the content type.















9. This screen prompt us about where we want locate and save the form template. Clearly, we want store all our templates on the ITDocCenter site. In this case, I have stored into "Operations" document library.

http://vm1f2909/sites/asia/it/TeamSites/RegInfra/Forms/Backup.xsn
















10. Promote the selected fields. In my case, I chose "Time Period" and "Templates".



















11. Next screen, will give is the summary and success of the content types.















12. Now, we go to the Tools site and go to "System Checklists InfoPath" document library.
Settings >> Form Settings >> Advanced Settings >> Allow content types.







13. Settings >> Content types >> Add from existing site content types >> Select with content types from >> Microsoft Office InfoPath >> Choose "Backup"













14. In the "System Checklists InfoPath" document library, we can see "Backup" as a new content type.














15. We fill-in the form









16. When we are done we submit the data to "System Checklists InfoPath" document library.





17. We can see a new record in "System Checklist InfoPath" document library. So, Tools >> "System Checklists InfoPath" everything is working fine. Lets check asia/it/ITDocCenter.




18. Surprisingly, there is no template on this document library. Reason: We have chosen "Document Library(Word Documents)" (Template ID : 101) where as "XML Form Library (InfoPath) (Template ID: 115). But we have a workaround.






19. We upload "Backup.xsn" from local computer to the SharePoint Server.






20. Now, we can see the a new record on the "Regional Infrastructure" document library. Now, if site administrator want to modify "Backup.xsn" file which is there in "Regional Infrastructure" document library, he could edit the InfoPath form >> Tools >> Design the form.












21. Add new questions and save it.












22. He has to republish the form template, promote the selected columns.



















23. Finally, modifed InfoPath template has been changed.










24. Now, if we go to "Tools" subsite >> go to "System Checklists InfoPath" >> New >> Backup, then we can see the new questions in InfoPath form.








Cheers,
--aaroh

Provisioning failed: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)".

Hi all,

While working on the Shared Services Providers (SSPs), I got an error as follows:
Provisioning failed: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)".

After a while I checked SharePoint's Central Administration and SharePoint Service account (such as systemadmin). Then checked the SharePoint Services (Services.msc) and found that
"Windows SharePoint Services Search" and "Windows SharePoint Services Timer" are using "Local System"

I followed these steps then:

>> Start >> run >> services.msc >> locate the “Windows SharePoint Services Search” and “Windows SharePoint Services Timer”. It should be log on as “Local System”. Right click on the “Windows SharePoint Services Search” >> Properties >> choose “This Account” and will in the SharePoint Service Account. Username: systemadmin, Password: test123.

Do the same steps with “Windows SharePoint Services Timer”.
It will fix the issue.

Cheers,
--aaroh

How to: Converting Word files into Infopath, manipulate InfoPath forms and publish onto SharePoint

Hi all,

In this post, I will talk about converting word files into InfoPath forms, we will manipulate InfoPath forms with some kind of simple logic, and finally publish this forms onto SharePoint.

Lets get started:

1) Open InfoPath 2007 >> Choose the "Design the form template" >>
Select Blank >> Click OK














2) Go to File >> Import form as follows:



















3) Choose word document.












4) The import wizard will convert word document to the InfoPath form.













5) Now, we are done with conversion. We need some kind of logic for the InfoPath form. For my case, I need to have a some questions to be displayed on regular intervals such as Daily, Weekly, Monthly, Quarterly and Yearly. I also have to the template name for a InfoPath forms.

Go to Design Tasks >> Controls >> Drag Drop "Drop-Down List Box" on the form. Then add the list box entries manually.




















5.1) Click on "Rules" button on the drop down list.



















5.2) Click on Add Action Button. Under the Action drop down choose >> "Submit using a data connection"











5.3) It will open a wizard. Choose. "Create a new connection to" >> "Submit data"







5.4) Then Select a destination for submitting your data >> Choose "To the hosting environment, such as an ASP.NET page or a hosting application "









5.5) Choose the default settings and click OK.







5.6) We drag one more control and this time it will be a textbox control.
>> Label will be: Template Category.
Right click on the textbox properties.
>> Field name: will be "templateCat" or simply "template"
>> Value: Backup
>> Under Display tab, make it "Read only".


















5.7) Click on Rules button >> Add >> Add Action. The Data Connection is already set.
Just Click OK. We have defined the rule on the second text box control.



















6) We have 5 tables for each period namely: Daily, Weekly, Monthly, Quarterly, Yearly.
Under Controls, drag-drop 5 sections and copy each table (Daily, Weekly, Monthly etc. tables) to corresponding tables as follows:



















7) Right click on Section (for Daily), Select Conditional formatting, timePeriod is shown up, choose "is not equal to", Select text and type in Daily.
Check the "Hide this control".

Do the same steps for other sections.









8) On Design Task >> Select the "Design Checker" >> Click on "Change Compatibilty Settings" >> Under "Browser compatibility" check the first option "Design a form template that can be opened in a browser or InfoPath" .

9) Save the InfoPath Form and Preview this form. Check if drop down is working for different time periods.








10) On Design Task >> Click on "Publish the InfoPath form". Choose the first option.
"To a SharePoint server with or without InfoPath Forms Services"
















11) We need to enter this Site URL where we have to publish the InfoPath forms.
















12) Create a Site Content Type.






























13) Browse >> Choose the Library where we can to publish InfoPath forms and type in the name of the InfoPath form.













14) On this screen we can promote the columns and these columns (metadata) will be visible on the SharePoint site columns.













Add >> Time Period.














We will add one more column "Template".


15) Next screen will verify if form information is correct. If everything is correct, we just Publish the form and close it.













16) Form library "System Checklists InfoPath", we enable allow management of the content types >> Add existing content types >> "Storage" content type.













17) For the "System Checklists InfoPath" form libray, We can see a New content type >> Storage.













18) When we fill out the "Storage", a drop down list will prompt the user to select a time period. For an instance we chose Monthly and Save the form.


















20) When the from is Saved, the list will show the monthly period as shown:





Cheers,
--aaroh