1

I am pulling items from a SharePoint list to populate into a DataTable. One of those items I am pulling through the request is a Date & Time column called WeekOf.

When it posts to the DataTable, it is formatted like so "2021-01-24T05:00:00Z". I have looked through libraries such as Moment.js(deprecated), Days.js, and Luxon.js. After some research I came to the realization that I should use a library if I don't have to.

I then came across https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/parse

After some research of that, I found what I thought to be a solution which was

(new Date(Date.parse(WeekOf))).toLocaleDateString();

This should post 2021-01-24 instead of 2021-01-24T05:00:00Z. I implemented it into my code, and then several errors arose in my console saying

Uncaught ReferenceError: WeekOf is not defined at loadData

Here is my code:

function loadData() { //Initializing the AJAX Request function to load in the external list data from different subsites
        //create an array of urls to run through the ajax request instead of having to do multiple AJAX Requests
        var fullUrl = "/_api/web/lists/getbytitle('WeeklyReport')/items?$select=Deliverables,Billable,Non-Billable,Tasks,Actions,SupportRequest,ResourceRequest,Team,Training,Upcoming,WeekOf";

            $.ajax({
                url: fullUrl,
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose"
                },
                success: function(data) { // success function which will then execute "GETTING" the data to post it to a object array (data.value)
                    console.log(data);
                    if (data.d != null && data.d != undefined && data.d.results.length > 0) {
                        var table = $('#myTable').DataTable();
                        table.rows.add(data.d.results).draw();
                    }
                }
            });
        (new Date(Date.parse(WeekOf))).toLocaleDateString();     
    }
    
    $(document).ready(function() {
        var collapsedGroups = {};
        var top = '';
        var parent = '';
    
        var table = $('#myTable').DataTable({
        "pageLength" : 50,
            "columns": [{
                    "data": "WeekOf",
                    visible: false
                },
                {
                    "data": "Team",
                    visible: false
                },
                {
                    "data": "Tasks"
                },
                {
                    "data": "Deliverables"
                },
                {
                    "data": "Actions"
                },
                {
                    "data": "Billable"
                },
                {
                    "data": "Non-Billable"
                },
                {
                    "data": "Upcoming"
                },
                {
                    "data": "Training"
                },
                {
                    "data": "ResourceRequest"
                },
                {
                    "data": "SupportRequest"
                }

            ],
    
            dom: "<'row'<'col-sm-12 col-md-10'f><'col-sm-12 col-md-2'B>>" +
                "<'row'<'col-sm-12'tr>>" +
                "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
            buttons: [{
                extend: 'collection',
                className: "btn-dark",
                text: 'Export Report',
                buttons: [{
                        extend: "pdf",
                        className: "btn-dark"
                    },
                    {
                        extend: "print",
                        className: "btn-dark"
                    },
    ],
            }],
            order: [
                [0, 'asc'],
                [1, 'asc']
            ],
            rowGroup: {
                dataSrc: [
                    'WeekOf',
                    'Team'
                ],
                startRender: function(rows, group, level) {
                    var all;
                    if (level === 0) {
                        top = group;
                        all = group;
                    } else if (level === 1) {
                        parent = top + group;
                        all = parent;
                        // if parent collapsed, nothing to do
                        if (!collapsedGroups[top]) {
                            return;
                        }
                    } else {
                        // if parent collapsed, nothing to do
                        if (!collapsedGroups[parent]) {
                            return;
                        }
                        all = top + parent + group;
                    }
    
                    var collapsed = !collapsedGroups[all];
                    console.log('collapsed:', collapsed);
    
                    rows.nodes().each(function(r) {
                        r.style.display = collapsed ? 'none' : '';
                    });
                    //Add category name to the <tr>.
                    return $('<tr/>')
                        .append('<td colspan="8">' + group + ' (' + rows.count() + ')</td>')
                        .attr('data-name', all)
                        .toggleClass('collapsed', collapsed);
    
    
                }
    
            }
        });
    
        loadData();
    
        $('#myTable tbody').on('click', 'tr.dtrg-start', function() {
            var name = $(this).data('name');
            collapsedGroups[name] = !collapsedGroups[name];
            table.draw(false);
        });

    });
3

There are several things to discuss here. First, the "2021-01-24T05:00:00Z" date format is what is known as "ISO formatted". It's what you would get if you did

new Date().toIsoString()

Second, ISO formatted strings are already handled by the Date constructor, so there is no real reason you need to jump through those hoops with new Date(Date.parse("2021-01-24T05:00:00Z")). You can just do

new Date("2021-01-24T05:00:00Z").toLocaleDateString()
// output:
"1/24/2021"

Now, as far as your error messages, it's because you are trying to reference WeekOf as if it were an independent variable, which it's not, it's going to be a property on the objects returned by your query to SharePoint. And not only that, you are trying to reference it before you even get the results back from SharePoint. So clearly, at the time your line of code is executed, WeekOf is definitely not defined. Let's look at your loadData() function:

function loadData() {

    var fullUrl = "/_api/web/lists/......";

    // here you START your asynchronous AJAX call to get the data
    $.ajax({
        url: fullUrl,
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function(data) {

            // this function is not called until AFTER your AJAX request
            // returns some data - this takes some time.
            console.log(data);
            if (data.d != null && data.d != undefined && data.d.results.length > 0) {
                var table = $('#myTable').DataTable();
                table.rows.add(data.d.results).draw();
            }
        }
    });

    // this next line is executed immediately after your
    // AJAX request is _sent_ - this is not waiting for
    // the data to get back from the server.  if you
    // wanted it to wait, this would have to be _inside_
    // the "success" function.
    //
    // in any case, there is nowhere that you define
    // what the value of "WeekOf" is below - it seems like
    // a disconected variable, you are not referencing it
    // as a property of one of the data objects returned
    // from the AJAX call (which, again, wouldn't be available
    // here, it would have to be _inside_ the "success" function)
    (new Date(Date.parse(WeekOf))).toLocaleDateString();     
}

Now, there are a couple of ways to do what you want to do. One is, you could loop through the data returned from SharePoint and transform all your date strings to the format you want before you send it into DataTables, OR, you can set up the transformation in DataTables so it will automatically apply the transformation to the data as it's rendering it.

If you want to do it the first way, you would have to set up your success function like this:

success: function(data) {
    console.log(data);
    if (data.d != null && data.d != undefined && data.d.results.length > 0) {

        // loop through all the results
        for (var idx = 0; idx <data.d.results.length; idx++) {
            // turn the ISO string into a Date
            var weekOfDate = new Date(data.d.results[idx].WeekOf);
            // reformat the string and store it back to the "WeekOf"
            // property you are trying to replace
            data.d.results[idx].WeekOf = weekOfDate.toLocaleDateString();
        }

        // now that you've changed all the date formats, load it into your datatable
        var table = $('#myTable').DataTable();
        table.rows.add(data.d.results).draw();
    }
}

OR, if you wanted to do it the second way, you could leave your success function as you have it, and change the part where you set up your data table columns. If you look at the DataTables documentation, there is a render property as part of the column options, which will allow you to define a function that will be called for every data element that goes into that column. You can specify your text formatting transformation there.

var table = $('#myTable').DataTable({
    "pageLength" : 50,
    "columns": [{
            "data": "WeekOf",
            visible: false,
            "render": function (data) {
                // "data" here is just going to be the
                // ISO formatted string from the "WeekOf"
                // field, so do basically the same thing
                return new Date(data).toLocaleDateString();
            }
        },
        {
            "data": "Team",
            visible: false
        },
        // ... etc ...
    ]
    // ... etc ...
});

The one thing I'm not sure about with the second option is that you call $('#myTable').DataTable() with all your options in the $(document).ready() function, but then you call it again with no options from your success handler, and I don't remember if DataTables is smart enough to know you previously set up a DataTable on that element, and give you back the instance you previously set up (which would preserve your render function), or if calling $('#myTable').DataTable() a second time from your success handler is going to squash whatever you had set up before and therefore erase the custom render function you set up for WeekOf.

1
  • So, when I try the second solution. Nothing is affected by the render, likely due to what you have stated. But after fooling around with the first solution, I was able to complete what I needed to complete. Much appreciated Dylan. Thumbs up and accepting your answer
    – BeerusDev
    Jan 27 at 20:37

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.