Possible workaround for date format problem

I noticed that a lot of the search results I was getting when Googling for Sharepoint information were ion Wordpress so I started a Sharepoint blog over there.  I found a workaround for the issue with date formats and posted it on my new blog.

I'll parallel run for a bit and see which one I prefer then mothball the other.

More problems with dates, 2010 this time

Run into yet another problem with dates, but on Sharepoint 2010 this time.

Basically it's the same old thing of wanting to display different alerters based on the relative positions of CurrentDate and a date field in an entry.  A straight comparison of is current date less than, greater than or equal to a date field is no problem, easily done in a filter or conditional formatting filter.  The problem comes when we want to find out if CurrentDate is X number of days before or after the date field.

For example a Task list has a Due Date field on each entry.  If CurrentDate is at least 5 days before DueDate we want a green flag, if it's less than 5 days before or after DueDate we want an Amber flag and it it's 5 or more days after Due date we want a red flag.  So if DueDate (dd/MM/yyyy) is 15/07/2011 we want:

Up to 10/07/2011 = Green
11/07/2011 to 19/07/2011 = Amber
20/07/2011 or later = Red

We found a template that looks like it should do this on an MSDN blog site but that doesn't seem to work properly.  From what we can tell it seems that if either DueDate or CurrentDate can be interpreted as a legal US format (MM/dd/yyyy) date (e.g. 08/07/2011, 8th July 2011 but interpreted as 7th August 2011) then that is how it will be interpreted, if it cannot (e.g. 30/06/2011) it will use UK/International format.

From that page there is a link to a different way of getting the difference between two dates which is for Sharepoint 2007.  I've posted a comment asking if that will work in 2010 as well.  We shall see!

I've also been asked to look at if it's possible to just subtract two dates from each other in 2010.  Worth a look maybe (you can't in 2007, so far as we know).

Edited to add (12:10 08/07/2011) Found another blog entry on a related issue that discusses the issues with using dates in conditional formatting, although that seems to be just comparing CurrentDate with a field.  A couple of the comments do ask about comparing with dates +/- CurrentDate.

Edited to add (14:51 08/07/2011) Now asked a question about this on EggheadCafe.  It looks like the same issue of US vs UK date format affects just doing straight comparison's of dates in Conditional formatting.  We didn't have this problem on the Sharepoint 2007 server so presumably it's either a difference betweenthe two servers or between Sharepoint 2007 and 2010.

Edited to add (15:58 08/07/2011) Found a useful site the LCID (locale identifiers) in Sharepoint. Bascially the number you use, say, in the second parameter of FormatDateTime tells it which locale format to use.  So, 1033 is US so it will format dates as MM/dd/yyy but 2057 is UK so dates will be formatted as dd/MM/yyyy.  Doesn't solve the problem but could be useful to know in the future.

Edited to add (16:29 08/07/2011) Looks like I'm not the only one with this problem, found someone's blog entry about the inconstent interpretation of dates.  Unfortunately his solution won't solve my problem (different problem just impacted by same underlying issue).

Edited to add (13:57 12/07/2011): Posted a question about this on Go 4 Sharepoint : http://www.go4sharepoint.com/Forum/inconsistent-interpretation-dates-us-vs-23644.aspx

We're having the Locale setting on the servers checked as that's the only thing left really. 

User updates list item but no blank entry in append field

A user threw up an interesting issue with this today. When a particular user updates an entry on a list with a append field, but doesn't put anything in the append field, the version history shows a blank entry but it doesn't appear when anyone views that entry in the field itself.  Anyone else it does appear.

Weird!

Googled it but only one relevant link came back.  This really just confirmed that a when you update a list entry that has an append multi-line field then if you don't put anything in the append field it puts in a blank entry automatically.  A version history entry is created showing the blank entry but nothing appears in the main view ofd the entry.

ETA:

Posted question about this to EggHeadCafe.

Can a variable in one web part be accessed by another web part?

Asked this question on Egghead Cafe.  Had one response so far, haven't tested it out yet.

The original problem we've now solved by using a spdatasource object.

Sharepoint 2007 - Pulling multiple data elements from a list

One of the things you can do in SharePoint 2010 is pull multiple data elements through from a list as part of a look up.  Say you have a list which contains projects and records project name, project manager, project sponsor &c.  In a 2007 lookup field you can pull through one element only so you can have project name OR project manager OR project sponsor, but not two or more of them.  In 2010 it is possible to have a look up that will pull through multiple elements.  E.G. you select the project name and it will pull through the corresponding project manager data.

To achieve this in SharePoint 2007 you need to create a workflow and use the 'set' functionality to query the data and insert it intot he appropriate field for each element.

Date filter in XSLT views for [Current Date] Plus or Minus a certain number of days

Creating a filter on a date field where you want to filter on the [Today] plus or minus a set number of days (e.g. in the past 14 days) in the web front end is pretty easy.  The problem comes when trying to achieve that in designer for an XSLT view.  There you can apparently only filter on [Current Date].  A work aroudn for this is to create the view initially in the web front end then load in Designer and convert to XSLT.

If you then view the filter in Designer it shows only [Current Date] but still works with the Plus or Minus.  The downfall is that if you then subsequently edit the filter (e.g. to add other clauses) the date parts start filtering just on [Current Date], they lose the Plus or Minus.  To avoid this before making any changes select the split view then select the Webpart in the 'Design' pane.  In the Code pane the related code will be highlighted.  Look for a line near the start which begins

<code>&lt;SharePoint:SPDataSource runat="server" DataSourceMode="List" SelectCommand=</code>

  Read this line and look for a section that reads something like:

<code>Type=&quot;DateTime&quot;&gt;&lt;Today OffsetDays=&quot;+14&quot;/&gt;&lt;/Value&gt</code>

copy the section following the word 'Today' up to the next slash (/) and paste into another program (e.g. Notepad).  In the above example you would copy out :

<code>OffsetDays=&quot;+14&quot;</code>

If there are multiple date filters you will need to do this for each filter.  Then edit the filter in the GUI (add clauses, edit non-date clauses &c).  Once this has been done go back to the code pane, you will see that the OffsetDays has been removed.  In the above example it would now be:

<code>Type=&quot;DateTime&quot;&gt;&lt;Today /&gt;&lt;/Value&gt</code>

Copy and paste back in the text and the filter should now work correctly.

Error message when you try to edit an Office document in a document library: "'Edit Document' requires a Windows SharePoint Services-compatible application"

Had an email from a colleague about a user having the  'Edit Document' requires a Windows SharePoint Services-compatible application error tryign to open and Excel file from sharepoint 2010.  Below is my response (names and some other details have been removed to protect the guilty and maintain confidentiality):

we had something similar last year, but bear in mind that our Sharepoint site is 2007 rather than 2010.  Solution was to install and enable the Sharepoint support addon for IE.  For more details see Microsoft Knowledgebase article 833714 (http://support.microsoft.com/kb/833714/en-gb),

I did a bit of research from home last night.  There are a few different possible causes:

• MS Office is either not installed or is a broken install (typically the person installing decided to leave some things out or some parts of the install were removed to free up space or resolve an issue)
• Trying to use a browser other than IE (e.g. Firefox, Safari or Chrome) to access Sharepoint (there is a plugin for Firefox).  I note from the screen shot that XXXX is using Firefox.
• The sharepoint site is not a trusted site in IE.
• Using IE 9 Release Candidate (i.e. post-Beta, ironically the Beta worked but one of the fixes when Microsoft moved it from Beta to RC broke sharepoint integration)
• Using IE as an embedded browser in [Mail Client] (depending on your settings if you click a link in [Mail Client] it either opens a separate browser window or opens the browser in a tab in [Mail Client], this latter causes a whole heap of problems so people should really set [Mail Client] to open the browser in a new window)

As XXXX is using Firefox I'd recommend she try again in IE, hopefully that will resolve the problem.

It appears (http://msdn.microsoft.com/en-us/library/ff407576.aspx) that the Sharepoint 2010 Firefox plug in part of Office 2010 so to use Firefox XXXX will need to install Office 2010.

If XXXX still cannot open the file after switching to IE I'd recommend:

• Check which version of IE she is using, if it's 9 downgrade to 8  or 6
• Check that the sharepoint site is in the trusted sites list
• Check if she can open other files and if other people can open that file (rule out it being a problem with that file)
• If all else fails, remove and re-install office.  Do a complete install to eliminate the chance that it's a problem with a vital component being left out.

Enhanced Folders

My colleague found this link on how to create enhanced folders. 

SPD 2007 won't open file but IE will, file has grown massively

We had a problem where a page would display OK in IE but if we tried to open it in Sharepoint Designer (SPD) for editing we had an error message.  We also noticed that the size had grown from a few hundred Kb to over 10Mb.  Turns out what had happened is the last time it was saved SPD had added thousands of 'blank' lines.  Apparently this is a known bug but unresolved.

The solution is to open the file as a text file (which SPD will do) and remove all the blank lines.  Note that few of the lines are truly blank (i.e., just a \n) but often have multiple tab characters in them so a blank line removal function probably will not work.  We found the only way to remove them reliably was to do so manually.

Setting permissions on a folder differently to the document library that contains it.

Had a support call come in that someone had lost access to a document library.  Turned out that what had happened was that one of my colleagues had been asked to make a folder in the document library 'secure', accessible only to a restricted group of people.  What he had done was to edit the permissions for the whole document library.

This request is actually pretty unusual for the sites we're running, We have been asked for 'secure' document libraries before but not to 'secure' a folder within a document library.

Securing a document library is done by selecting "Document Library Settings" from the Settings menu when in the Document Library then Manage Permissions  from the settings page that appears.

To do so for a folder first navigate to the parent folder of the folder you want to secure, so if the folder is DocLib\Parent\Child you would navigate to DocLib\Parent.  OPenh the Edit menu for the folder you wish to secure and select Manage Permissions.  This will open the permissions page for the folder.  Unless this folder has previously had it's permissions edited the only available menu will be Actiions.  You will need to open the Action menu and select Edit Permissions as it will be inheriting it's permissions from the parent folder.  This will cause the New menu to appear and will break the link between the permissions for the parent folder and those for this folder.  You can then add and remove users and groups via athe New and Actions menus.  If you change you mind you can also choose to return to inheriting the permissions from the parent folder, from the Actions menu.

To upgrade or not upgrade

Our main site right now is on SharePoint 2007 (WSS 3.0), no MOSS or other fun stuff.  At some point it will move to SharePoint 2010.  There has been a suggestion that we do that in the next couple of months.  Our concern is that we suspect that a number of our custom developments might not survive the migration, there are somethings we can do with ease on SharePoint 2007 that simply fail if we try to do them on 2010, for example graphical alerters in place of numeric fields.  Given the number of bespoke developments we have migrating could be a very painful experience and we don't have the luxury of downtime, the sites have to be available.

I did find a forum thread on LinkedIn that discusses the move.  It seems that there's a tool (STSADM) that will pick up most of the problems you're likely to face.

The thing is, most of the sites in our site collection (including the ones with the most bespoke development) will be getting archived in March/April/May 2011.  If we wait until after this has been done there will be a lot less to go wrong and to fix.  I have recommended that we wait until then.  Yes, 2007 is very out of date but we do not currently have any problems that would be fixed by going to 2010 or a business need for any features added in 2010.  We do have a 2010 site so if a new site came along we could build that on 2010 and if a site developed a need to move to 2010 we could transfer just that site over.

Displaying Server Parameters

My colleague discovered how to display a server variable (e.g. the logged on user) on a page:

1. Insert a field from any list as a single item dataview, doesn't matter which list or which field, into your page.  We'll say you're using 'Title'
2. Create a Parameter on that view of Parameter Type 'Server Variable' and set the Server Variable Name to the name of the variable you want to use (e.g. LOGON_VARIABLE for the username of the logged on user.
3. In the page select the dataview you created then go to the Code view (or code pane in Split view).  look for a line that contains an at symbgol followd by the name of the field you chose.  If you used the title field it would look something like:
   <xsl:value-of select="@Title" />
4. Replace the @ with a $ and the field name with the name of the parameter.  If the parameter name is Param1 the line will now look like:
   <xsl:value-of select="$Param1" />
5. Save the page and view it in your browser, it should show your username.

f#

Attended a presentation on Microsoft's new programming laguage, F# (pronounced Eff-Sharp, not Eff-Hash as some of my friends thought).  It's decsribed as like a better form of C# as it's Functional rather than Imperitive and doesn't have anywhere near the amount of 'boiler plate' that you need if you're writing code in C#/Java/C++/C.  As you can write code to add dunctionality to SharePoint in C# I asked if F# could be used for that as well, the almost ksh like way you can pipe things in F# seemed ideal for processing data entities such as list items in SharePoint so I couldn't see why not.  The guy from Microsoft didn't know.

Multilevel dropdowns

Need to look at this more:  http://blog.pathtosharepoint.com/2009/01/15/a-drop-down-menu-the-sharepoint-way/

Conditional Formatting bug in SharePoint Designer 2010

We have found a bug in Conditional Formatting in SharePoint 2010.  You set up the conditional formatting logic as you normally would and it just doesn't work (e.g. if you set up a Hide condition for certain circumstances it always hides).

If you click the Advanced button and look at the XPath information you'll see that the field names have been prepended with $thisNode/ and have an extra full stop (period) at the end.  To resolve this remove the extra text and magically it works!

Using Multiviews

Will update later but just wanted to stick this up as an aide memoirw: http://www.code-magazine.com/article.aspx?quickid=0709061&page=3

Creating piecharts in SharePoint

Having recently discovered how to produce bar charts from SharePoint data we have been getting requests for Pie Charts.  Bar charts are no great shakes, just an XSLT stylesheet controlling the relative width of two cells in a table, one with a coloured background and one with a transparent background, pie charts are altogether more complex (unless you have MOSS and so Excel services, but we don't).

Initially we found an article on EndUserSharePoint on how to do this using a service from Google.  This works well but has the disadvantage that the data has to be sent to Google's servers which then return the piechart so the user has to be connected to the internet and there's a chance of data leakage.

We then found a solutions for Secure Pie Charts on SharePoint which gets around this by using a solution from Yahoo!, storing the files locally and callugn them from some Javascript.  Well worth a look, I think.

We implemented it, I got it working on Monday and one of my colleagues struggled to get it implemented on Thuresday.  We eventually got it working but only when I remembered something else we had to edit to get it working, which isn't mentioned in the original blog entry (or if it is I missed it) and is easy to miss in the Javascript.

The below assumes that you have read the  blog entry linked above, downloaded the files, unpacked them as recommended and have the Javascript that calls the files open and ready to paste into the page in SharePoint Designer.  We tested this on Sharepoint 2007/WSS3.0 using Sharepoint Designer 2007 no warranty is given or implied, try it first on a test/sandpit server and if you screw up your production or any other server following these suggestions then it's your problem not ours. 

If you paste just the Javascript into a text editor before pasting it into the page the lines you need to edit are 27 (line 27 starts "<link rel="stylesheet" type="text/css" href=") through 39 to point to the local copies of the various files.  The original blog entry recommends putting the files in a document library (create one that isn't shown on the Quick list for this purpose) then using the "Copy Shortcut" on the Right-Click menu to get the URL to paste, I would recommend following that advice.  Then do the same for line 47 (setting the jQPath variable) with the path to jquery.js. but delete the filename leaving the final forward slash (so if the full shortcut is "https://myserver.org/resources/jquery.js" the line should read "var jQPath="https://myserver.org/resources/";).  The penultimate step is to edit line 123 to change the URL for charts.swf to the one for that file on your local server.

What caught us out the second time around is line 71 which sets the value of the variable chartkeyword.  Set this to the name of the field you are grouping by in the view.

Paste the resulting code into the Content Editor Web Part (use Code view) the original blog told you to put on the page, making sure to put it between the <![CDATA and ]] tags.  You should be ready to go and able to view the pie chart in IE.

If you can't see the chart first try pasting in the Javascript unedited then just changing line 71 to the name of the grouping field.  If that doesn't work check where you've pasted the code and you've used the correct field name.  If it does work recheck the various URLs ands change them one by one to the marching ones for your own server, retesting each time.  When it stops working that's the one you got wrong.

We found that initially after pasting in the code Sharepoint Designer 2007 crashed when we clicked back in the Display pane.  If this happens to you see if Microsoft Office Diagnostics starts up and if it does let it run and fix your installation.  This happened on 3 different machines so we figure there was some patch or fix that was missing (or possibly it was because we had Sharepoint Designer 2007 installed along side Office 2003 an there was some library mismatch).  If Microsoft Office Diagnostics doesn't start up make sure you're got the latest patchsets for SharePoint Designer 2007.

The question we're looking at now is if we can control the colours of the pie chart segments.  A number of the lists we're looking to have pie chart views on are for things that have colour coded statuses and that's what we want to display (how many red, how many amber and how many green).


This could be really cool and useful.

ODBC connectivity to Sharepoint 2007

We were asked about getting an ODBC connection to Sharepoint for reporting purposes (i.e. using something like Crystal Reports or even a BI tool like Business Objects) to carry out reporting.  After some research it seems that you cannot use a generic ODBC connection to access Sharepoint, you can however use Microsoft Access to link to Sharepoint Lists and then use an ODBC connection to the Access database.  This does mean that you need to have Access on the machine and it adds an extra layer so it might be a bit slower than ODBC but it does at least give a solutions.

If the link is two way then this also opens up the opportunity to create 3rd party tools to update Sharepoint, possibly via a batch job, to keep another system in sync or to enforce business rules that Sharepoint lacks the sophistication to do.

Need to dynamically filter a calendar view

We have a list of project risks where each item (i.e. Risk) is associated with one and only one project, each item also has a proximity date (i.e. roughly when we expect the risk to hit).  We have been asked to product a report page with a calendar view showing the risks against their proximity date with a dynamic filter that will let management select which project they want to view risks for.


Were this just a normal list view, no problem.  Just convert the list view to an XSLT DVWP in SharePoint Designer (or create the DVWP from scratch), drop a datasource and drop down box on the page and feed the selection into the filter on the view.  Job done.  Unfortunately it appears that you cannot convert a calendar to an XSLT DVWP and cannot find a way to create a Calendar DVWP from scratch.

We searched but the results either had nothing to do with what we wanted to do or were in the negative.

Posted this as a question in the Microsoft Sharepoint help forums.

Linking Webparts

We have a report that is basically a bunch of dataviews in webparts pulled from various lists. Serveral of these have filters based on a text field selected via a dropdown.

All well and good until we needed to filter a dataview on fields that are not part of the dropdown. In database terms we neded to do an inline view so that the results of one query were the parameters of another. We were looking at Webpart connections as a possibility but documentation is not exactly abundant. I did find a YouTube video that explained how Webpart connections work and allowed me to work out the rest.

The process is:

1. Open the .aspx page in Sharepoint Designer (this uses Sharepoint Designer 2007, other versions may differ)
2. Create a webpart dataview to do the 'inline view'  sub-query, this only has the fields need for parameters for the filters on the main webpart dataview.  This will be referred to as the providor webpart and the main webpart dataview that displays the actual data will be referred to as the consumer webpart.
3. Set any filters required, the result of this should be to return only one row of data for each value..
4. Make sure that both webpart dataviews have a meaningful name.
5. On the providor webpart open the "Common Dataview Tasks" menu and select Webpart Connections.
6. A dialogue should open giving the name of the Providor Webpart as Source Webpart and 'Send Row of Data To' as the Source action.  The source action can be selected via a dropdown box in the lower part of the dialogue, leave it as 'Send Row of Data To' or change it to that if it has not defaulted to that.  Click Next.
7. The next step give you the option of connecting to a webpart on the same page or another, select to use a webpart on the same page and click Next.
8. You can now select the consumer web part in the Target WebPart drop down box.  In the Target Action select 'Get Parameters From'.  Click Next
9. The next step will show a two column table in the lower part of the dialogue, the left column should be named "Columns in [Name of Providor WebPart]" and the right column should be named "Inputs to [Name of Consumer WebPart]".  In the right column there will be listed any existing parameters on the Comsumer WebPart (there may be just the default one of ListID) and at the bottom <create new parameter>.  Select <create new parameter>, the normal prameter creation box should appear.
10. For each parameter you need create a parameter with a short but meaningful name.  The 'Parameter Source' drop down should be greyed out and the 'Default Value' should be blank, leave them as they are.  Once you have created your parameters click OK to return to the Webpart Connection dialogue.
11. Your parameters should now be listed in the right column.  in the left column cell against each one click to select the cell, this will cause a drop down to appear.  Open the dropdown and select the field from the Providor WebPart that you want to populate the parameter.  Repeat for each parameter then click Next twice then finish to close out the dialogue.
12. Now go to the Consumer webpart and open the Common DataView Tasks menu.  Select Filter.
13. The normal filter dialogue will open and you can create the filters as normal but instead of creating the parameters you should select the parameters that you have just created.  Click OK to save the filters.
14. You probably won't want to display the Providor webpart so select the table it is embedded in and open the Table Properties.  Set width and height to zero and click OK.  Finally, select the webpart and open WebPart Properties, open the Layout area and select hidden then OK.  The webpart will still be visible in Designer but won't be when the page is viewed in a browser.