5

I am trying to create some fields, a content type, a list definition and a list instance in Visual Studio. All of my fields are working, showing up in the list etc. except for calculated fields. What happens when I view the list is the calculated fields are there, but their title is blank and clicking on them causes the error:

Cannot show the value of the filter. The field may not be filterable or the number of items returned exceeds the list view threshold enforced by the administrator.

Here is an example of the markup for a working field, and a calculated field that is not working (which relies on the first field) from Elements.xml:

<Field ID="{c43a0f19-4e87-4b84-99fd-cad842b2f581}"
    Name="TestDate"
    StaticName="TestDate"
    Type="DateTime"
    DisplayName="Test Date"
    Required="TRUE"
    Format="DateOnly">
</Field>

<Field Type="Calculated"
    DisplayName="Date Month"
    Format="DateOnly"
    ResultType="Text"
    ReadOnly="TRUE"
    ID="{88e0dc85-2aac-45dc-8c1b-2e1282e58a50}"
    StaticName="DateMonth"
    Name="DateMonth">
    <Formula>=TEXT(TestDate,&quot;MMMM&quot;)</Formula>
    <FieldRefs>
        <FieldRef Name="TestDate" ID="{c43a0f19-4e87-4b84-99fd-cad842b2f581}"/>
    </FieldRefs>
</Field>

<ContentType ID="0x01005682c3d0c85948c9972ccc57915e128d"
         Name="TestList"
         Description="Test List"
         Inherits="TRUE"
         Version="0">
    <FieldRefs>
        <FieldRef ID="{c43a0f19-4e87-4b84-99fd-cad842b2f581}"
            Name="TestDate" />
        <FieldRef ID="{88e0dc85-2aac-45dc-8c1b-2e1282e58a50}"
            Name="DateMonth" />
    </FieldRefs>
</ContentType>

Am I doing something obviously wrong with my calculated field? Is something missing or invalid? I can't see why it isn't working while the other field works fine.

  • Your content type seems fine (works on mine). After deploying, can you try adding this content type to another custom list and see if you get the same error. – sssreddy Apr 19 '12 at 4:37
7

Turns out I had made a wrong assumption. I thought the formula used the internal name of the field, but it actually uses the DisplayName. So either changing DisplayName to TestDate or changing the formula to TEXT([Test Date],&quot;MMMM&quot;) will fix the problem for me.

It caught me out for so long because I had copied the fields out of an exported list template manifest.xml file. In this file, it DOES seem to use the internal name in formulas.

  • 1
    This is not true, on my danish site-collection my computed field formula had to reference internal names and not display names, when defined in a site column that when attached to a content type used in a list. – sjkp Feb 5 '14 at 16:04
  • 1
    This was not the correct answer for me! In the XML it expected the internal field names not the display names. When I look at the formula in SP the internal names were replaced by the display names, but if I try to use the display names in the XML then they will be replaced with #NAME? and the calculation doesn't work. – Daniel Gimenez Feb 1 '16 at 18:43
2

Try this :

<!-- Parent ContentType: Document (0x0101) -->
  <Field ID="{01854464-EABC-4726-A517-397A4EA224C2}" Name="dueDate"  StaticName="dueDate" DisplayName="due Date" Description="dueDate" Group="MyDoc Content Types" Type="Calculated" Format="DateOnly"  ResultType="DateTime" ReadOnly="TRUE"  >
    <Formula>=DATE(YEAR(Created),MONTH(Created),DAY(Created))</Formula>
    <Default></Default>
    <FieldRefs>
      <FieldRef Name="Created" />
    </FieldRefs>
  </Field>
  <Field ID="{8096E74E-C89C-460F-91FB-33905D39F648}" Name="RetentionPeriod" StaticName="RetentionPeriod" DisplayName="Retention Period" Description="RetentionPeriod" Group="MyDoc Content Types" Type="Choice" >
    <CHOICES>
      <CHOICE>1</CHOICE>
      <CHOICE>2</CHOICE>
      <CHOICE>3</CHOICE>
    </CHOICES>
  </Field>

  <Field ID="{A583AA0A-EFCF-4B90-80F5-369C4B3311FA}" Name="expDate" StaticName="expDate"  DisplayName="exp Date" Description="expDate" Group="MyDoc Content Types" Type="Calculated" ResultType="DateTime" Format="DateOnly" ReadOnly="TRUE" >
    <Formula>=DATE(YEAR(dueDate)+[RetentionPeriod],MONTH(dueDate),DAY(dueDate))</Formula>
       <FieldRefs>
      <FieldRef Name="dueDate" />
      <FieldRef Name="RetentionPeriod" />
    </FieldRefs>
  </Field>


  <ContentType ID="0x010100661a97f6da6a430abb92a0a4fa671998"
               Name="DocContentType"
               Group="MyDoc Content Types"
               Description="My Content Type"
               Inherits="TRUE"
               Version="0" Overwrite="TRUE">
    <FieldRefs>
      <FieldRef ID="{01854464-EABC-4726-A517-397A4EA224C2}" Name="dueDate" />
      <FieldRef ID="{8096E74E-C89C-460F-91FB-33905D39F648}" Name="RetentionPeriod" />
      <FieldRef ID="{A583AA0A-EFCF-4B90-80F5-369C4B3311FA}" Name="expDate"/>

    </FieldRefs>
  </ContentType>
</Elements>
1

user7922 is great, followed your steps and voila. We need the display name not internal names. Did my Provisioning using JavaScript JSOM.

  1. list.get_fields().addFieldAsXml('<Field DisplayName="Amount" Type="Number" Required="FALSE" Name="Amount" />', true, SP.AddFieldOptions.defaultValue);

  2. list.get_fields().addFieldAsXml('<Field Decimals="2" DisplayName="Refunded Amount" Type="Number" Name="Refunded_x0020_Amount"><Default>0</Default></Field>',true, SP.AddFieldOptions.defaultValue);

  3. list.get_fields().addFieldAsXml('<Field Type="Calculated" DisplayName="Balance" Format="DateOnly" Decimals="2" LCID="1033" ResultType="Number" ReadOnly="TRUE" Name="Balance"><Formula>=[Amount]-[Refunded Amount]</Formula><FieldRefs><FieldRef Name="Refunded_x0020_Amount" /><FieldRef Name="Amount" /></FieldRefs></Field>', true, SP.AddFieldOptions.defaultValue);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy