I needed to return a date in a SQL query using the Nintex Form SQL Request Control. I can do this via a simple SQL query. However, the returned format is:
10/31/2016 12:00:00 AM
I need it to be 10/31/2016.
Yes, I can create a calculated control to format this SQL Request Control, but then I have to "hide" the control and only display the calculated control. What a pain!
I found that I can convert the date field in the SQL query to the format I need!
SQL query before:
SQL query after:
Select convert (varchar, EnrollmentStartDate, 101) AS EnrollmentStartDate
Both the Value and Display field in the SQL Request Control settings are "EnrollmentStartDate".
Here's the reference
to the date formats used in the Convert command. (The number referenced after the field.)
I want to hide the green "plus" button at the bottom of an inline editing view on a list. In fact, I really want to use this view in a Nintex List View control. So, even if I remove the icon via SharePoint Designer, the Nintex List View control still renders it. (I know...odd!)
I found this forum post with a simple jquery/CSS solution that worked perfectly! Just add this code into a Content Editor Web Part on the page (hide the web part).
Use this Powershell script to run a specific workflow on a specific list:
# URL of the Site
$web = Get-SPWeb -Identity "https://sharepointsrv/site1"
$manager = $web.Site.WorkFlowManager
# Name of the list
$list = $web.Lists["Shared Documents"]
# Name of the Workflow
$assoc = $list.WorkflowAssociations.GetAssociationByName("On Item Created","en-US")
$data = $assoc.AssociationData
$items = $list.Items
foreach($item in $items)
$wf = $manager.StartWorkFlow($item,$assoc,$data,$true)
I used to have to make a change to every list item (using Datasheet view) to force the "on update run the workflow". However, sometimes this doesn't work if the item doesn't have all required fields completed.
I have a grouping view that is using an external content type as the grouped field. This field is not being displayed:
I had to create a calculated column that pulled in the value of the external content type column, then I could group on the new calculated column.
On my SP 2010 farm, I was trying to create a new best bet on my intranet site collection.
Here were my steps:
I could have sworn I've done this a hundred times!!!
- From the Manage Keywords page, I clicked "Add keyword".
- In the Keyword Phrase field, I typed "Core Values".
- In the Synonym field, I typed "mission;vision".
- In the Best Bets section, I clicked Add Best Bet.
- On the Add Best Bet dialog, I selected Add new best bet and entered the URL and Title and clicked OK.
- Back on the Add Keyword page, I clicked OK and received this error:
I found on this forum post
that if you add the keyword without the synonyms, you don't receive this error. You can go back and edit the keyword and add the synonyms.
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")