Creating external lists in SharePoint 2010 – Part 1

Now with SharePoint 2010 it is possible to create a list from other external sources with the help of external content type and external lists.

You can connect with any external source and can create a list from that. In this example, I am going to show you how to create a list from the SQL server database.

The advantage is you not only can view, but also insert, update and delete as well using external content types.

You can create external lists from designer as well as browser. In this part we will see how to create it through the browser and then in next part we will see how to create with the help of designer.

But even to connect with a list from the external SQL server database, you must first create external content type from SharePoint Designer 2010.

So open the SPD2010 and click external content type and then create.



Click on the content type and see the screen that you get



You will see name, display name, namespace, version, office item type, and office sync. Office related options are useful when your external list resembles the fields of outlook options. Like if you have contacts external list which has fields similar to the contacts outlook option; or calendar options, then you can select these options. For us we are going to use generic list. Because our list is different and does not resembles any structure with outlook options.

Click on the click here to discover the external data sources option.




Now click on Add connection.



Select type SQL Server and specify database server and name.






Once you are done with this, it will discover the tables. As of now I only have one table and hence It is showing only one.



Now right click the table and select create all operations. Which will create insert, update, and delete operations.



As you can see following wizard comes up which shows you the operations being performed.



Have your at least one field set as map to identifier.



Click on next to set filter options. As you can see because external list table in our case may contains so many records and it is not advisable to return all records in one time and hence you can see by default warning message saying that you should at least mention limit as a filter parameters.

Add filter by clicking on add filter.




Click on Ok and then finish. Now we have our external content type set up.




Click on save button to save the content type to Meta store.

Now go to your SharePoint site and create external list.





Click on create, so the list will be create. Now check that you might get this message.



Open central administration.

Application Management -> Manage Service Applications -> Business Data Connectivity Service



Now select content type and link on set metadata store permissions. Following screen appears.



Now there is a possibility that you get blank section in the permissions for all Users (windows). Means you may not see any permissions listed in that block. Yes, and this is what I got. Well I also found a solution and the solution is to use PowerShell script.

PowerShell is a scripting language which is used to automate some tasks. It is same as how we write commands on the command prompt. But PowerShell has more power as name suggests.

Open run command prompt and type in PowerShell.exe and you get the PowerShell Window.



As you can see it starts with PS and it stands for PowerShell of course.

Now run these commands one after the other

$adminURL="HKLM:\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\WSS\"
$key = Get-Item $adminURL
$adminurl = $key.GetValue("CentralAdministrationURL")
Write-Host "Central admin site "+ $adminurl
Add-pssnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

Write-Host Updating BCS persmissions
$obj = Get-SPBusinessDataCatalogMetadataObject -BdcObjectType Catalog -ServiceContext $adminurl
$right = (1+2+0x10+4)
$claim = New-SPClaimsPrincipal -Identity 'c:0!.s|windows' -IdentityType EncodedClaim
Grant-SPBusinessDataCatalogMetadataObject -Identity $obj -Principal $claim -Right $right
Copy-SPBusinessDataCatalogAclToChildren -MetadataObject $obj
Write-Host Complete

We would like to thank Jon for suggesting and coming up with this solution.

Once you perform the above step, you get the permissions in the metadata permission property box. Now assign permission to appropriate users and click on OK.

Now open the site and open the Employees list. Ok, surprise, one more error and this time it is following.



This error comes because when we create external content type in SharePoint designer 2010, it takes the user’s identity as authentication mode.

So let’s change the authentication mode from User’s identity to BDC Identity.

Open SPD2010 and open the external employee content type we created.



And now, we get this



Wow, another error. It seems like we are facing so many problems. Well, after resolving problem and get the solution is indeed is a pleasure.

So let’s put a step forward to get a solution.

Now this time open Windows PowerShell modules by going to Start-Administrative Tools-Windows PowerShell Modules and use this script

$bdc = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"};
$bdc.RevertToSelfAllowed = $true;
$bdc.Update();

Now go to Employees external content type, open it and change it from User’s identity to BDC Identity and save it, this time it should not give you an error.

This should resolve this issue. As you can see we have set RevertToSelfAllowed to true through console. Because this property is by default set to false and it is disable in SPD2010. So we need to set it through the PowerShell.

And there we have it now. Open your site and open Employees List.



Click on New, enter the details



See, now we not only have the data in the list but also in SQL Server database.



But yes, one very important thing to note here is that external lists neither participate in any event handlers nor participate in workflows. So if you see its settings page, you will not see any workflow options.



More to come. Stay tuned.

Inline editing of list items in SharePoint 2010

This feature is some of the very much awaited features amongst all new features in SharePoint 2010. People like to edit the item without opening that item in window. Just like ASP.NET grid provides the way of edit and then update by clicking edit button and then updating the record.

Well, now this was possible in MOSS 2007 as well, but it required little trick, but with SharePoint 2010, it is easy to do that.

Just go to the view settings of any list and then enable the in line editing option.



once you set this, you can see edit button for each list item and then clicking edit button will change the item in to editable mode, after change you can save or cancel it. just like how we do normally.



You can also add item without opening a NewForm.aspx. Click on + sign below last list item, which will allow you to add item.



I am sure this simple yet very useful feature will be used by many. Isn't it?

Result is out

Finally the result is out.

It looks like MS still has to work more to retain his market share for IE. Chrome is a big challenge for them despite of many changes in IE9.

Determine External List in SP2010

There is a new concept in SharePoint 2010 which is called external list. you can fetch data from external entities and LOB. we soon are going to see in detail how we can get the data from external entities.

This is the simple method to determine if the list is external list or regular internal list of SharePoint. this may be important at some point because external list does not participate in workflows and event handlers. we cannot attach handlers on external list.

list.HasExternalDataSource is the property which tells us that the list is external source or not. (where list is a list object of which you want to check)

How to create Quick steps – Part 2

In this post, we are going to see how we can have a quick step in the ribbon tab group.

If you have not read first part, then i recommend you reading part 1 first.

We will add two quick steps in ribbon group; One for Google and one for Bing.

So open SPD2010 and connect with the site. Now as you can see that we can create multiple quick steps in SPD2010. The first one is List item menu which is ECB which we created in part 1. And in this post we will cover New and View Ribbon. Rest you can check on your own.



So click on New Form Ribbon. But before that note that now from SPD2010, you can create quick step for calendar as well. We have mentioned in previous post that it is not available from the User Interface from SharePoint 2010. So go ahead and select Calendar in the Lists pane and then click on New Form. So we are now creating quick step for New Form of calendar

Give it a new and description and specify 16 by 16 and 32 by 32 icon locations.



And now click on Calendar and click on New. There we have the Bing logo. Click on it and will take you to Bing search.



Changing the ribbon location changes the position and group of icon.



Here we have changed from Actions to Commit in the Tab.Group.ID and hence the output



Ideally this operation should be kept in the Actions group, as we are performing some actions not doing anything else. The aim is to place icon on respective operations group.

Do the same operation for Display form and this time add two custom actions; one for Bing and other for Google.

These are the sequence numbers





Changing the sequence number will also change the place of icon.

How to create Quick steps – Part 1

Quick steps are one click handlers that performs some operations. Now with SharePoint 2010 and SharePoint designer 2010 it is possible to define quick steps easily.

In this post, we are going to see how we can add new ECB options for list items and also see how to add button in existing ribbon tab. These both options are now possible with SharePoint Designer 2010.

Creating ECB menu option in MOSS 2007 requires creating feature targeting edit control block with CustomActions and deploying it on server.

However there is one limitation for adding control in existing ribbon tab. You cannot have JavaScript function, showing message, disabling controls etc. when creating quick step using Designer 2010. These all can be done via in feature. We will have a detailed post in this topic going forward which is a vast topic.

Open your existing site, open let’s say team discussion. As you can see New Quick Step option is available in customize list group.



If you open Tasks list, then



However there is one strange behavior, if you open calendar, you will not see this option. Not sure why? Well, have to look into this. If you know the answer, do post a comment and we will appreciate it.

Let’s go ahead and create one ECB menu option for lists. Open SharePoint Designer 2010. Connect with the site. If you notice, SPD2010 is way different from SPD2007. There are so many differences. SPD2010 looks very clean and clear. It gives very user friendly interface than SPD2007.

Click on Lists and Libraries and choose the list on which you want to create ECB menu item. I have chosen Tasks list.

Double click on the Tasks List to make it open on right side panel. Now check out the far end right button, and you will notice custom actions below workflows. We can create workflows and custom actions from here.



Click on New. Give the name and description. Here you can choose if you have your own page, you can send user to this page with parameters. If you want to initiate any workflow you can do that from here. Or you can also send user to any other url that you wish.

Just to demonstrate i am using Google image and redirecting user to google.com from ECB menu. So mention that URL, use 16 by 16 image of Google, which will be shown to user in the menu item. And finally click on OK. And you are almost done. Remember this image should be in the IMAGES folder under 14 hive. (New structure, not 12 hive like in MOSS 2007)




Open the site, go to Tasks list and create any item and see what you have in the menu now. New option Google. Click on it which will take you to google.com



If you want to edit or remove this, you can see options on left top side of SPD2010.

Content Organizer – Drop off library

Hi

A new cool feature that has been added to SharePoint 2010 is content organizer. It actually helps to have a similar method like what we had in MOSS 2007 as record center.

Content organizer allows administrator to define certain rules for the documents to move from one library to another library. This actually helps to redirect specific business documents at certain locations. Total number of documents to be uploaded in library and when you created automated folder etc. are handled by content organizer feature itself.

To use this functionality we first need to activate feature called content organizer and this feature is in the site actions, manage site features.



Once you activate this feature, you get two more options under the site administration which is content organizer settings and content organizer rules.




Now before diving into this feature, let us understand what this feature can do more. This feature only works with content types. So you cannot use this feature to manage any list items, it works only for the documents.

Another thing to note here is that along with two new features, it also adds one more document library called “Drop off library” where user can drop their documents and then routing rules will route those documents based on the rule being set.

Content Organizer Settings is where you configure whether to route documents that are added to the site collection based on rules. Any changes made here affect routing for the entire site. Content Organizer Rules is where you go to create the rules that are used when routing documents. Any rules created here are used to route individual documents based on metadata attributes that you choose.

Now click on content organizer settings and let’s explore its options. The very first option is redirect user to the drop off library.



This means if it’s turned on, then even if user uploads any document to any library in the site, he will be redirected to drop off library to upload the document. If the drop off library has metadata property, then it will ask to fill in those properties which are metadata of drop off library not the library where user tried to upload the file.

There are some pros and cons of this approach, and when you start working on it in real scenario; we will have more discussion on it based on your questions.

Second option is sending to another site. If this has been checked, then we can create a rule which also can redirect the upload to the document library sitting in the other site.

Folder Partitioning: This is very important settings here. We can configure how many items we want to allow and after that start creating folders and replace % 1 with date and time.

Duplicate submission: What if the same document gets routed automatically; then uses SharePoint default versioning or appends the unique character at the end of the file name.

Preserving context: if this property is checked, then the original audit logs and properties for a document are preserved after the document has been routed. It’s important because you can have all of the historical information about an item retained with it. If it is retained, you can click on Compliance Details from the View Properties page of an item.

Rule Manager: very important property to set. Even if user has manage lists permission on the site then also cannot create rules or alter the rules. Any user that wants to perform rule operations must be added here. If the user’s name is in rule manager then only he will be able to manage rule settings for this.

Now then we have set all properties of organizer settings; let’s proceed and set up some rules in organizer rules settings. Let’s create a rule which will route excel documents to a new library in the site.

Give rule a name and then decide the priority. Priority means if document matches more than one rule then these rules are evaluated based on priority. If the rule is inactive then this rule is not evaluated at all.




As you can see it can only be done on content type, we will go with document content type. Each group has its own specific types which is very useful. We can define ruled for audio, video, images etc. by using digital asset content types. Check all the options to see. As of now, we will go with Document content type.



Set all above properties; and click on ok and you will see one rule being set in the organizer rules.



Now it’s time for action; let’s go to Drop off library and upload a document. When upload window opens, you can notice one status message saying that this is using the content organizer rule settings.



The moment you upload, it shows your screen for metadata of drop off library



Fill these details and submit it, you will see your document is now in destination library rather than in drop off library.

Relevant documents web part

Hi

We all know that there are many out of the box web parts available in MOSS 2007 and now more in SP 2010. Sometimes it is important to see what they offer. Recently I came across to a very good document management related out of the box web part which is relevant documents web part.

This web part was a part of MOSS 2007 and continues to be a part of SharePoint 2010.

The Relevant Documents web part displays documents that are relevant to the current user. This web part can show following;

Documents last modified by current user
Documents created by current users
Documents checked out by current user

So ultimately it becomes my checked out, my created, and my last modified documents.

This web part is under content rollup section in SP 2010 and under miscellaneous section in MOSS 2007 and these are the configurable properties.

How to use Group Calendar

Hi

We know how to use very calendar in MOSS 2007 and we also know that it was lacking one very basic fundamental functionality, which is the availability of people for whom you are creating an appointment or something.

Now in SharePoint 2010, we have an option to have attendees in the calendar list item as well as we can see the schedule of them as well. So that now we can see the availability of team members as well as conference rooms. We can also now have the conference room references in the calendar.

So now it is moving towards a bit of similarity with outlook calendars.

Let’s explore how it works.

First when you create calendar, it has nothing new in it. so go to list settings > General settings > Group calendar options, this will turn calendar into a group calendar.



Go back to calendar, and click on new. As you can see we can now see schedule of each person you add in attendees.



One more thing, now we also have an option directly to add event for a day. Click on Add button to add an appointment for 12th of respective month.



The real advantage of group calendar comes in when you merge two or more calendars with one and hence you would be able to see your all schedules in to one single calendar.

So see this in action, add one appointment to your any of the calendar in the site. Let’s say I have defined one event and check out the color is green.



Now go to another calendar where you want to merge this. Let’s go to Employee Calendar that I have in my site and check this out on left side when you are on that calendar. Click in Calendars in view.



When you click on it, it takes you to a page where you can add additional calendars. So go ahead and click on add new calendar.



Click on new calendar and give calendar a name and then click on resolve web URL, this will fetch lists to a drop down and select calendar from that drop down. Also you can configure the color in which you want to see those appointment. You can add up to ten additional calendars.

Now go ahead and add appointments in both calendars with same timings. And see what happens when you open the calendar in which you have added the additional calendar.



Now go to site settings and site features and find feature called “Group work List”. Activate that feature. Now go to calendar and then go to list settings >general settings and then check one new option in Group calendar option which is use this calendar for resource reservations. Turn it on.



Go to calendar and click on New, and you get this.



Click on reservations.



As you can see I have two resources conf room1 and conf room2. These options come from the list called resources. Your site should have the list named resources, if it does not have; then create one.

So as you can see, calendar has gone through a lot of enhancements in this version and certainly it is useful.

Rating in SharePoint Lists and libraries

Hi

One cool new feature that is now available in SharePoint 2010 is ratings for the list items as well as document libraries.

Now we can rate list items and documents and the other important thing is this can be searched as well. We can search items and documents which are rated between one and five; which is amazing.

To make this available, first you need to go to

CENTRAL ADMINISTRATION > System Settings > Servers > Manage services on server (by default it is Started)

Under CENTRAL ADMINISTRATION > Monitoring > Timer Jobs > Review Job Definitions > look for 'User Profile Service Application - Social Data Maintenance Job' and 'User Profile Service Application - Social Rating Synchronization Job'.

These jobs are scheduled to run every 1 hour.



You can change that anyways



Open any list, go to list settings and find ratings settings which are new in SP 2010.



Allow it and turn it on.



Once you add this, you get two fields in list items or documents item. Rating and Number of Ratings.



And then based on the timer job, you would be able to see your ratings and other ratings as well.

When you rate, it gives you an indication that your rating has been submitted and will be processed soon because it all depends on the timer job.



I hope you will enjoy this new feature in SP 2010 and use it in the search part as well.