Introduction
In SharePoint, you can create a diverse set of lists to hold your data, whether they be documents, service requests, or process tracking. The view tools provided by SharePoint with the various filter and grouping tools are useful for managing that data and making it easier to find a specific record.If the list grows very large those view and filter tools may not be sufficient to find the specific records you are looking for quicky and easily. In our case, we have thousands of records submitted by various users that need to be tracked. A quick way to narrow down the list is to filter that list by the users last initial. The screenshot below shows the alphabetic filter above the list, and the list it filters below it. So, clicking the "B" on the top list of links filters the list below by all users who's last name begins with B. Clicking any other letter will extract all user records that have that last initial. Clicking "Clear" clears the filter and shows all of the records in the default view set up for that page.
 |
| List with last initial filters connected. "B" selected. |
Part One
Modify the list to extract the last letter of the field to be searched.
The first step is to extract the first letter of the field you wish to filter.
If you are using a straight text field (Single Line of Text), you can extract the first letter using a calculated column. Create a new calculated column that will hold the first letter on the list you wish to filter. Use the following formula in your field:
=LEFT([YourField],1)
where "YourField" is the column that you are extracting the first letter from.
If you want to extract from a complex field such as "Created By", you will need to use a workflow to extract that value.
As above, create a new column in your list but make it a straight text field, rather than a caluclated column.
Open SharePoint designer, and create a new list workflow named something like "Capture last initial". Ensure it is triggered whenever an item is created. Also ensure that the workflow can be manually started. This allows you to populate the new field with existing records extracted last letter.
Create two new workflow variables. One named "Captured Name" and one named "Last Initial". Both are strings.
For your first line of the workflow, choose "Action". Select "Set Workflow Variable". Choose "Captured Name" as the variable, and select the field "Current Item", "Created By", and select "Display Name" as in the screenshot below:
 |
| Captured Name should be display name version of current item created by |
For the second line, again choose "Action", and select "Extract Substring from start of string". Choose your captured name variable as the extracted string, and choose "Copy 1 character from the start". Next, set the output variable as your last initial variable. See the workflow screenshot for more clarity. Finally, update the current items field you just created in the source list (last initial) to the value in the last initial variable:
 |
| Completed workflow that selects last initial |
Part Two
Create the list of links to generated the URL Filter link.
First, we need a page that both hosts the list you wish to filter and the Content Editor Web Part and Query filter web part used to filter the list. Create a new web part page and add your list to be filtered to that page. Select the view that is most appropriate using the web part configuration components.
Add a content editor web part to the top of your page.
Next, we need to create some HTML that has the filter letters embedded in the URL. Here is the link format:
Create 26 of these, modifying the Initial and display letter to include each letter of the alphabet.
In your content editor webpart, insert the links you created into the following html. This will format the letter based links into something more useable, and add two "clear" boxes to remove all filters from the associated list:
Save your webpart page. You should now see something similar to the screenshot at the beginning of this article.
Part Three
Use the Query filter web part to pass link values to filter list.
The final step is getting your clicked link values passed to the list below to filter that list. This is done using a query string filter. Add a new webpart to your page and, in the "Filters" section, choose "Query String URL Filter".
Add this to your page. Click on the query string filter on your page, and choose "Edit Web Part". Leave the filter name as query string, unless you want to change it. For the query string parameter, insert the parameter we have embedded in our html described above "Initial". Click "OK".
Now, on the query string filter web part, click the drop down arrow on the right side and choose "Connections". Then choose "Send Filter Values To". Then select your embeded list on this page as your choice. If you only have one list on this page it should be your only choice.
Save your page. If you are generating your last letter with a calculated field, your filters should now work. If you are generating the last letter by workflow, then you will need to manually run the workflow for each item in your list. Its a good idea to set this up before the list gets too large, if you have that forsight. If you are generating the last letter by workflow and already have a large number of records in the list, then you can manually enter the last letter for each record using datasheet view.
Once this last letter field is populated, you should now be able to filter the list by clicking the letter links.
