Wednesday, November 15, 2017

Fix problem with “The formula refers to a column that does not exist” error in calculated columns for Sharepoint lists which use owssvr.dll

Sharepoint contains many hidden features which are really useful. One of them is possibility to generate iCal files (.ics) from calendar event. In order to do it you need to add calculated field to the calendar with the following formula:

   1: =”http://<SITE_URL>/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=<LIST_GUID>
   2: &CacheControl=1&ID=”&ID&”&Using=event.ics”

where instead of <SITE_URL> you should use url of web site where calendar list is located and instead of <LIST_GUID> – guid of the calendar list. You may get list guid if will go to List settings page – guid will be in browser address bar in List query string parameter. Also note that list guid should be added without curly braces because otherwise link won’t be clickable in list item view form.

This formula will work properly on English sites (and for several other languages – see below) but if you will try to use it on some other language site (e.g. on Finnish) you may get the following error:

The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column

(error message will be translated on the local language as well). The problem is related with internal representation of ID field and with fact that in formulas fields’ display names should be used instead of internal names. If we will check it e.g. using Sharepoint Manager tool there will be property called SchemaXmlWithResourceTokens which contains xml definition of ID field. It will look similar to this:

   1: <Field ID="..."
   2:        ColName="tp_ID"
   3:        RowOrdinal="0"
   4:        ReadOnly="TRUE"
   5:        Type="Counter"
   6:        Name="ID"
   7:        PrimaryKey="TRUE"
   8:        DisplayName="$Resources:core,ID;"
   9:        SourceID="http://schemas.microsoft.com/sharepoint/v3"
  10:        StaticName="ID"
  11:        FromBaseType="TRUE" />

Pay attention of DisplayName attribute of the field: for ID (which is internal field which exists for all list items) it is retrieved from core.resx file, i.e. it is localized as well as other fields. So e.g. for Finnish language (DisplayName=Tunnus) formula will look like this:

   1: =”http://<SITE_URL>/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=<LIST_GUID>
   2: &CacheControl=1&ID=”&Tunnus&”&Using=event.ics”

I checked core.resx for all languages available for Sharepoint 2013 and found the following languages which will have the same problem: Catalan (ca-ES), Bulgarian (bg-BG), Greek (el-GR), Basque (eu-ES), Hebrew (he-IL), Hungarian (hu-HU), Dutch (nl-nl) uses “Id” instead of “ID”, Polish (pl-PL), Russian (ru-RU), Turkish (tr-TR), Ukranian (uk-UA), Chinese Taiwan (zh-TW). Other languages use “ID” display name for ID field.

Saturday, November 4, 2017

Problem with reusing taxonomy terms with navigation settings in different term sets in Sharepoint

Suppose that we have the following sub sites in the same Sharepoint Online site collection:

  • /en – publishing site
  • /content/en –authoring site

And we need to use the same managed metadata navigation on these publishing and authoring sub sites. In order to achieve that we need to create 2 navigation term sets (e.g. Navigation.en-US and NavigationContent.en-US) and configure navigation settings for the terms:

We can’t use same term set for 2 sub sites because Sharepoint allows to use navigation term set for single site only. As we need to create 2 term sets anyway we would like at least to reuse terms from 1st term set Navigation.en-US in 2nd term set NavigationContent.en-US like it is shown on the picture above (in order to have less maintenance work). And here we face with the problem: it seems like that during reusing navigation settings of the terms are not reused. I.e. if we create terms in 1st term set Navigation.en-US, then reuse them in 2nd term set NavigationContent.en-US and then configure navigation settings in Navigation.en-US – reused terms in NavigationContent.en-US won’t inherit changed navigation settings automatically as we would expect.

Workaround for this problem is quite simple: at first configure navigation settings in source term set (Navigation.en-US) and only after that reuse terms in 2nd term set (NavigationContent.en-US). In this case navigation settings will be inherited properly. But if you will change navigation settings after that in original terms from the source term set – they won’t be changed automatically in 2nd term set. I.e. you will need to change them in 2nd term set explicitly.