Author: Jim Bob Howard

The “Real” Title: How I Created a SharePoint List to Replace an Excel Spreadsheet That Was Being Routed Around to 15 Managers Every Month by Creating a Web Part Page for Each Manager Which Displays an Editable Table of Employee Information Filtered into Easy-to-Read and –Update Tabs, Using Site Columns, Cascading Dropdowns, DVWPs, EasyTabs, Form Action Workflows, PreSaveAction(), SPServices/jQuery, XSLT, and CSS.

At last, we arrive at part 35 of Extending the DVWP. For the last 18 weeks, we’ve been assembling tools, techniques, and trial-and-error to put together a robust solution to solve a simple problem:

Problem: "I have an Excel document that I re-create every month and email out to 15 managers. Each manager is responsible for one of fifteen worksheets, but I send all the sheets to every manager, hoping they make updates to the right areas. I also cross my fingers that they only enter data in certain fields and don’t obliterate my formulas.

"Is there a way we can do this better via SharePoint?"

Our solution needs the following:

1. Avoid routing documents through email
2. Allow each manager easy access to view and update their own data
3. Give the curator a view of all employees across all locations
4. Audit changes to the current data, allowing historic reporting
5. Make regular changes as easy as possible
6. Make irregular changes as intuitive as possible
7. Give the manager multiple views of employees, gathered by employee type
8. Provide group sub-totals and page totals

It will look something like this:

Getting Started

The web part that looks most like an Excel spreadsheet is the Data View Web Part (DVWP), which will allow us to show all of the employees for a Location with on-the-page edit options. So we’ll start with a DVWP, but extend it to meet our needs.

Everything for this project will be contained within its own subsite so that the default page and the Quick Launch navigation column will all pertain to the Full-Time Employee (FTE) project. The easiest way to start creating pages that function like the default page (and, therefore, include the Quick Launch) is to create them in SPD from the default page.

Right-click default.aspx to open context menu, then click New from Existing Page

We won’t need all of these webparts, but it’s a good place to start:

Our new webpart page based on default.aspx for our site

Assuming the possibility that we’ll want to use Share Documents for… well, documents we want to share… we’ll save our new page in a new document library called Locations.

1. From the Web Site tab, right-click in the window to open the context menu


Right-click in window, then choose New -> SharePoint Content…

2. Choose Document Libraries and Document Library, then give the new library a name


Choose Document Libraries and Document Library, give it a name

3. From the tab of your new document, Save it to your new library


Save the new page into the new document library. I called mine LocationsMaster.

Now we have a page we can work with. In my case, I don’t need the Right web zone. Rather than just deleting the zone, I’ll remove the TDs that hold the Right zone and the space between it and the Left zone.

Easy as 1-2-3 … uh, 4

1. In Split view, click on the Image web part in the Design pane. This will select the HTML/XSL for the entire web part (dark blue, above) in the Code pane, as well as highlight the ImageWebPart in the Tag Bar.
2. If you click on the Tag Bar tab for the WebPartZone#Right, the HTML/XSL for the entire web zone—and any included web parts—will be selected, but not the TD that contains it.
3. The tab just to the left of that one will select the entire TD tag.
4. But we want to delete the preceding sibling TD, too, so we’ll hold the Shift key and click to the left of the TD tag on line 244 above (your line numbers may vary) to select it, as well.

The Delete key will get rid of it all for us.

In the Design pane, we see that there’s still a big gaping hole where the right zone was.

Removing the Right web part zone left a big hole on the page. We’ll fill that in.

To stretch the Left zone over to where the Right used to be:

1. Click on a web part in the Left zone, then
2. Click the closest td tab in the Tag Bar, which will
3. Bring the td tag to the top of the Code pane, where you’ll
4. Notice that the width is set to 70%.
5. Change that to 100% and you’ll get your real estate back. Well…
6. You’ll find that there’s one more td below this one, so
7. Delete it and your DVWP will be wall-to-wall

For Announcements and Calendar, we don’t want to delete the zone, just the web parts. So, for each of them:

1. Click on the web part in Design,
2. Hover over the highlighted tab in the Tag Bar,
3. Click the arrow to open the context menu, and
4. Choose Remove Tag

Now we can add our DVWP.

Adding the DVWP

Now we have the shell of our page weathered-in, we are ready to finish. Let’s add our first DVWP.

1. Click the link in the zone to highlight it in SPD.


Follow the instructions on the link.

This will make that zone ready to receive the DVWP.

2. Click Insert -> SharePoint Controls -> Data View

3. SPD continues to walk us through the process.


On the Data Source Library tab, click on the dropdown for the list that contains your data. Then, click Show Data.

4. SPD will shift over to the Data Source Details tab where you can choose the fields you want to include.


Note: SPD will put the fields on the DVWP in left-to-right order according to the order you click them in the Data Source Details window. Hold down the Ctrl key to click on multiple columns out of the displayed order.

5. When you have the fields you want, click Insert Selected Fields as…


…and choose Multiple Item View.



Initial load of the DVWP

Now that we have it on the page, we can start massaging the page through wizards and minor XSL changes.

Massaging the DVWP

One thing I definitely want is for my project to use multiple DVWPs to manage the data, rather than look at it in blocks of ten records at a time.

Massage Data View Properties: Paging

The red-boxed link opens the Data View Properties dialog box to the Paging tab

You could also get there from the menu: Data View -> Paging…

 

By default, paging is set to 10 items. We’ll check Display all items.

While we’re here, let’s change a couple of other settings.

Massaging Data View Properties: Column Heading Sorting

It’s nice to let the user do some sorting within the page, so we’ll turn that on from the General tab.

No Toolbar settings are checked by default. We’ll Enable sorting and filtering on column headers.

Massaging Data View Properties: Editing

We want the user to insert, edit, and delete right from the DVWP, so let’s turn those on from the Editing tab.

All Link options are turned off by default. We’ll check all three boxes.

The first few extensions to the DVWP: inline editing, no paging, and column header-based sort/filter.

Mine took awhile to load in SPD because I have 486 records. I certainly don’t want my users having to load that much data at once, so let’s add some filters and grouping.

Massaging Data View Properties: Filtering

Filtering the DVWP will allow us to segment the list according to Location

Like Paging, you can get to the Filter Criteria dialog from the menu: Data View -> Filter…

Massaging Data View Properties: Sort and Group

While we’re working with the tasks, we’ll sort and group the data chunk we’ve created.

The default is to sort ascending by Created. We’ll group by Group and sort by Worker, both ascending.

Massaging Data View Properties: Edit Columns…

Now that we’ve filtered by Location and grouped by Group, we don’t need those columns visible anymore.

Remove the Location and Group columns by highlighting them and clicking << Remove.

Massaging Data View Layout: Titles, Headers, and Formatting

Just a couple more tweaks to make before we start building out our extended solution.

Title

Since we’re going to use this same web part for multiple locations, we need to make a quick change to the page title.

The web part title defaults to the name of the List whose data we’re displaying. We’ll change it to Operations to reflect our filtering options.

Headers

Now that I’ve created my list and loaded all my data, HR has decided to change their nomenclature: they want to see "Job Title" instead of "Position," "Employee" for "Worker," "Shift" rather than "Work Shift," and "Effective" in place of "EffDate."

Each column header is handled by a template call to dvt.headerfield, passing in the display name.

Click the column header in Design to highlight the template call in the Code pane.

In this template, fieldtitle and displayname are the two we have to update to change the text on the header row (see line 245-6):

<xsl:call-template name="dvt.headerfield" ddwrt:atomic="1" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime">

    <xsl:with-param name="fieldname">@Positions</xsl:with-param>

    <xsl:with-param name="fieldtitle">Job Title</xsl:with-param>

    <xsl:with-param name="displayname">Job Title</xsl:with-param>

    <xsl:with-param name="sortable">1</xsl:with-param>

    <xsl:with-param name="fieldtype">x:string</xsl:with-param>

</xsl:call-template>

After we changed the column titles

Formatting

The time on the Effective Date is really meaningless; it’s always 12:00 AM. Let’s hide it by changing the data format.

Dates show the time by default. We’ll uncheck Show Time to make it just a date. We could also change how the date is displayed.

Extending the DVWP

Now that we’ve finished massaging the way it looks, we can start Extending the DVWP.

The first thing is to Rearrange the Columns to get the edit and delete links on the right side of the page. We’ll have to do that for the default template, the edit template, and the insert template.

Next, we’ll put those form action links all on one line and then turn some of them into buttons.

Once we have them looking the way we want them, we’ll open them up, see how they work, add a PreSaveAction(), add a  workflow, rearrange some things, and then fix what we broke.

Then, we’ll pass some workflow variables to the workflows, pass some form variables, and then add a few more. And we’ll add more variables that we can use with jQuery and the PreSaveAction() for form validation or writing to an audit trail.

To make data entry and modifications easier for the user, we’ll set up some site columns, load them with data, and create relationship lists between the site columns (and load those one of two ways). Then we’ll put those site columns and relationships to work with cascading dropdowns (both two and three-tier cascades).

Sometimes, when we’re loading massive amounts of data, it’s nice to have some fields defaulted for us. For instance, in our relationship lists, the Title is almost superfluous. It just tells us what data is in the relationship columns. We can let SharePoint fill that data in for us with jQuery. Alternately, we could fire up a workflow to do it instead.

Now that our data is flowing well, we can set up an audit trail to capture any changes made to the list.

Since our edit template looks like our default template, it may confuse our users as to which form they’re on. So, we can modify the edit template with some easy graphical elements to make it obvious where we are. And we can create another version of the edit template to capture information when a list item is deleted. This remove template can be massaged separately to meet our needs. We can even modify its form action workflows independent of the edit template.

To make things easier for the amounts of data we’re displaying to disparate users, we’ll want to reuse the web part we’ve created using Christophe Humbert’s Easy Tabs code for the Content Editor Web Part (CEWP). For each page we create following the steps above, we’ll add as many DVWPs as we want for each section, changing the filtering and sorting as it makes sense.

Before we save the webpart, though, we might want to make a few more changes to the insert template to fill in default data. But since we’re using multiple DVWPs, we’ll need to make special considerations so that our defaults land on the right tab.

Finally, to make it look even nicer, we’ll massage the way total and subtotal rows look and replace some of our form action links with icons.

Reusing Our Web Part

It took us 18 weeks to get here. It would be horrible to have to recreate each of those steps every time you want to add a new tab, or a new Location. Fortunately, we don’t have to. We can just export the webpart we created and reuse it.

We’ll do this from the browser.

1. Click Site Actions -> Edit Page.
2. Click edit -> Export…

Save it somewhere easy to find.

Then whenever we want to use it, we’ll simply:

1. Click the Add a Web Part button

2. Click Advanced Web Part gallery and options

3. Click the Browse dropdown and choose Import

4. Click the Browse… button

5. Locate your webpart and click Open

6. Click the Upload button

7. Drag the webpart onto the page

8. Repeat for each tab you want on the page
9. Adjust the filtering, sorting and grouping for each tab, as above
10. Finally, click Exit Edit Mode

Goal!

Even though we’ve reached the end of the Extending the DVWP series, we’ve really only touched the tip of the iceberg of functionality available from the Data View Web Part. Keep checking back on EndUserSharePoint for more great ways to harness the power of the DVWP.

It’s been a pleasure to present this series and I look forward to hearing what great things YOU are doing with the DVWP.

Bonus

The question recently came up on Stump the Panel asking how to get around the "feature" that causes the insert form action link to go away when the list is empty. My solution is a bonus article to this series. Watch for it!

Author: Jim Bob Howard

Jim Bob Howard is a web designer / webmaster in the healthcare industry. He has been working with SharePoint since March 2009 and enjoys sharing what he has learned. He is a moderator and frequent contributor to Stump the Panel, and answers SharePoint questions on Twitter (@jbhoward) and via email ([email protected]).