Skip Ribbon Commands
Skip to main content
Home
Personal experiences and findings related to SharePoint.
January 18
Create Dataview Web Part to Display Data From a SQL Database View
I want to create a page in our intranet to be used like an "Associate Directory".  Key factors:  Not all associates are in Active Directory, but all are in a custom SQL database.

We need to use SharePoint Designer 2010 (SharePoint 2010 site) to create the datasource and dataview.

Create Datasource
  1. Open SharePoint Designer 2010 to your site which you want to create the page.
  2. From the Navigation pane, choose Data Sources.
  3. From the ribbon, choose Database Connection.

    DatabaseConnectionButton.jpg

  4. The Data Source Properties dialog appears.

    DataSourceConnection.jpg

  5. Click Configure Database Connection.
  6. From the Connect to Database Server page, enter the Server information and Authentication, or select to "Use custom connection string".

    ConnectToDatabaseServer.jpg

  7. Click Next.
  8. Choose the Database, Select a table or view, or specify custom commands.

    SelectDatabaseView.jpg

  9. Click Finish.  You are returned to the Data Source Properties dialog.

    DataSourceProperties.jpg

  10. By default, all fields are selected.  If you want to remove some, click the Fields button.
  11. If you want to apply a filter or sort, click the Filter or Sort button.
  12. Click OK.  The new data source has been created.
Create Page
  1. Create a web part page and save it to a library. 

    Note:  You cannot create Dataview web parts on publishing pages.  Instead, you will need to create a Web Part Page and add the web part to the new page. 
    1. From the Navigation pane, select All Files.
    2. Open the Pages library.
    3. All pages are displayed.
    4. From the ribbon, choose Web Part Page.  Choose the layout you want.

      NewASPX.jpg

  2. Click the newly created page to open its Settings pages for it.
  3. Under the Customization section, click Edit file.

    EditFile.jpg
Create Dataview Web Part
  1. With the page open in Split or Design mode, from the ribbon, choose Insert, Data View.  Under the Database Connections section, choose your new Data Source.

    InsertDataView.jpg

  2. A web part appears with a table that shows a few fields from the data source.

  3. Customize the fields, sorting, additional filters, and paging by using the ribbon in the Data View Tools section on the Options tab.  (Note:  The Data View web part must be highlighted for the correct ribbon menu to appear.)

    DataViewToolsOptions.jpg

    For my page, I did the following:

    • Sorted on Last Name, then First Name.
    • Created Conditional Formatting to shade odd rows with a grey background.
    • Set Paging to show 100 items per page.

  4. Now, I want users to be able to filter and sort on the column headers.  From the ribbon, choose the Data View Tools, Design tab.  Check Sort & Filter on Headers.

    SortFilteronColumnHeaders.jpg

  5. I also want the filters to appear on top of the column headers.  From the ribbon, choose the Data View Tools, Design tab.  Click Options, Grouping Toolbar.

    GroupingToolbar.jpg

  6. A new toolbar appears above your column headings.  (When the Filter button is clicked, it will show filter options above each column header.  When you click on the column header, it will sort in ascending order.  If you hover over the column header, you can perform a filter.)

    FilterToolbar.jpg

  7. Now, I do not want the Filter button, Sort by or Group by options to display, but I do want the filter options above each column header to appear.  This requires going into the Code view and commenting out a line of code.
  8. Either switch to Code view, or click in the Code pane of the Split view.
  9. Press Ctrl+F to perform a search.  Search for:

    <xsl:if test="$dvt_adhocmode = 'filter'" ddwrt:cf_ignore="1">

    FindCode.jpg

  10. Once this line of code is found, delete the line of code.


  11. Scroll down until you find the ending </xsl:if> tag and delete it.

    EndCommentCode.jpg

  12. When you preview the page, the filter options are enabled by default and the user does not need to click the Filter button.

    FilterChoices.jpg

  13. Now, I do not want the Hide Filter Choices, Sort by or Group by options showing. 
  14. Click inside each cell and delete the contents.  The filter choices still appear, but those options are gone.
  15. Now, I want to change both the column headers and the filter headers to be more descriptive and not use the database field names.
  16. To change the column headers, click inside the column header cell and manually change the text.  (Sometimes, I will change it in the Code view because it's faster.)
  17. To change the filter headers, scroll to the bottom of the code.  Just a few rows up (above the ending </table> tag), you will see code to display the headers.  The section in the Code pane will look something like this:

    ChangeColumnHeaders.jpg

  18. Change both the fieldtitle to the name you want.
  19. Scroll up and change the other fields if you want.
  20. Save the page.  You are all done!
If you are using publishing:

Once your Data View web part looks and acts like you want, you will need to export the web part and import onto your publishing page.

January 18
External Data Type Field Will Not Group In View

I have a list with an External Data Type field (goes to BCS connection to a SQL database view)​.

When I try to group by that column, it shows as an empty field. You can see below that the Requestor field has data, but the grouping field, which is the same field, does not.

RequestorEmpty.jpg

You can create a calculated column to show the External Data Type field.  (I created the new, "Requestor Name" field as a calcuated field.) Then, this field will group correctly. 

RequestorName.jpg

September 22
Calculated Date Doesn't Display Correct Date

We have a calendar.  We want a list to show only the mm/dd format (no time or year).  

I created a calculated column for =TEXT([Start Date],"MM/DD").  Seems easy, right?  NO!  The dates for all day events are coming out a day off.

I found this forum​ post about it.​ 

Basically, dates are stored in the SQL content database in GMT format so the time zone settings for the site or user can be adjusted.

Here's the formula to use so that it will properly adjust the time:

=IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",IF([Start Time]=ROUND([Start Time],0),[Start Time],DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])+1)),[Start Time])​

I modified this just a tad to include the formatting of the date to mm/dd.

=TEXT(IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",IF([Start Time]=ROUND([Start Time],0),[Start Time],DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])+1)),[Start Time])​​,"MM/DD")
September 08
Add List Search Box "Find an Item" To a Page

​In SharePoint 2013, the "Search this list" scope was removed and the "Find an item" search box was added to the list toolbar.

FindanItem.jpg

But, what if you add the list web part to a page and want to search just the list?

Well, when you add the web part, there is a property in the Miscellaneous section of the Web Part Properties pane, named "Display search box". When you check this option, the "Find an item" search box appears!  (You don't even need to show the list toolbar.)

Miscellaneous.jpg




August 19
What "Web Stuff" Turned Out to Be for Me

Check out my article​ on IT Unity's Women in Technology Community page.​ 

July 15
Using "Substring-After" in SharePoint Designer DataView

I have the need to display data from another site (and different site collection) on a page.  The data from the master site collection/site is pulled into a custom SQL database for additional usage.  This data is from a People Picker, so SQL renders it as "206;#Erin Glenn".  (The ID is the ID of the user in the site collection user information table.)  

In SharePoint Designer 2010, you can connect to a SQL datasource, which I did.  Since the name fields come across as text with the ID;#Name (example: 206;#Erin Glenn), I needed to remove the ID, semicolon and number symbol.

Here's the syntax:

Original:  <xsl:value-of select="@Manager" />

New: <xsl:value-of select="substring-after(@Manager,'#')" />

Where @Manager is the field name and '#' is the character to look after for the string.


July 06
Convert Field Text To Upper or Lower Case in InfoPath 2010

​I needed a concatenated field to be set to all lower case letters in an InfoPath 2010 form.

I found this blog and it lead me to creating a rule on the field (which already has a concatenate formula for the default value).

  1. ​Once the field has been created and is the one you want converted, add the following rule:

    Condition = "None-Run when field changes"
    Rule type = Action
    Run these actions = Set a field's value

  2. In the Rule Details dialog, Field field, select the same field as you are applying the rule.

  3. In the Value field, click the Fx (formula) button and enter the following:

    translate(., "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz")

  4. Click OK.
  5. Click OK out of the Rule Details dialog.
Additional Conversions

Convert To UPPER case:
translate(., "abcdefghijklmnopqrstuvwyxz", "ABCDEFGHIJKLMNOPQRSTUVWYXZ")

Convert to lower case:
translate(., "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz")

Convert first letter to UPPER case:
concat(substring(translate(., "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 1), substring(., 2, string-length(.) - 1))

June 05
People Picker Empty in Datasheet View

In SharePoint 2010, I have a list with existing entries. We use the datasheet view to update a column.  

When we attempted to update this column and save the record (by moving to another row), we were presented with the error, "​The text you entered for Instructor isn't an item from the list. Select an item from the list, or enter text that matches one of the listed items."  ("Instructor" is the people picker field, but wasn't the field being updated.)

In datasheet, when I clicked the drop-down arrow to select the name in the Instructor field, there were no choices. I even tried on a new row.

InstructorField.jpg

So, I attempted changing the settings on the column. That didn't work.

I found this blog post that states you have to open the column in SharePoint Designer and click OK (no changes are made).  Here are the steps:

  1. Open the offending site in SharePoint Designer.
  2. From the Navigation pane, click Lists and Libraries.
  3. From the list of list names, click the offending list name.
  4. From the List Information details page, under the Customization section, click Edit list columns.
  5. Double-click the offending column name.
  6. From the Column Editor dialog, click OK. (Do not make any changes.)
  7. From the SharePoint Designer toolbar, click Save (to save the site).
  8. Refresh the datasheet and the picker now has choices!


May 22
Remove User From Site Collection List SP 2013/O365

I needed to remove an external user from an O365 site collection because they gave me the wrong email address to use for their account.  

Unfortunately, there is no UI link to get this page. Instead, you need to append yoru site URL with ​/_layouts/people.aspx?MembershipGroupId=0 or /_layouts/15/people.aspx?MembershipGroupId=0.  This works for SharePoint 2013 as well.

Example:  https://myO365site.sharepoint.com/sites/site1/_layouts/people.aspx?MembershipGroupID=0

May 20
All Day Event on Calendar Does Not Return as Same Date in Calculated Column

I have a standard calendar list and want to display one item where the Start Time=[Today].  If it's an all day event, it doesn't work (it thinks it's a day before), but an item with a custom start and end time do work.

So, I thought I would create a calculated column to see what the actual date (no time) is returning as from the Start Time column.  This proved that the Start Time of an all day event shows as the previous date.

I found this forum post that explained it.

Here's the incredibly complicated calculated column formula that gets around the issue!

=IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",IF([Start Time]=ROUND([Start Time],0),[Start Time],DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])+1)),[Start Time])​

​Excerpt from the forum with explanation:

...when creating an all day event, the calculate column based on the Start Time/End Time is different from the original column value. The date is stored in SQL in GMT time and is displayed in SP in the correct time in lists. So returning the start time in a calculated column for an all day event returns the start time in GMT time, which is not the current time most likely.

This is a confirmed issue, as a workaround, I would suggest you to regenerate the calculate column to add one day/minus one day based on whether the event is an all day event, or create a workflow to copy the start time column to your created date/time column.

To generate the calculate column, instead of using =text[Start Time], use the following format:

IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",IF([Start Time]=ROUND([Start Time],0),[Start Time],DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])+1)),[Start Time])

If you choose to create a workflow, please copy the start time column to the created column that just displays the date which will show the correct date, which in the calculated column always showed the previous/out day of the all day event."
1 - 10Next