Export SharePoint User Groups in Excel

In this article, we are going to discuss about a SharePoint tip which is not present in the User Groups by default.

Scenario:

From any list in the SharePoint, you can export the whole list in to a excel file or you can edit and save the excel file.

But is that possible in SharePoint User Groups?

Yes, it is possible.
Solution:

1. Go to Site Settings (http://sitname/_layouts/settings.aspx) -> Under Users and Permissions Select People and groups 

2. You will be seeing a URL like this http://sitname/_layouts/people.aspx?MembershipGroupId=5

3. Then navigate to the List Settings in the people and groups of a particular group which you want to export to the excel file

4. Now, in the URL you will see like this:

http://sitname/_layouts/listedit.aspx?List=72bcbfee-c10e-41ba-b128-2769c8fe9cc9&Source=%2Fpeople%2Easpx%3FMembershipGroupId%3D5
5. If you want you retrieve the users list from any view, then click on the view from the views list as shown below:
Scroll to the bottom of the page and click on the view you want to export

6.Now, copy the URL from the address bar and keep it in a notepad which is something like below:
http:// sitname /_layouts/ViewEdit.aspx?List=%7B72BCBFEE%2DC10E%2D41BA%2DB128%2D2769C8FE9CC9%7D&View=%7B4A52DE00%2D9B7C%2D4FDC%2DAC39%2D960FDF18F1F3%7D&Source=%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D72bcbfee%252Dc10e%252D41ba%252Db128%252D2769c8fe9cc9%2526Source%255Flayouts%25252Fpeople%25252Easpx%25253FMembershipGroupId%25253D5

From the above URL, we have to copy the LIST ID and VIEW ID (marked bold in the above URL)

7. Now, we can easily export the people and groups in to excel using the following dll from the SharePoint server.

http://[sitename]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl=1

8. Now, replace the site name, LIST ID and VIEWID in the above URL:

I will get the final URL as:

http://sitename/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=%7B64DAB4AC%2DD043%2D4DA4%2D85C7%2D9D4521C3F4CC%7D&View=%7B0FAE8D90%2D59D5%2D48F4%2DA54E%2DF02333777C7F%7D&CacheControl=1

Note:

To know more about the owssvr.dll please read this article

http://msdn.microsoft.com/en-us/library/dd588689(v=office.11).aspx

Open the above URL in the address bar, you will get an option to save an EXCEL FILE.

9. Open the Excel file, you will be asked for authentication, enable the external content and open it.

10. That’s it. You will see all the users in the groups with their details exported to the excel file.
11. This excel file exported using this method has all the users from the user groups but it is not filtered by user group name.

Read this article to achieve exporting the SharePoint user groups with the group name Exporting SharePoint User Groups with Group Names.
Read this article to know How to Programmatically get the users with the Group Name in SharePoint 

Programmatically get SharePoint Users with Group Name.
See Also

Check if current user is member of a SharePoint group using JQuery

Read More

SharePoint Web Part Maintenance Page

In this post, we will come to know how to navigate to the web part maintenance page.

Why we need this page in SharePoint ?

In the webpart maintenance page, we will see the web parts present in a SharePoint page.

By adding “?contents=1” at the end of the page URL, you can navigate to the web part maintenance page.

For Example:

http://severname/default.aspx?contents=1

Here, you can see the web parts present in the SharePoint page and you can delete the web parts if it is corrupted or not used in the particular page.

Advantage:

There will be scenarios like; you will get an error while opening the SharePoint page in the SharePoint designer. Read this article to know more Deafult.aspx is not opening in the SharePoint Designer

Read More

Cascading dropdown (or) Filtered Values in Lookup Columns in SharePoint

Update: 27/06/2017

To know about implementing Cascading drop down or filtered lookup in SharePoint 2013 or SP Online using REST API, read this article: Cascading drop down in SharePoint using REST APIUpdate: 06/07/2017

To know about implementing multi-value lookup column and filtering values, read this article: Multi value Lookup Columns in SharePointTo know about the Basics of Lookup Columns read this article.Also, to know about the Enhancements of Lookup columns in SharePoint 2010 read this article.

Problem:

In SharePoint the cascading or the filtered values in the lookup columns are not present by default.

In this article, we will achieve this using the simple JavaScript.

Solution:

Consider the simple scenario that the user needs to input the values of Continent, Country, State and City which has to be the filtered values.

Steps:

1. Create four lists with the following column and corresponding types

Note: 

I haven’t used the default title column for any of the lists above and I have hide it from the default view.

To know how to hide the default title column from the list read this article.

2. The screen shots of the four custom lists created in the step 1 are shown below:

3. Now, we will create another custom list for testing the cascading dropdown in the SharePoint. Create a custom list with the following column and corresponding types

The screen shot of the created list is shown below:

4. When we click on the dropdowns in continent, country, state or city we will get the unfiltered values as shown below:

5. Now we add the java script to achieve the solution

General Code to be added:

1 2 3 4 5 6 7 8 9 10

// Execute the following JavaScript after the page has fully loaded, when it's ".ready" $(document).ready(function(){ $().SPServices.SPCascadeDropdowns({ relationshipList: "Display Name of Master List", relationshipListParentColumn: "Parent Column Internal Name from Master List", relationshipListChildColumn: "Child Column Internal Name from Master List", parentColumn: "Parent Column Display Name from List/Library", childColumn: "Child Column Display Name from List/Library" }); });

Description of the code

Note:

To know how to get the Internal Name of a column in a list in SharePoint, read this article.
Download of JQuery referred in the code can be found from jquery.com and codeplex.com

For ease of download for the readers, I have uploaded the script in the below links. Click to download the jquery-1.4.2.min.js and jquery.SPServices-0.5.3 min.js

Code in our case:

6. Now, we will insert this JQuery and achieve the cascading dropdown.

7. Click on New in the created custom list (Cascading Lookup Demo) as shown below:

8. Now, we will insert a Content Editor Web Part in the newform.aspx to place the JavaScript code in it.

 To know how to insert a web part in the NewForm.aspx without using SharePoint Designer read this article.

How to Insert a Web Part in NewForm.aspx without SharePoint designer

9. After inserting the web part, paste the code and save the web part.

Note: Place Content Editor Web Part below the list form or else the Java Script added won’t work.

10. We are done! Now, we will check the cascading drop down. So, when the continent is selected only countries with respect to the continent should be displayed (Filtered values) and then the state, city in the same case.

The results of various scenarios are shown below:

I have checked for the empty values scenario also. What if the child column is empty or does not contain values. Then after inserting the value, it should add empty values in the column as shown below:

After inserting the values the list is as follows: 

Advantages:

Ø  We can simply achieve the cascading drop down in lookup columns in SharePoint without using any third party tools

Ø  No need of SharePoint designer to achieve this result

 Disadvantages:

Ø  This won’t work in the Data Sheet View since we are inserting the J query in the aspx page only.

Ø  When the number of items in a look up column is greater than 20 then your drop down will change as follows:

We had more than 20 states in the States List (28 items with lookup values)

This happens only in the IE and not in the Chrome or any browsers.

Have a look at the page in the Chrome Browser:

The reason for this variations and the fix is explained well in this article. Below is the link

SharePoint Lookup field: how does it work and how to add JavaScript event handler function to it? 

Hope you have read a nice article.

Please free to comment. Always, your comments help me to write more.

Share this post to others if this helps you!

Note:

I have updated the code of this article on 08.06.2012 since readers of this article requires the following:

1. What is the case if there are more than 2 values (4 cascading or filtered values – using more number of variables)?

2. What is the case if there is an empty string in the child value?

3. To explain the solution with more details for ease of reading and understanding for the readers.

Update: 27/06/2017To know about implementing Cascading drop down or filtered lookup in SharePoint 2013 or SP Online using REST API, read this article: Cascading drop down in SharePoint using REST APIUpdate: 06/07/2017To know about implementing multi-value lookup column and filtering values, read this article: Multi value Lookup Columns in SharePoint

Read More

How to Insert a Web Part in the NewForm, EditForm and DisplayForm.aspx in a list without using SharePoint Designer

In this article, we are going to know very useful and tricky tip in SharePoint.

We have options for inserting the web parts in the home page or any libraries in the SharePoint using Site Settings -> Edit Page -> Insert Web Part

What will you do for inserting a web part in the New/Edit/Display forms in the list?
So, we will go to SharePoint designer and edit the list page and insert the web part. This is the usual way.

Suppose that, I want to insert webpart without using the SharePoint designer. Is it possible? Yes. 

Here is the trick!

Steps:

Go to the list page -> Click New for adding the item as shown below

This is the URL, we will get we click New Item in the List.

http://sitename/Lists/Regions/NewForm.aspx?RootFolder=%2FLists%2FRegions&ContentTypeId=0x0100D296A516EE3F4F4EA21125EB1E562502&Source=http%3A%2F%2Fservername%2FLists%2FRegions%2FAllItems%2Easpx

Here, we use TOOLPANEVIEW concept in the SharePoint to implement the same.

After the “NewForm.aspx” part of the URL, erase everything and replace it with “?toolpaneview=2” (without quotes) and press enter. This will open the page where you can add the web parts easily.

Now, the new URL will be:

http://sitename/Lists/Regions/NewForm.aspx?PageView=Shared&ToolPaneView=2

Now, you will see the following, where you can add the web part from the Add Web Parts option as shown below:

Note:

If you need to keep the URL parameters in the URL for some reason i.e., if you wanted to make changes to only a single item, keep the URL parameters and add the following to the end of the URL: “&toolpaneview=2″ (without quotes).

Similarly, if you want to add it for EditForm or DisplayForm pages, you can merge it as shown below.

http://{SiteName}/Lists/ListName/EditForm.aspx?PageView=Shared&ToolPaneView=2

http://{SiteName}/Lists/ListName/DispForm.aspx?PageView=Shared&ToolPaneView=2

To know how to insert a web part into New/Edit/Display form.aspx in SharePoint 2010, read this article.

Hope this helps you. Please feel free to comment and share this post.

Read More

Lookup columns in SharePoint 2010

In the previous article, we have learned about basics of lookup columns and the disadvantages in lower versions of SharePoint.
In this article, we will discuss the capabilities of lookup columns in SP 2010.
Types
There are two types of lookup columns:
1. Lookup columns with an enforced relation
2. Lookup columns with an unenforced relation
Lookup columns with an enforced relation

In a lookup column with an enforced relationship (also called referential integrity), you can lookup single values and maintain the integrity of your data in the target list in one of two ways:

Cascade Delete When an item in the target list is deleted; the related item or items in the source list are also deleted. In this case, you want to make sure that all related items are deleted as part of one database transaction.
Restrict Delete When you try to delete an item in the target list, and it has one or more related items in the source list, you are prevented from deleting the item in the target list. In this case, you want to ensure that the related item in the target list is preserved.
Lookup columns with an unenforced relation
In a lookup column with an unenforced relationship, from the source list, you can lookup single or multiple values in the target list. Also, when you delete an item in the source list, as long as you have delete permission on the list, there are no additional delete restrictions or delete operations that occur in the target list.
Enhancements in SP 2010
In SharePoint 2010, three major enhancements are added in the lookup columns:
1.   Additional column to show
2.   Cascade or restrict delete options
3.   To allow duplicate values or not options
As described in these article, I will create two custom lists such as Operating systems and Asset details as shown below

In the Asset details list, we will create a column which has lookup column from Operating Systems list as shown below

Note: I have created enforce relationship in this with restrict delete option as shown above.Now, we will add an item to asset details list

Now I have added some more items with different lookup fields from it.
Now, we will try to delete the Windows Vista item from the operating systems list which has lookup in the asset details list.

Now, as per the relationship this cannot be deleted and we will get an error which shows that asset details and operating systems list are related and it cannot be deleted as shown below.

Now, we will check with Cascade delete option

As per the relationship, when an item in the Operating System list is deleted, then the related item in the Asset Details will be deleted as shown below

I have deleted Windows Vista and hence, the corresponding items are deleted in the operating system list as shown below

When the relationship is not set, deleting an item in the source list will leave a blank space in the target list as shown below (this is the case same in WSS 3.0 and MOSS 2007)

Hope, this article helped in understanding the lookup column capabilities in SharePoint 2010 easily.

Now, there is another question arises, is it possible to create a cascading drop downs using lookup columns in SharePoint?
For answer, see this article.

Read More

Lookup columns in SharePoint

In this article, we are going to discuss a simple and useful feature of SharePoint which is called lookup columns.

What are Lookup columns in SharePoint?
Ø  In simple words, it is a referential integrity between the lists in SharePoint (It is something like relating the two columns in a table in the database using queries).
Ø  We will create lookup columns, when we need to create relationship between two lists.
Ø  To create a relationship between two lists, in the source list, you create a lookup column that retrieves one or more values from a target list if those values match the value in the lookup column in the source list.

Advantages of Lookup columns

Ø  Easily create relationships between the lists without using calculated fields

Ø  Creates multiple relations between the lists

Lookup columns in WSS 3.0 and MOSS 2007
We have two custom lists created named Asset Details and Operating Systems
In Operating Systems list I have the following columns
ID                                   - Default column
Title                                - Single Line of Text
Release Version               - Number

The Operating System list is shown below

In Asset Details I have the following columns

Machine Name                - Number

Location                          - Single Line of Text

Operating System           - Lookup column

The below is the screenshot which shows how the lookup column is created for the Operating System column in the asset details list

Now, I will add a new item to the Asset details list

Hence, here for the operating system column, you will get the lookup values from the operating systems list as shown above.

Once created, when you click on the Windows 7 in the list, you will be navigated to the windows which shows the properties of the Windows 7 as shown below

Now I have added 3 values in the asset details list as shown below

Now, I will delete the Windows Vista data from the Operating Systems List as shown below

Now, we have deleted the Windows Vista item from this list, which is integrated or mapped in the Asset details list. Either, it should not allow us to delete or should show some warning about it. But it is deleted.

There’s no referential integrity between the two.

When we go and see the asset details list, the value is left empty as shown below

This disadvantage has been resolved in the SharePoint 2010. Read this article to know about lookup columns in SP 2010.

Read More

Reading the Custom Properties of MS Office Word 2007 & 2010 documents

Earlier, we have discussed about reading the Summary and Custom Properties of Word 2003 using DSO DLL.

Here are the articles references

Basics of DSO DLL

Reading Summary Properties of Word 2003

Reading Custom Properties of Word 2003

As already mentioned in the above articles, DSO dll is a 32 bit and also it has OLE property reader class, which can read only the office 2003 documents.

The same DSO file cannot be used for reading the properties of 2007 & 2010. This is because, word 2003 stores the properties in OLE type and hence DSO has capabilities to read the properties. But word 2007 and 2010 stores in XML formats.

Want to know how the office 2007 & 2010 stores the properties in XML formats?

Change the extension of word 2007 or 2010 documents from .docx to .zip. Now extract the contents.

You will see many xml files inside that folder.

Now open the folder -> docProps folder -> 3 files will be there                     
 1.  App.xml – it stores the document’s summary properties

     2.   Core.xml – it stores the document’s summary properties

     3.   Custom.xml – it stores the document’s custom properties

The below snap shot shows the custom.xml file of a word 2007 document

It is possible to read the custom properties of word document using Office DLLs (which is deployed when you install a MS office in a machine).

But how to read the properties without using Office DLLs?

Hence, we will go for an open source dll provided by Microsoft called Open Office XML SDK 2.0.

The same can be downloaded from the below link

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5124

It has the capabilities to read the xml properties from the MS office documents.

Code snippet for reading custom the properties of word 2007 & 2010

public Dictionary<string, string> WDGetCustomProperties(string filename)
{
using (var package = WordprocessingDocument.Open(filename, false))
{
var properties = new Dictionary<string, string>();
var customProps = package.CustomFilePropertiesPart;
foreach (var property in package.CustomFilePropertiesPart.Properties.Elements<CustomDocumentProperty>())
{
                    if (property.Name == "Name")
                        Name = Convert.ToString(property.InnerText);
                    if (property.Name == "Expertise")
                        ExpertiseIn = Convert.ToString(property.InnerText);
                    if (property.Name == "Place")
                        Place = Convert.ToString(property.InnerText);
}
 return properties;
}
}

Note: Don’t forget to declare the 3 variables used for storing the values in theabove code.

Hence, this article shows you how to read the custom properties of the word 

2007 & 2010 documents.

To know how to create the custom properties in the Word 2007 & 2010 read 

this article.
Hope this helps You! Please free to comment.

Read More