Issue with a Calculated column

Question

I have in a list with multiple content types calculated column with this formula :

=if(
    [ContentTypeId]="0x0100C74070DC6A24431BAFDA7E34EB40AB71000158A3D022CBA34BBDF923656EFE684400E7789CC966C89540BAC6C7FC7FD01294";
    2;
    if(
        [ContentTypeId]="0x0100C74070DC6A24431BAFDA7E34EB40AB71003812C8B350ABA140B2F15CCBCC4762D100DD8681818759F247931F04F23B6D2A61";
        1;
        0
    )
)

[edit] To remove syntax issue, a simple formula that illustrates the issue :

=[ID du type de contenu]

The goal of the formula is to produce a value specific to the content type.

However, when I try to set up this formula, I get the error :

The formula refers to a column that does not exist

One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.

[edit] changing the formula changes the error

FYI, the column is firstly provisionned using XML :

  <Field ID="{ECE65686-1A5F-414A-9999-1E54AE611DFF}"
         Name="MyField"
         Type="Calculated"
         ResultType="Number"
         ReadOnly="TRUE"
         StaticName="MyField"
         DisplayName="My field"
         Group="MyGroup">
    <Formula>=0</Formula>
    <FieldRefs>
      <FieldRef Name="ContentTypeId" ID="{03e45e84-1992-4d42-9116-26f756012634}" />
    </FieldRefs>
  </Field>

Then, the actual formula is set by code :

var myField = myList.GetFieldByInternalName("MyField");
myField.Formula = CreateFormula(); // logic to produce the formula below
myField.Update();

Using the debugger, I can see that myField.FieldReferences actually contains the ContentTypeID column.

What is wrong with my formula?

FYI, I'm working with SP 2010 Standard

[Edit] it looks like the ContentTypeID column is not supported in formula : http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/d1ead239-1d8a-4a78-a72d-3bd2a6e4997c

Answer 1

I finally succeeded in setting up my formula. In fact, I add to face two actual issues.

The first issue is that the ContentTypeId is of type ContentTypeId which is one type that is not supported by the formula columns.

In order to solve this issue, I add to create a new column, ContentTypeIdCopy, of type text, which is the copy of the content type value. This value is set by an event receiver when the item is added.

From this point, I am able to use this copied column in my formula. However, the field is show to the user.

If I add Hidden="TRUE" to the column definition, the formula stopped to accept this column.

I can solve the issue by putting ShowInDisplayForm="FALSE" ShowInEditForm="FALSE" ShowInNewForm="FALSE" in the field definition.

Finally, as suggested Andrey Markeev, I add to be careful about the locale of the formula. In french, I need to write "SI" in place of "IF"... even if the UI accepts both.

Answer 2

I think the problem is that you're not from US or UK :) This is a known issue with calculated fields: since they use the same engine as MS Office for formulas in calculated columns, you have to use same formulas syntax, and the thing is, as you might have noticed, in Excel your formulas are localized. So you should use localized formulas: i.e. ";" instead of ",", localized function names, etc.

You can create the formula in Excel and then just put it into SharePoint, replacing cell references with field titles.

Also you can try to set Thread.CurrentThread.CurrentUILocale and/or Thread.CurrentThread.CurrentLocale to 1033 and try to keep the formula you have now though I'm not sure it will work.

I had a hard time with this issue some time ago, and as far as I can remember, I was not able to struggle through this, and ended up having to use localized versions of formulas.