none
AddFieldAsXml doesn't work for calculated fields

    Pertanyaan

  •  Hi,

    I'm trying to generate a calculated field (MOSS 2007) from the schemaXml of another field, that looks as shown below.
    The calc field refers to another filed called "Base Num". Due to the space in the name, the internal and display name are different.

    <Field Type='Calculated' DisplayName='CalcNum' Format='DateOnly' LCID='1033' ResultType='Text' ReadOnly='TRUE' ID='{cd60edd0-4750-4ac5-b7fa-50691baf3226}' SourceID='{dfdfbdc6-25b7-45d4-8b0d-71906d519225}' StaticName='ACalcNum' Name='ACalcNum' ColName='sql_variant3' RowOrdinal='0'>
    <Formula>=Base_x0020_ Num</Formula><FieldRefs><FieldRef Name='Base_x0020_ Num' /></FieldRefs></Field>

    When I call myList.Fields.AddFieldAsXml with the above string I get exception "The formula refers to a column that does not exist",
    although Base Num does exist.

    I've seen the same exception with a similar situation: after renaming a referenced field, its internal and display name are different, and adding a calculated field that refers to it will fail with the same error.

    Please note that I don't build the the CAML string I'm using for AddFieldAsXml; it is simply copied from the schemaXml of another calc field. I would prefer not to change this string, becaue it might contain a complex formula and by processing it I might ruin it.

    Any ideas?

    Thanks in advance
    Sruli Ganor

    31 Agustus 2008 15:34

Semua Balasan

  • Any ideas on this one?  I'm having the same issue.

    I created a computed column on a task list called "Days to complete" that subtracts the due date from the start date.  I then take the SchemaXml from that field and use the formula from it to try and create a second column.  The exact same formula, "<Formula>=DueDate-StartDate</Formula>" throws a "The formula refers to a column that does not exist" exception.
    13 Februari 2009 21:58
  • I finally got this working the hard, long-winded way, but I think the trick might be to use square brackets around the field in the formula and in the FieldRef.

    This worked for me, *phew*:

                                SPList list = clientWeb.Lists["Reports"];
    
                                if (!list.Fields.ContainsField("Report Month"))
                                {
                                    string sField = list.Fields.Add("Report Month", SPFieldType.Calculated, false);
                                    list.Update();
                                }
    
                                SPField field = list.Fields["Report Month"];                            
    
    XmlDocument xDoc = new XmlDocument(); xDoc.LoadXml(field.SchemaXml); XmlNode node = xDoc.SelectSingleNode("//Formula"); node.InnerText = "=IF([Reporting Month]-9>0,[Reporting Month],\"0\"&[Reporting Month])&\" \"&TEXT(DATE([Reporting Year],[Reporting Month],[Reporting Day]),\"MMMM\")"; XmlNode fieldRefs = xDoc.CreateNode(XmlNodeType.Element, "FieldRefs", null); xDoc.ChildNodes[0].AppendChild(fieldRefs); XmlNode reportingDay = xDoc.CreateNode(XmlNodeType.Element, "FieldRef", null); XmlAttribute reportingDayName = (XmlAttribute)xDoc.CreateNode(XmlNodeType.Attribute, "Name", null); reportingDayName.InnerText = "[Reporting Day]"; reportingDay.Attributes.Append(reportingDayName); fieldRefs.AppendChild(reportingDay); XmlNode reportingYear = xDoc.CreateNode(XmlNodeType.Element, "FieldRef", null); XmlAttribute reportingYearName = (XmlAttribute)xDoc.CreateNode(XmlNodeType.Attribute, "Name", null); reportingYearName.InnerText = "[Reporting Year]"; reportingYear.Attributes.Append(reportingYearName); fieldRefs.AppendChild(reportingYear); XmlNode reportingMonth = xDoc.CreateNode(XmlNodeType.Element, "FieldRef", null); XmlAttribute reportingMonthName = (XmlAttribute)xDoc.CreateNode(XmlNodeType.Attribute, "Name", null); reportingMonthName.InnerText = "[Reporting Month]"; reportingMonth.Attributes.Append(reportingMonthName); fieldRefs.AppendChild(reportingMonth); StringBuilder sb = new StringBuilder(); StringWriter sw = new StringWriter(sb); XmlTextWriter tx = new XmlTextWriter(sw); xDoc.WriteTo(tx); field.SchemaXml = sb.ToString(); field.Update();

    (The above basically creates a Calculated column to show the Month name).

    HTH.
    03 Juli 2009 8:48
  • Hi,

    It seems like there may be some space between Base and Num specified Base_x0020_ Num. Try removing the space. If it doesn't work, then try the below xml structure - 

    <Field Type='Calculated' 
    DisplayName='CalcNum' 
    Format='DateOnly' 
    LCID='1033' 
    ResultType='Text' 
    ReadOnly='TRUE' 
    ID='{cd60edd0-4750-4ac5-b7fa-50691baf3226}' 
    StaticName='ACalcNum' 
    Name='ACalcNum' 
    RowOrdinal='0'>
    <Formula>=TEXT([Base Num])</Formula>
    <FieldRefs><FieldRef Name='Base_x0020_Num' /></FieldRefs>
    </Field>

    I have created calculated field using the below xml structure without any issue - 

    <Field ID="{2792023C-5FB9-4e9c-B136-D67F97F59FC2}"
                     Name="KPMGACTMGTStartDate"
                     Type="Calculated"
                     DisplayName="Start Date"            
                     ResultType="DateTime"
                     Format="DateOnly"
                     ReadOnly="FALSE">
                <Formula>=TEXT([Start Time],"dd/mm/yyyy")</Formula>
                <Default></Default>
                <FieldRefs>
                  <FieldRef Name="StartDate" ID="{64cd368d-2f95-4bfc-a1f9-8d4324ecb007}" />
                </FieldRefs>
              </Field>


    Prateesh Nair (MCTS) | Orion India Systems

    10 Juli 2012 16:47