Adding calculated column to existing list

Sep 19, 2011 at 5:03 PM

I have used SPServices to add fields to a list many times, but I have until now never had to add a calculated column. I am migrating some fixes to a several sites and I have to add 5 calculated columns

to a list on each site. To help speed up the process, I thought that I would write some quick code to add the columns. The problem I think is formatting the formula properly so that it will add correctly.

<script language="javascript" type="text/javascript">
$(document).ready(function() {
    var nfields = "";
    nfields += "<Fields><Method ID='1'><Field Type='Calculated' DisplayName='DayCount' Format='DateOnly' ResultType='Text' ReadOnly='TRUE' Name='DayCount'>";
    nfields += "<Formula>=DATEDIF(EventDate,EndDate,'d')</Formula>";
    nfields += "<FormulaDisplayNames>=DATEDIF([Start Time],[End Time],'d')</FormulaDisplayNames>";
    nfields += "<FieldRefs><FieldRef Name='EndDate' /><FieldRef Name='EventDate' /></FieldRefs></Field></Method></Fields>";
    $().SPServices({
        operation: 'UpdateList',
        listName: 'Calendar',
        newFields: nfields,
        completefunc: function(xData, Status) {
            tp1 = xData.responseText;
            alert(tp1);
            tp4 = tp1.indexOf("errorstring");
            if (tp4 < 0) {
                alert("Fields created!");
            }
            else {
                // Error creating fields!
                alert("Error creating fields!");
            }
        }
    });
});
</script>
This is an example of the code with just one column. I know it is trying to work based on the returned xml, but it will not accept the field.
Any thoughts?!
Sep 19, 2011 at 6:02 PM

I didn't think that you could set the values of calculated columns, well because they are derived from the vaules of other columns.  As long as you set the value of the deriving columns, the calculated columns should be available in the XML (of a GetListItems) after that update.

Sep 19, 2011 at 6:08 PM

I am not trying to set the values of the colums! I am creating the columns! The list does not yet have the columns in it and I want to add the columns to the list. I have not tried to create them without the formula but I don't know if it would work. I am not sure it is possible at all, but I can add other fields. I will keep trying though!

Sep 19, 2011 at 6:35 PM

Sorry, I guess I misunderstood ya.  I assumed add a calculated column meant add a value to. My bad.

Sep 19, 2011 at 6:40 PM

No worries Eric! Looking at the docs it is possible to do it, but I can't seem to get it to go! It has to be the formatting of the formula and formuladisplaynames options.

Sep 19, 2011 at 6:59 PM

Well, I guess you can always try configuring the column through the UI forst to make sure you have the Formula and Display formulas down before passing them through jQuery.  If you create calculated columns in the UI as listed above, do they create or throw errors?

Sep 19, 2011 at 7:08 PM

No they don't. That is how I got the formulas. I even used SharePoint Manager to check the XML and it is the same. The formula works fine, but I can't pass it in through the code. If I use the example from the docs, this works fine!

Sep 19, 2011 at 7:30 PM

For these I have narrowed the problem down to the spaces in the field names on the displayformulanames:

nfields += "<Fields><Method ID='1'><Field Type='Calculated' DisplayName='DayCount' ResultType='Text'>";
nfields += '<Formula>=DATEDIF(ADate,BDate,"d")</Formula>';
nfields += '<FormulaDisplayNames>=DATEDIF(ADate,BDate,"d")</FormulaDisplayNames>';
nfields += "<FieldRefs><FieldRef Name='ADate' /><FieldRef Name='BDate' /></FieldRefs></Field></Method></Fields>";

This works fine! So as in the first post, the fields for the display have spaces in them (ex. Start Time). I have tried using _x0020_ to replace the space, but that is not what it is looking for...

Sep 20, 2011 at 11:30 AM

Eureka! Sometimes you have to use your noodle to come up with the answer. OK, that was just a figure of speech, but the real problem was that I had the wrong formula which I knew, but I did not

know why! I was getting my formula by looking at the XML after it was already inserted which in some cases is okay, but not in others. So, I thought I would try to use the formula how you enter it

into the UI which is the same as the FormulaDisplayNames value. I think Eric might have said that but I missed it if so! Anyhow, this worked and now I can finish writing the code to update the list.

This code is supporting a fix to allow the OOB 2007 SharePoint calendar the ability to show a 5 day work week. It is complex, but it seems to work so far!

nfields += "<Fields><Method ID='1'><Field Type='Calculated' DisplayName='DayCount' Format='DateOnly' ResultType='Text' ReadOnly='TRUE' Name='DayCount'>";
    nfields += "<Formula>=DATEDIF([Start Time],[End Time],'d')</Formula>";
    nfields += "<FormulaDisplayNames>=DATEDIF([Start Time],[End Time],'d')</FormulaDisplayNames>";
    nfields += "<FieldRefs><FieldRef Name='EndDate' /><FieldRef Name='EventDate' /></FieldRefs></Field></Method></Fields>";

May 6, 2013 at 3:56 AM
I know its 2013 and this post was back in 2011... but... THANK YOU... @spevilgenius ... after much searching I found the answer to my troubles in your last post. You posting back how you got around the problem has help me and probably many others.

Paul.
Coordinator
May 6, 2013 at 4:05 AM
Great when the community helps each other!

M.