Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question
    
Similar question: sharepoint.stackexchange.com/q/59195/1430 –  Andrey Markeev Mar 1 '13 at 10:23
add comment

2 Answers 2

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.

share|improve this answer
    
Good to know, thanks for sharing the solution! –  Andrey Markeev Mar 1 '13 at 14:00
add comment

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.

share|improve this answer
    
Actually, I don't think this is a locale issue. If I put =[ContentTypeId], I get the same error :( –  Steve B Mar 1 '13 at 10:21
    
you should use column titles rather than field internal names –  Andrey Markeev Mar 1 '13 at 10:23
    
I can see that setting the formula with the column internal name is automatically translated by the setter of the formula property. My formula is converted to =[ID du type de contenu] –  Steve B Mar 1 '13 at 10:28
    
I've edited my question. It seems that the calculated column does not support all data types. –  Steve B Mar 1 '13 at 10:36
1  
Ah, you're right, calculated column supports only a very limited set of field types. Full list of them and reflector investigations, in case you're interested: sharepoint.stackexchange.com/a/38240/1430 –  Andrey Markeev Mar 1 '13 at 10:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.