It's been quite some time since I've spoken at a conference, or even user group.
On Saturday, September 17, 2016, I'll be at SharePoint Saturday Charlotte. Here's my session:
A Peek Into Real Life Business Solutions
Track: IT Pro, End-User, Business
Have you ever wanted to see how someone else did "it", outside of standard intranet content or a document management solution? Come to this session to learn about business obstacles and how they were solved using SharePoint out-of-the-box tools, including SharePoint Designer, InfoPath, SQL Reporting Services, and custom SQL databases (for full-blown application-style solutions). Some solutions will include a "lite" learning management system, logistics tracking application, employee recognition nomination and approval, program application and attendance tracking, and many more.
All of a sudden, our enterprise search center stopped working. When you tried running a search, it would eventually timeout with a generic "server error".
ULS logs showed this error:
(Watson Reporting Cancelled) System.Net.WebException: The remote server returned an error: (503) Server Unavailable.
at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
at Microsoft.Office.Server.Search.Query.HttpAsync.RespCallback(IAsyncResult asynchronousResult)
Just below this error, followed this one:
(Watson Reporting Cancelled) System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpServerUtility.Transfer(String path)
at Microsoft.SharePoint.Utilities.SPUtility.TransferToErrorPage(String message, String linkText, String linkUrl)
at Microsoft.Office.Server.Search.WebControls.QueryUIError.GetErrorMessageOrRedirectToErrorPage(Exception ex, Boolean showMessages)
at Microsoft.Office.Server.Search.WebControls.CoreResultsDatasourceView.GetXmlResponseDoc(DataSourceSelectArguments selectArguments)
at Microsoft.Office.Server.Search.WebControls.CoreResultsWebPart.GetXPathNavigator(String viewPath)
After reading many blogs and forum posts on the first one, which all pointed towards an IIS app pool not being started (which wasn't my issue), I realized that other scopes worked, including my People scope which uses the PeopleResults.aspx page. My default scope, All Sites, uses Results.aspx. This page was not working.
Thinking about the "server unavailable" erorr in the ULS logs, I went to the Results page in Edit mode. I had an "Internet Search Results" web part which was really just a Search Core Results web part with the configuration set to internet search results. (See this Technet article
for details on setting this web part.) I removed this web part. VIOLA! Issues solved!
So, whatever site this "internet search" uses, must have been down, or no longer exists.
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
- Open SharePoint Designer 2010 to your site which you want to create the page.
- From the Navigation pane, choose Data Sources.
- From the ribbon, choose Database Connection.
- The Data Source Properties dialog appears.
- Click Configure Database Connection.
- From the Connect to Database Server page, enter the Server information and Authentication, or select to "Use custom connection string".
- Click Next.
- Choose the Database, Select a table or view, or specify custom commands.
- Click Finish. You are returned to the Data Source Properties dialog.
- By default, all fields are selected. If you want to remove some, click the Fields button.
- If you want to apply a filter or sort, click the Filter or Sort button.
- Click OK. The new data source has been created.
Create Dataview Web Part
- 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.
- From the Navigation pane, select All Files.
- Open the Pages library.
- All pages are displayed.
- From the ribbon, choose Web Part Page. Choose the layout you want.
- Click the newly created page to open its Settings pages for it.
- Under the Customization section, click Edit file.
- 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.
- A web part appears with a table that shows a few fields from the data source.
- 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.)
For my page, I did the following:
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.
- 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.
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.
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.)
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.
Either switch to Code view, or click in the Code pane of the Split view.
Press Ctrl+F to perform a search. Search for:
<xsl:if test="$dvt_adhocmode = 'filter'" ddwrt:cf_ignore="1">
Once this line of code is found, delete the line of code.
Scroll down until you find the ending </xsl:if> tag and delete it.
When you preview the page, the filter options are enabled by default and the user does not need to click the Filter button.
Now, I do not want the Hide Filter Choices, Sort by or Group by options showing.
Click inside each cell and delete the contents. The filter choices still appear, but those options are gone.
Now, I want to change both the column headers and the filter headers to be more descriptive and not use the database field names.
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.)
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:
Change both the fieldtitle to the name you want.
Scroll up and change the other fields if you want.
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.
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.
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.
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")
In SharePoint 2013, the "Search this list" scope was removed and the "Find an item" search box was added to the list toolbar.
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.)
Check out my article on IT Unity's Women in Technology Community page.
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.
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).
- 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
- In the Rule Details dialog, Field field, select the same field as you are applying the rule.
- In the Value field, click the Fx (formula) button and enter the following:
translate(., "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz")
- Click OK.
- Click OK out of the Rule Details dialog.
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))
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.
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:
- Open the offending site in SharePoint Designer.
- From the Navigation pane, click Lists and Libraries.
- From the list of list names, click the offending list name.
- From the List Information details page, under the Customization section, click Edit list columns.
- Double-click the offending column name.
- From the Column Editor dialog, click OK. (Do not make any changes.)
- From the SharePoint Designer toolbar, click Save (to save the site).
- Refresh the datasheet and the picker now has choices!