0

So I have gotten to the point in one of my projects where I realize it would be so much easier to have a simpler data structure than what I am actually working with.

When I get the data with:

_api/web/lists/getbytitle('MockMorningReport')/items?$select=User/Id,User/Title,Department,Monday,MondayStatus,MondayLocation,Tuesday,TuesdayStatus,TuesdayLocation,Wednesday,WednesdayStatus,WednesdayLocation,Thursday,ThursdayStatus,ThursdayLocation,Friday,FridayStatus,FridayLocation&$expand=User

The data is returned in the following object array structure:

Department: "IT"
Friday: "2021-05-28T04:00:00Z"
FridayLocation: "Office"
FridayStatus: "P"
Monday: "2021-05-24T04:00:00Z"
MondayLocation: "Office"
MondayStatus: "P"
Thursday: "2021-05-27T04:00:00Z"
ThursdayLocation: "Office"
ThursdayStatus: "P"
Tuesday: "2021-05-25T04:00:00Z"
TuesdayLocation: "Office"
TuesdayStatus: "P"
User: {
Id: 1
Title: "Shap Dev"
}
Wednesday: "2021-05-26T04:00:00Z"
WednesdayLocation: "Office"
WednesdayStatus: "P"

To make it easier on myself and the application I am building I want to try and change the structure of the data to something like so:

var data = [{
    Department: "IT",
    Employee: "Shap Dev",
    Days: [{
      Day: "Monday",
      Date: "06/21/2021",
      Status: "P",
      Location: "Office",
    },{
      Day: "Tuesday",
      Date: "06/22/2021",
      Status: "P",
      Location: "Office",
    }]
  }

This is something I have never performed before, so I have absolutely no idea what to do.

UPDATE:

I cannot get the original data or the transformed results to display/log in the console. Here is the code:

function loadData() { 
    // creating a variable for the list API url
        var uri = "Sharepoint list url";

            $.ajax({ //initializing and defining my AJAX call
                url: uri,
                method: "GET", //GET method to pull the data from the SharePoint list API above
                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(); //if the data.d.results being pulled from the AJAX is neither null nor undefined, then it will initialize the table and add rowdata to the table.
                        table.rows.add(data.d.results).draw();
                    }
                }
            });
         }

        $(document).ready(function() { 

            var daysOfTheWeek = [
                "Monday",
                "Tuesday",
                "Wednesday",
                "Thursday",
                "Friday"
            ];

            var transformedResults = [];

            //loop through the sharepoint list items and apply your custom data transformations

            data.d.results.forEach(function (spItem) {
                var newDataObject = {
                    Department: spItem.Department,
                    Employee: spItem.User.Title,
                    Days: []
                }

                //loop through the days of the week to build the new individual day data
                daysOfTheWeek.forEach(function (dayName) {
                    
                    // set up your property keys that have the prefix of the
                    // day name, but all have the same suffix

                    var dateKey = dayName; //will be "Monday" etc.
                    var statusKey = dayName + "Status"; //will be "MondayStatus" etc.
                    var locationKey = dayName + "Location"; //will be "MondayLocation" etc.

                    //change the date format from ISO format to short date string
                    var transformedDate = new Date(spItem[dateKey]).toLocaleDateString();

                    var newDayData = {
                        Day: dayName,
                        Date: transformedDate,
                        Status: spItem[statusKey],
                        Location: spItem[locationKey]
                    }

                    newDataObject.Days.push(newDayData);
                });

                //after looping through the days to build the individual day data. add the employee data object to the new array of transformed data
                transformedResults.push(newDataObject);

            });
            // here you can use your transformedResults array
            // with the data in the new format
            console.log(newDataObject);

            var table = $('#myTable').DataTable({ //initializing the DataTable
            "responsive": true,
            "scrollY": "400px", //enabling the vertical scrolling if the table reaches more than 400px on the Y-axis. This is to prevent the page from being way too long
            "scrollCollapse": true,
            "paging": false, //disable the pagination
            "columns": [ //here is where I define all of my columns with the data I GOT from my AJAX call, and their relevance to the <th> in my HTML
                {"data": "Department", visible: false, orderable: false},
                { "data": "User.Title", orderable: false},
                { "data": "Monday", visible: false, orderable: false},
                { "data": "MondayStatus", orderable: false},
                { "data": "Tuesday", visible: false, orderable: false},
                {"data": "TuesdayStatus", orderable: false},
                { "data": "Wednesday", visible: false, orderable: false},
                {"data": "WednesdayStatus", orderable: false},
                { "data": "Thursday", visible: false, orderable: false},
                {"data": "ThursdayStatus", orderable: false},
                { "data": "Friday", visible: false, orderable: false},
                {"data": "FridayStatus", orderable: false}
            ],
        })
    })
1
  • Responding to your edit: 1) I don't see where you call loadData() to actually start the process and make the AJAX call to get the data from SharePoint, and 2) you put all my data transformation code inside $(document).ready(), but the data is not going to be immediately available in $(document).ready(), you have to get it from SharePoint first. So you have to put the transformation code somewhere where it will run only after the data is returned from SharePoint, so maybe inside the AJAX success handler? Or in another function that you call from the success handler? Jun 28 at 16:44
1

Technically this question should be posted on StackOverflow, because this is a JavaScript programming question that doesn't really have to do with SharePoint. Yes, you are getting some results from SharePoint, but what you are talking about is just transforming one JSON data structure into a different JSON data structure.

That being said... here's what you could do. You can take advantage of the fact that you can access JSON properties by either the usual dot notation:

dataObject.PropertyName

or by using bracket notation:

dataObject["PropertyName"]  // gives the same value as dataObject.PropertyName

The interesting thing to note about the bracket notation is that you use a string to specify the property name you want to access. That means that you can actually use a variable instead:

var propName = "PropertyName"

dataObject[propName]  // gives the same value as dataObject.PropertyName

So, since you can use a variable, you can change the value of that variable to change which property you are trying to access:

var propName = "FirstName"

dataObject[propName]  // will give the value of dataObject.FirstName

// change the variable
propName = "LastName"

dataObject[propName]  // now this gives the value of dataObject.LastName

Do you see where I'm going with this? Because your SharePoint column names all follow the same pattern, like {day name}Status, {day name}Location, you can loop through a list of the day names to build property accessor keys to access the right properties from the SharePoint item for each of the days, and transform the data into your new individual day format that you want:

var daysOfTheWeek = [
    "Monday",
    "Tuesday",
    "Wednesday",
    "Thursday",
    "Friday"
];

var transformedResults = [];

// loop through the sharepoint list items and
// apply your custom data transformations
sharepointResponse.d.results.forEach(function (spItem) {
    
    var newDataObject = {
        Department: spItem.Department,
        Employee: spItem.User.Title,
        Days: []
    };

    // loop through the days of the week to build
    // the new individual day data
    daysOfTheWeek.forEach(function (dayName) {

        // set up your property keys that have the prefix of the
        // day name, but all have the same suffix
        //
        var dateKey = dayName;                  // will be "Monday" etc
        var statusKey = dayName + "Status"      // will be "MondayStatus" etc
        var locationKey = dayName + "Location"  // will be "MondayLocation" etc

        // change the date format from ISO format to short date string
        var transformedDate = new Date(spItem[dateKey]).toLocaleDateString();

        var newDayData = {
            Day: dayName,
            Date: transformedDate,
            Status: spItem[statusKey],
            Location: spItem[locationKey]
        }

        newDataObject.Days.push(newDayData);
    });

    // after looping through the days to build the
    // individual day data, add the employee data
    // object to the new array of transformed data
    transformedResults.push(newDataObject);
});

// here you can use your transformedResults array
// with the data in the new format
10
  • Please see my most recent edit
    – BeerusDev
    Jun 28 at 15:25
  • Dylan, thank you so much for your answer. I am aware this isn't necessarily a JS related site, but I got downvote bombed on SO main site. One last thing, I was able to restructure the data like that and mimicked it in my fiddle here with a dummy data set. The reason I did this is so I could shorten my code by quite a lot so I don't have a million if statements (90 lines worth to be exact). But here: jsfiddle.net/BeerusDev/exzqys93/75 on lines 196-217 (I should be able to condense those 90 lines to 20 with a switch), it tells me "Days is not defined"
    – BeerusDev
    Jun 29 at 20:31
  • Looking at that fiddle, first, I would change the updateCounter function and the switch inside to be function updateCounter(counter, Day) { and switch (Day.Status) {, because with that function you are looking at a single "day", so having the variable named Days (plural) is confusing. Second, Days is undefined because you need to access it through the data variable. That foreach one-liner should be data.Days.foreach(Day => updateCounter(counter, Day)); Jun 29 at 21:31
  • So I did the following and it works (jsfiddle.net/BeerusDev/gm10rqp7/43) except my counter isn't actually getting updated?
    – BeerusDev
    Jun 30 at 13:45
  • I tried to use debugger; to debug why it isn't working properly, but it won't debut that function for some reason. It does everything outside of it but won't pickup that function
    – BeerusDev
    Jun 30 at 13:52

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.