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=""
  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.

No comments:

Post a Comment