Category: Calculated Columns, Filter, SharePoint Reminder Comments:0 ;
Category: SharePoint TeamTime, SharePoint Timesheets Comments:0 ;
For more advanced custom reports then you can always run reports on the raw data to the tool of your choice using the Extensibility lists.
An example of where you may want to do this is if you want assign a different hourly rate per Project or Task (TeamTime only has the option of 1 rate per user).
One option is to use Microsoft Excel and this walkthrough will show you how to do that.
Category: Calculated Columns Comments:0 ;
A fairly common SharePoint question is how to deal with the Yes/No field type (technically known as Boolean fields) when using Calculated Columns.
Typically you may want to do something like change Yes/No to something else for display purposes – for example if the fields is for Important you could put “VIP” or blank in a View instead.
What does NOT work?
If we assume the Yes/No field is called “Important” then here are some of the formula that WILL NOT work
The reason is that you’re working with a Boolean field under the hood so TRUE is a special key word and TRUE is not the same as “TRUE” which is a string with the letters T, R, U and E in.
(I show my wife examples like this when she wants to know why computer programmers are so pedantic!)
These also fail :-
So what does work?
So what you can use instead is (note NO quotes!)
The opposite is of course
=IF([Important]=FALSE,"Not a VIP","A VIP")
You can also use this as a shorthand for =TRUE
For more tips on calculated columns be sure to check out our handy Calculated Column Cheat Sheet and other blog posts about SharePoint calculated columns and formulas.
Tip – for View Fitlers still use Yes or No (no quotes) – this is just for calculated column formula.
Category: SharePoint Ideas, SharePoint Online / 365 / Cloud, Training Comments:3 ;
As with any technology a successfully SharePoint implementation relies just as much on the business and ‘softer’ user adoption skills as the technical aspects. Which is why I’ve always found it strange that there are huge amounts of great technical resources (books, examples, best-practices, blogs, conferences etc) but far less aimed at business and power users.
The free on-line SPBiz conference is going to balance this a little with its focus on the needs of SharePoint Business and Power Users. There is still some great technical content in there but the focus is on empowering the less technical audience with resources to get things done and make SharePoint a success.
Just some of the sessions at SPBiz!
- Create a GTD Dashboard in Office 365 to “Get Things Done”
- Want to use SharePoint for internal communications?
- Secrets of Successful SharePoint Intranets
- How to build a knowledge sharing culture
- Strategies to Deliver Successful Employee Portals
- Leverage SharePoint 2013 for Better Collaborative Project Management
- How to be productive with Office 365
- Change business processes into easy SharePoint workflows
- Find out about the best “no-code” solutions for SharePoint
- SharePoint Governance 101
June 17th and 18th – Free and Online.
Category: PivotPoint Web Part, SharePoint Planner, SharePoint TeamTime Comments:0 ;
New versions of Planner, PivotPoint and TeamTime are available that correct an incompatibility with Google Chrome v43+.
This incompatibility can cause the web part to be hidden when using the web part configuration toolpane (edit web part).
As Google Chrome is automatically updated it’s important that any customers who use Chrome update to the latest version.
You can download the latest trial version and upgrade by installing over the top – your existing settings will be saved and the License Key picked up to activate the full version.
Category: SharePoint TeamTime Comments:0 ;
Google have controversially removed a feature (showModalDialog) from Chrome v37+ that has been included in most browsers since 1994 and is still part of the HTML5 specification.
This has caused problems with lots of web applications including companies internally developed apps, ’3rd party apps’ and Microsoft products like Outlook Web Access, Dynamics and of course SharePoint.
TeamTime (our timesheet app for SharePoint) has been effected by this too as we use SharePoints built in People Picker control in the Timesheet to allow you to view other users timesheets (if you’ve got the right permissions).
With Google Chrome v37 and above it will open up the people picker and let you select an individual, but when you click OK it won’t update the main page.
This is actually due to a bug in SharePoint 2010 and you can see the same problem if you edit a normal “Person or Group” field like the Assigned To field in any task list.
Interestingly (to us nerds anyway!) it works fine if the Edit Form opens as a dialog, but won’t work if it opens up full screen (which will happen if you right click Open In New Window, click on an edit link in an email or turn of Launch forms in a dialog in List Settings > Advanced Settings).
|Edit Form opens in dialog – people picker will work OK.||Edit Form opened full screen, SharePoint 2010 people picker will not work in Google Chrome v37 or above|
Nerd Alert – The bug is in init.js, function commonShowModalDialog. At the end of the function (in the branch that runs if showModalDialog is not available) you have the line
if (window.frameElement != null)
window.fndlgClose = d
Where d is the ‘callback’ function to run when the user selects a person. The test if (window.frameElement != null) means this is only ran if its running in an iFrame – hence it working if the edit form opens up in a ‘popup’ itself.
What are my options?
- Stop using Chrome in favour of another browser.
- Temporarily use a registry hack to turn showModalDialog back on (but this will only work until the end of April 2015)
- Upgrade to SharePoint 2013 (this only happens on SharePoint 2010, not 2007 or 2013)
- Wait until either Microsoft fix this or Google come to their senses.
Category: SharePoint Reminder Comments:0 ;
A new version (1.9.6) of Reminder for SharePoint which enables date based email Alerts is available.
The main change in this version is to make it compatible with Google Chrome v37. Controversially Google removed a feature (showModalDialog) that has been included in most browsers since 1994 and is still part of the latest HTML5 specification.
We’ve also added
- Improved compatibility with custom master pages or other controls that use jQuery and the ability stop reminder including jQuery on a page that already has it. (options under the Support tab).
- Reduced storage requirements for state data to support large lists
- Configurable Web Part processing timeouts (for large lists)
- A correction to an error that occurred if you use List and View name with angle brackets in the title.
To upgrade simply download and install the latest trial version – your license key will be picked up during the upgrade and all your settings will be saved.
Warning - Once you’ve upgraded to v1.9.6 or above you can’t rollback to a version older than 1.9.6.
Category: Filter, FilterPoint Web Part, SharePoint Development Comments:3 ;
It’s a common requirement to be able to do wildcard free text searches on SharePoint lists.
Whilst you can’t do wildcard or complex Boolean search (that’s what the search system is for) you can get close with a contains search. Thus searching for “Acme” will return “ACME Construction” and “Big ACME Rentals” but not “Rent-A-Crane”
It’s not easy though, and does require SharePoint Designer and a Filter Provider such as our FilterPoint product.
Filter Providers and Filter Consumers.
In SharePoint you have
- Filter Providers (like FilterPoint or SharePoint Enterprises own filter web parts)
- Filter Consumers (like the List View web part or other web parts such as our PivotPoint).
The Filter Provider sends the filter you’ve entered (e.g. “Customer” and “Acme”) but it’s entirely up to the Filter Consumer what it does with that filter.
This means that if you want to do things like Date Ranges, wildcard filtering using “Contains” operations you have to set those options on the Filter Consumer.
Using “Contains” filters to do free text search in FilterPoint.
We’re going to use SharePoint Designer to setup Parameters and Filters in the List View Web Part (LVWP) so we can do wildcard type filtering.
(It’s a limitation of SharePoint that you can’t do this without SharePoint Designer – sorry!)
First go to the web part page that has your list (or create a new, empty web part page and add your list to it).
In our example we’ve got a list of Customers with the customer name in the Title field.
- Open this page in SharePoint Designers edit view.
(Depending on what type of page this is will invoice different steps – see these tips for more help on using SharePoint Designer or the online manual for SharePoint Designer)
- Once in the edit view, click anywhere List, then click on the Parameters icon on the Ribbon (under Options)
- If you’re using SharePoint Designer 2013 you will have to select the List View web part differently as the design view has been removed – follow the instructions and then continue
- In the Data View Parameters window :-
- Click New Parameter,
- In the Name box put Customer_Search.
- (You can use whatever name you want here but it needs to be consistent across the next few steps, also note an underscore, no spaces!)
- Change Parameter Source to Form
- Change Form Field to Customer_Search
- Click OK
- Now select Filter from the Ribbon (top left of the edit page view) to open the Filter Criteria window
- The field name you want to filter on – in our case the customers’ names are in the Title field.
- Set the Comparison operator to Contains (this means that searching for ACME will return “ACME Corporation “or “Big ACME rentals” etc.)
- Set Value to be the parameter we created above – Customer_Search.
- Click OK
We’re done with SharePoint Designer (SPD) now so you can hit save and close SPD.
Open the page you’ve been working with in the browser – notice that the list won’t be displaying any results as we’re not passing in anything to our Contains Parameter.
Edit the page and add the Filter Point web part (or another FilterProvider web part of your choosing)
- Select the Web Part menu (button on top right highlighted) and then Connections > Provide Filter To > Customers (or whatever your list is called.
- On the next dialog box select Get Parameters From and then Finish
- Right – we’ve added a Parameter and a Contains Filter to the List view and connected the FitlerPoint web part to the List View, nearly there!
- Next open FilterPoint’s configuration toolpane
- Select New and enter a label such as Customer
- Set the Filter Style to “Free Text”
- Set the Field Name to From the connected web part and choose the parameter that you set above – in our case Customer_Search.
- Click OK and you’re finally done.
(For more info on configuring FilterPoint see the manual)
Searching for “acme” or “rent” will now return any customer that has that string in their name :-
A couple of points to note :-
- You can’t do complex searches like “acme AND rent” or “Rent*Crane” – just Contains or Begins With searches.
- If you want to setup additional filters then once you’ve connected the web parts in “Get Parameters” mode (rather than the normal “Get Filter Values” mode) you will have to repeat the above steps to create Parameters and Filters in the List for each filter. It’s a pain but a limitation of SharePoint.
- If you do add additional Parameters and Filters be sure to set the And/Or operator correctly – for most use cases you will want And.
Category: Filter, FilterPoint Web Part, General, SharePoint Development, SharePoint Ideas Comments:6 ;
SharePoint Designer is especially useful for things like setting up complex filters and parameters for List View web parts like ‘wildcard’ searches and ‘before or after a date’ filters.
In SharePoint Designer 2013 (SPD 2013) the “Design View” was (controversially) removed so now it’s a little harder to use as you have to wade through some X/HTML soup. There is also a bit of a wicked bug that’s going to trip you up as well so in this post I am going to give a few tips on how get to the Parameters and Filters options in the ribbon menu.
Note –this post is NOT a tutorial on how to use SharePoint Designer (its complex and worth a whole book in itself) but just a few tips for those already familiar with using SPD on how to do use the 2013 version. It won’t put back all you’ve lost, just allow you to access the Parameter & Filter options.
Missing Designer View and finding the List View Web Part
When you edit a page the most obvious difference in SPD 2013 is that you don’t get a Design View (the WYSIWYG view that allows you to see how the page will look whilst editing it). Instead you’ve only got the code view and you’re going to have to work through some angle bracket gibberish!
You’re looking for the code that defines the list view – it starts with <WebPartPages:XsltListViewWebPart and ends with </WebPartPages:XsltListViewWebPart> (pink in the pic here).
But save your eyes! Just search (CTRL+F) for XsltListViewWebPart! If the cursor is between the two XsltListViewWebPart tags then this is the same as clicking on the List in the old Design / Split view.
(If there are more than one List View on the page you will have to figure out which is which by the position of the web part in the page or look for the URL listed in TitleUrl, DetailLink or Url)
Getting the List View Tools options to show in the Ribbon
After putting the text cursor (or clicking) in the code that makes up the List View web part the ribbon should show the List View Tools option.
Except… there is a wicked bug in SPD 2013 at the time of writing that means the Ribbon doesn’t update to show the Options menu (unless you’ve turned off Server Rendering).
|The trick (given to me by MdMazzotti) is to right click on XsltListViewWebPart then select Tag Properties and then click OK without clicking anythingYou will now have access to the List View Tools > Options ribbon and the Filter and Parameters button (see picture at the top of this page)|
You can view these steps in a video :-
Category: PivotPoint Web Part, SharePoint Ideas Comments:1 ;
Our PivotPoint web part for SharePoint allows you to summarise data from your SharePoint lists in a similar way to Excel’s Pivot Tables and Charts.
A few users have asked how you can add highlighting or conditional formatting to the Pivot Tables – to do things like highlighting the High Risk column, or any cell above a certain value.
If you’re looking for an easy way to add conditional formatting/ highlighting for SharePoint Lists then check out Highlighter.
For SharePoint 2010 and 2007 you have to use the Content Editor web part (CEWP)
A much better way is to save the script as a .txt file in a document library and then include the file on the page with the Content Editors “Content Link” feature. Christophe walks you through this method here.
jQuery or not?
You could adapt these ideas to run without jQuery if you like – but you’re on your own with that!
You can see the reference to jQuery on the first line of the example scripts.
One thing you should determine is
a) Has jQuery already been added to all your SharePoint pages (by an administrator adding them into a custom master page)? If so then you should remove the jQuery reference from these examples as there is no point in including it twice.
b) These jQuery files are loaded from Googles CDN servers. If you don’t have external internet access you will have to copy jQuery to some other location (such as a document library).
Show me the examples already!
Download and unpack these zip files - inside you will see
.htm files that you can experiment with.
Open them in a browser to see what they look like. Open them in a text editor like Notepad to change how they look and then re-load in the browser (F5).
.js.txt files that you can use in SharePoint.
The two examples are :-
Highlighting PivotPoint cells based on the value
The first example we’re going to look at is to highlight different cells depending on the value that’s in them.
For our example –
- If it’s less than or equal to 160 then we want the text in red
- If it’s more than or equal to 180 then it should have a background in yellow.
The result should be a web part like this.
Highlighting PivotPoint based on the row and column
In this example we’re looking to highlight a cell based on what Row and Column it appears in.
E.g. in this risk matrix we want to highlight the “High Risk” and “Frequent Probability” in red.
The first is to highlight based on the position, e.g.
Will highlight the cell in column 0, row 0 by setting the background red. (Yes sorry, the rows and columns start from number 0!).
But – if the layout of your table changes in future (e.g. someone adds a “Super Extra High Risk”) column) then everything will move and this will be in the wrong place.
In that case the better way is to highlight by the value of the Column and Row headers – e.g.
- You’re not just limited to setting colours, you can also add in any other effect possible in CSS.
- Use the developer toolbar (F12 in IE and Chrome) if you have an error – this tool will enable you to track it down, but how to use it is beyond the scope of what we can show here!
.css('border','1px solid red');
- You can also add images (e.g. flags)