0

I was given a spreadsheet with the names of 85 departments. They want a list where each department will have 3 text boxes to be filled in with a numeric value. Example: enter image description here

As you can tell when you do the math, I will have to create 255 columns. I refuse to do that, so with the help of stackexchange, I created code using JSOM that allowed me to create the columns dynamically. Here's my problem, not all the columns were created. When I ran the code, maybe about 75 columns or so were missing and they were not the same 75 or so every time I recreated the columns. Here's part image of what was generated showing the missing columns. enter image description here

As you can see, there are several gaps. When I console.log() the error, here's what I received. Request failed. The specified program requires a newer version of Windows. (Exception from HRESULT: 0x8007047E)

Here's the code that you can even run yourself to test.

departMentNames=[];

function getColumnNames() {
//Department Test contains all 85 department names
    var endPointUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbyTitle('Department Test')/items?$select=Title,ID";

    jQuery.ajax({
        url: endPointUrl,
        type: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: function (data) {
        //alert(data);
            detailComplete(data); // Returns JSON collection of the results
        },
        error: function (err) {
            alert("Error Occured:" + JSON.stringify(err));
        }
    });
}


function detailComplete(data) {
for (var i=0; i<data.d.results.length; i++)
{
    departMentNames.push(data.d.results[i].Title.replace(/[^A-Z0-9]/ig, ""));
}

var gsf = ["Before", "After", "Delta"];
var newFieldNames =[];
var z=0;

//Create the 255 fields in the list
for(var x=0; x<departMentNames.length; x++) {
    for(var y=0; y<gsf.length; y++) {
            addFields(departMentNames[x]+gsf[y]);
    }
}
}

getColumnNames();

function addFields(NewField) {
var clientContext = new SP.ClientContext.get_current();
var oWebsite = clientContext.get_web();
//Getting reference to the list where I will create 255 columns
oList = clientContext.get_web().get_lists().getByTitle('Test 2');

// Get filed collection
var fldCollection = oList.get_fields();

var f1 = clientContext.castTo(fldCollection.addFieldAsXml('<Field Type="Text" DisplayName="'+NewField+'" Name="'+NewField+'" />', true, SP.AddFieldOptions.addToDefaultContentType), SP.FieldText);
console.log("About to set the field");

f1.update();
f1.set_title(NewField);
//f1.set_description("sample desc");

clientContext.executeQueryAsync(
    Function.createDelegate(this, this.onQuerySucceeded),
    Function.createDelegate(this, this.onQueryFailed)
    );
}

function onQuerySucceeded() {
console.log("List Field Updated");
}

function onQueryFailed(sender, args) {
console.log('Request failed. ' + args.get_message() + '\n' + 
args.get_stackTrace());
}

Does anyone know why all the columns aren't being created?

Thanks for any help you can provide.

2

I feel like these requirements can be solved in a simpler way without the use of code. Why not have a column called "Department Name", make it a choice field with all of your department choices. Then 3 Single Line of Text columns, call them "Before", "Delta", and "After". Add 85 items to the list, each item representing a different department.

  • I agree with you but the problem is, the client wants to see all 85 on the screen. I know, it's insane. – JustMe Jul 5 '17 at 20:32
  • 1
    If that's the case, I would modify the Item Limit on your default view to show all 85 items. You aren't limited to the top 30. – Spawnbroker Jul 5 '17 at 20:38
  • 2
    Also, OP states that they should have numeric values... so... why not make them Number fields instead of Text? And then you can make the Delta field a calculated column that calculates the difference between Before and After automatically... – Dylan Cristy Jul 5 '17 at 20:41
  • I see where you all are going with this. It makes sense. One slight detail I accidentally left out. So there will be hundreds of projects and each project will need what I described above. How do you think I can set that up? – JustMe Jul 5 '17 at 21:07
  • 1
    @JustMe does one project belong to just one Department? If so, I would probably just create another column for Project. That way you can filter by Department and see all projects for that Department, or filter by Project to see a specific set of projects (no matter what department they belong to) next to each other, etc. If multiple departments are working on the same project, that's a different story, and you may want to think of a different way to organize the data. If all 85 departments work on every project, you may want to have a separate list per project. – Dylan Cristy Jul 6 '17 at 13:32

Your Answer

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

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