Thursday, June 7, 2007

Creating a A-Z filter for a list

My customer wanted to have a library list, for adding books.

We had a title field for the book and some other meta data for each book.

He wanted to have a page where users could filter the list to view all books starting with A,B,C...Z

Something that will look like:
A,B,C,D,E,F,G,H,...Z

And a list grid below that will filter all books starting with the selected letter.

I found a rather easy solution for that that needed no code at all - only customization!

Basically what I did was:
1 - Add a calculated column that will hold only the first letter of the Title field.
(fomula for this: =LEFT(Title,1) )
2 - Then I added the list grid web part.
3 - To add filter support I added above a rich text web part and added the HTML for A,B,C...Z and made each letter a hyperlink to the same page with adding "?FilterBook=A".
4 - Now, all i had to do is to add a query string filter web part (comes with SharePoint) and configure it to take the filter value from "FilterBook" query string and send it to filter the list grid web part below.

Done!

No code solution that took no more than 10 minutes!

Customer happy, me happy :)

28 comments:

clickpunch said...

Hi, thanks for the interesting post.

I'm new to the Sharepoint development and would love to try this out myself but given the instruction, I'm not sure what to do.

Would it be possible to give more detailed direction? For example, what it means to add a "calculated column" to where, how to add "list grid web part", how to add filter support, etc.

Thanks again.

clickpunch said...

Sorry never mind my previous comment. It was easy enough to figure out. Here are few things I did:

- Under Document Center site
- Select Site Action and select Create
- Select Custom List and name it whatever you like (my case "Book List")
- Once created, add few items into the list. In my case, I added title that start with "T..." and "I...".
- From Book List tool bar, select "Settings -> Create Column"
- Type column name as "Filter By Title"
- Select "Calculated" as column type
- Under Additional Column Settings, select "Title" from Insert Column and type "=LEFT(Title,1)" into Formula. Keep all default settings and click "OK"
- Back to Book List, you will see new column called "Filter By Title" and has T and I value in them already.
- From Site Actions tab, click Edit Page
- Click Add a Web Part and add "Content Editor Web Part"
- Modify that Content Editor Web Part and click "Rich Text Editor" button to add two customized links.
Basically they are two hard links like "I". Note that I get the URL from the Book List URL itself and appended FilterBook after it.
- Click "Add a Web Part" and add "Query String (URL) Filter".
- Modify the setting to have "Query String Parameter Name" to have "FilterBook" then click "OK"
- Now from Query String (URL) Filter, select "Edit -> Connections -> Send Filter Values To -> Book List" and when Popup appears select "Filter By Title"

That's it! To include "ALL" to go back to showing all, just get a hard link without the FilterBook variable and put it into the Content Editor Web Part.

Shai Petel said...

Hi ClickPunch!

Sorry my instructions were not clear enough, but I am glad to see you managed the solution your own!

Feels great, doesnt it? Thans SharePoint for you...

Unknown said...

This is great stuff. Thank you for posting this.

I work at a hospital where our lab needed a solution for tracking phone orders. I created a web part page as their front-end and the data is entered into a list library. I have the list grouped by last name alphabetically, but there are so many names that I wanted to create something like this to make it easier for them to go right to the first letter of the last name they were looking for rather than have to scroll down through the list.

Thanks you again!!

Anonymous said...

Thanks a lot, you saved me a lot of time!

Anonymous said...

Hi I would really appriciate a detailed walktrough on how u made this work? Their are some elements in the guide that I dont understand. Like the "query string filter web part" that you say comes with Sharepoint, I dont seem to find it in the webpart gallery.. And when u do the url in the a -z list is that in a "Content Editor Web Part" and to what page shall the url point to? the list or another page where I show data from the list?

Anonymous said...

Hi, The query string filter web partwill come only with original versions. If u r struggling to get the filter eventhough u r having the original version ,u will find the filter in the "Advanced Web Part gallery and options" avilable at the lower right end of add web part pane.

Anonymous said...

Hi, I tried to do the same way as clickpunch said.But the message displayed is 'There are no items to display'.Your reply will be highly appreciated.What is your mail id?

Doc said...

Hi !

Awesome smart solution !
YMMD !

Regards from Germany.

Unknown said...

Great solution! I appreciate you taking the time to post the concept and steps.

Grajeda Fam said...

It works great for a one level folder structure however if you have 2 or more levels of folders it doesn't work.
When I click to open the filtered folder I don't see any of my subfolders. Any idea to make this work?

Shai Petel said...

Hi Azha,
How about usign a view that does not show sub folders?

Anonymous said...

For German installations its not:

"=LEFT(Title,1)"

but

"=LINKS(Titel;1)"


VERY TRICKY
despiteful

Shai Petel said...

LOL I had no idea functions are changed in languages...

thanks!

Srikanth Gadiyaram said...

@ClickPunch Actually I'm working on the same issue now. Can you please elaborate the solution for my better understanding.

"Under Document Center site
- Select Site Action and select Create
- Select Custom List and name it whatever you like (my case "Book List")
- Once created, add few items into the list. In my case, I added title that start with "T..." and "I...".
- From Book List tool bar, select "Settings -> Create Column"
- Type column name as "Filter By Title"
- Select "Calculated" as column type
- Under Additional Column Settings, select "Title" from Insert Column and type "=LEFT(Title,1)" into Formula. Keep all default settings and click "OK"
- Back to Book List, you will see new column called "Filter By Title" and has T and I value in them already.
- From Site Actions tab, click Edit Page
- Click Add a Web Part and add "Content Editor Web Part"
- Modify that Content Editor Web Part and click "Rich Text Editor" button to add two customized links.
Basically they are two hard links like "I". Note that I get the URL from the Book List URL itself and appended FilterBook after it.
- Click "Add a Web Part" and add "Query String (URL) Filter".
- Modify the setting to have "Query String Parameter Name" to have "FilterBook" then click "OK"
- Now from Query String (URL) Filter, select "Edit -> Connections -> Send Filter Values To -> Book List" and when Popup appears select "Filter By Title""

this is your solution. I created a library and added some documents and created a new column with "Filter by Title" as a calculated column and added the formula and it is showing only the initial letters. I created a page and added this library and added a content editor web part , but I'm not able to figure out what to do here.

You wrote "
Basically they are two hard links like "I". Note that I get the URL from the Book List URL itself and appended FilterBook after it."

CAN YOU PLEASE EXPLAIN ME WHAT IS THIS PART!!!!!!

Shai Petel said...

hi Srikanth Gadiyaram,
Where did you get stuck?
I assume you get the list working and the calculated column that only shows the first letter, right?

So, next thing to do is to create a web part page if you dont have one already (you can use your home page) and add the following:
1. list/library view web part that shows all books - not filtered - including the first letter calculated column.
2. add a content editor web part, with the a-z links. those links simple change the query string in your page.
3. add a query string filter web part. this web part will read the query string and pass it as a filter to the view web part (added in step 1).

please let me know in which step you got stuck... good luck!

Anonymous said...

Hi, Shai.

I got stuck in the same step as Srikanth.

Could you explain how to link URL to change the query string?

Quote:
Basically they are two hard links like "I". Note that I get the URL from the Book List URL itself and appended FilterBook after it."
"

What does GET the URL and APPEND FilterBook mean? What is FilterBook? the new column other than "Filter by Title"?

Please help me on this.

Jean

Shai Petel said...

Hi Jean,
Sorry I was not clear... let me try to explain:

you have 3 components to complete this task:

1. create a new column in the list to filter by. this column will have the first letter of the title only.

2. place a web part on the page with a-z a href links. each link will be to the current page URL with a different query string. the query string key I chose is "FilterBook", but you can select any other key, as long as it is the same one in step 3 below.

3. place a "filter by query string" web part. this is an OOB web part that can take a specific key from the query string and send it as a filter. in this case, I set the key name to be "FilterBook", you can make it anything else you want.


I understand your questions are on step 2, please correct me if I'm wrong.

In step 2 I meant you should get the URL of your current page, for example: "http://server/site/page.aspx", and build the links a-z like this:
A would go to http://server/site/page.aspx?FilterBook=A

B would go to http://server/site/page.aspx?FilterBook=B

… and so on until Z.

you should build these links manually in the HTML. For more advanced users, you can use client script to build the links.

Good luck!

Anonymous said...

Hi,
I have a simpler solution than this, even doesn't need a Calculated column.

Just call the page URL with FilterName and FilterMultiValue Parameters.


Like
url=url + "FilterName=ReportDescription&FilterMultiValue=" + sQuery
$(location).attr('href',url);

and Pass sQuery as A* for button "A", B* for button B and so on.

Anonymous said...

query string filter web part not available without Enterprise licence so it seems. Bummer. Any alternatives welcome to this solution. We have Standard licence only.

Thanks!

Shai Petel said...

I didn't try it myself, but previous comment suggested using FilterMultiValue in query string filter to inject the filter directly to the list view web part.

You can try that maybe...

Unknown said...

Hi Guys....good Evening

in this Topic you said adding a Column to list or library...
but
my requirement is without any changes the in the list or library how can i filter the results based on alphabets

Unknown said...

Hi Guys

in my requirement i need to filter the values from list/ library but without changes in list or library...

Unknown said...

Hi Guys

in my requirement i need to filter the values from list/ library but without changes in list or library...

Shai Petel said...

Hi Krishna,
This post is from 2007, on which version of SharePoint are you working currently?

Feel free to take a look at this product: http://www.kwizcom.com/sharepoint-add-ons/sharepoint-list-filter-plus/overview/ maybe it will provide an answer to your need

Nigel said...

Hi
In a list view of a document library, the Title Field is not populated and you cannot use the Name field in a calculated field. So LEFT(NAME,1) does not work.

Are there any other ways of doing this ?

Regards

Nigel

Shai Petel said...

Hey Nigel, if you want to use this method you will probably need an event handler that calculates the file name into the first char.
That would either require you to do some dev work, or buy a product.
We have a product that can help set the title to match the file name in libraries for example that can help.

Unknown said...

Nice and perfect!