0

I am trying to call a value from a List into a cell in a table on a different SharePoint page. The table is composed using html, and I think that is where I'm having trouble trying to connect the dots. The code for the particular cell is:

<td style="background-color:Black; color:White; text-align:center; font-weight:bold; border-style:inset"> Business Days</td>

The value in the List is a numeric value with I need displayed before the word Days in the cell. I have about 17 cells that I will need to set up, but if I can figure out one, I can work on the rest.

*ETA I'm thinking this would be a code snippet that I can insert in the for that cell:

<td style="background-color:Black; color:White; text-align:center; font- 
weight:bold; border-style:inset"><div id="domesticshippingDays">   
</div>&nbspDays</td> 

I'm at a loss how to get the value into the "domesticshippingDays" variable.

SharePoint 2013, List name: CurrentTimeFrame, Row 0 name: domesticshipping, Row 1 name: overallshipping, Column name: Days (internal name 'c3t9')

Thanks in advance for any help!

List preview:

enter image description here

<table cellspacing="0" cellpadding="0" width="100%" align="center" border="0">



<thead>
      <tr style="height:70px">
      <th rowspan="2" colspan="9" style="width:30%; background-color:#015A78; 
color:White; text-align:center; font-size:35pt; font-weight:bold; border- 
width:thick; border-style:inset">Current Time Frames<br> <span 
style="color:Red; font-size:25pt;">*second custom header*</span></th>
       </tr>
 </thead>
 <tbody>

       <tr>
      <td colspan="9" style="font-size:5pt">&nbsp;</td>
      </tr>
      <tr style="height:40px">
      <td colspan="4" style="width:30%; background-color:#015A78; color:White; 
text-align:left; font-size:20pt; font-weight:bold; border-width:thick; border- 
style:inset">Shipping</td>
      <td></td>
      <td colspan="4" style="width:30%; background-color:#015A78; color:White; 
text-align:left; font-size:20pt; font-weight:bold; border-width:thick; border- 
style:inset">Grading</td>
      </tr>


      <tr>
      <td colspan="3" style="width:30%; background-color:#015A78; color:White; 
text-align:center; font-size:20pt; font-weight:bold; border-width:thick; 
border- 
style:inset">Domestic</td>
      <td style="background-color:Black; color:White; text-align:center; font- 
weight:bold; border-style:inset"><div id="domesticShippingDays"></div></td>
      <td></td>


      <td colspan="3" style="width:30%; background-color:#015A78; color:White; 
text-align:center; font-size:20pt; font-weight:bold; border-width:thick; 
border- 
style:inset">Overall</td>

      <td style="background-color:Black; color:White; text-align:center; font- 
weight:bold; border-style:inset"><div id="canadaShippingDays"></div></td>
      </tr>

 </tbody>
</table> 




<script type="text/javascript">

$(document).ready(function () {


    var timeFrameRequestUrl = _spPageContextInfo.webAbsoluteUrl + 
 "/_api/web/lists/getbytitle('CurrentTimeFrame')/items";


    $.ajax({
        url: timeFrameRequestUrl,
        method: "GET",
        headers: {
            accept: "application/json;odata=verbose"
        }
    }).done(function (response) {

        response.d.results.forEach(function (listItem) {

            if (listItem.Title === "domesticshipping") {
               $("#domesticShippingDays").text(listItem.c3t9 + " Business 
Days");
            }
            if (listItem.Title === "canadashipping") {
               $("#canadaShippingDays").text(listItem.c3t9 + " Business 
Days");
           }


        });
    }).fail(function () {
        alert("Oops! Something went wrong.");
    })
})
</script>
2
  • When you say "Row 0 name: shipping", do you mean the value of the Title field for that row is "shipping"? – Dylan Cristy Jan 8 '20 at 19:57
  • Correct. The very first column is the title row which is named shipping. – mehumphry Jan 8 '20 at 20:04
0

Ok, so there are two parts to this:

  1. Get the data from the SharePoint list
  2. Put that data where you want it to go in your table

For retrieving the data from the list, I would recommend looking into using the REST API from Javascript code you put on the page where your table is, because then you will have the data in a place where you can easily then turn around and insert it into your table. I'm not going to go into too much detail here about how to do that, there are tons and tons of resources out there to help you figure it out (just google for "sharepoint rest api"), and the details of how that code would be written has entirely to do with which Javascript library you decide to use to help make the HTTP requests. But for your example, you're probably going to want to put together a query like

/_api/web/lists/getbytitle('CurrentTimeFrame')/items?$filter=Title eq 'shipping'&$select=Days

Now, for the second part, you are right that you'll have to add a little more HTML into your table, but I don't think you want to add inputs. An input will give you a text box that looks like you should be able to enter data into it. You just need something to display the data in a read-only way (unless I'm completely misunderstanding what you are trying to do), so really all you need is an element that you can add an id to, so that you can find it from your Javascript code. I would just add a div into your table cell:

<td style="background-color:Black; color:White; text-align:center; font-weight:bold; border-style:inset">
    <div id="shippingDays"></div>
</td>

Then when you get the data from the list, you can do

var shippingDaysDiv = document.getElementById("shippingDays");
shippingDaysDiv.innerText = retrievedDays + " Days";

As far as making sure the data in the table is refreshed, you won't really need to worry about that, because you'll set it up so the Javascript code runs every time the page is loaded, so anytime anyone goes to that page, they will always be seeing the newest data because the code dynamically goes out and retrieves the data from the list at that time.


In response to your question in the comments about which Javascript library to use, here are some suggestions:

One of the easiest to use, I think, is jQuery. You will definitely find plenty of examples of using jQuery's $.ajax() function to call SharePoint's REST API. The downside is that you have to include the whole jQuery library just to be able to use $.ajax(). That may or may not be a concern for you. Also, you may end up having to use jQuery if you have to support Internet Explorer. Oh, but an upside to using jQuery would be that you can use it to help you make inserting the data that you retrieve into the table a little easier, jQuery is very good for DOM manipulation.

If you don't have to support Internet Explorer, you could try using the native ES6 Fetch API. fetch() is built-in to modern Javascript implementations (hence - not in IE), so you won't actually have to include an extra library. If you're going to eventually be making SharePoint web parts using SPFx, I would go with fetch() because the tools provided in SPFx I believe are just wrappers for fetch(). At least their syntax is very similar.

If you can get away with supporting only IE 11 from the IE family, another option to consider would be axios. Axios syntax for making queries is very similar to jQuery $.ajax(), so it is pretty easy to understand, but the responses are a bit different, you just have to play around with it a little bit (and read the documentation) to see what's going on there and how to use it.


Based on your update with the code you have tried, here's what I see: a lot of room for improvement, which I can't fully get into here. Sorry if that seems a little harsh, but it is the truth. I'll throw out a brief list of some of the things I see that could improve, but I'm only going to get into the details of what needs to be improved in order to address the crux of your original question - how to get data from a list and insert it into a table.

  1. Depending on how you are adding that HTML to a SharePoint page, you might not need the outer <html> tags or the <head> and <body> tags. I would go so far as to say you most likely don't but the reasons for that are off topic here.
  2. All your inline styles will be much easier to manage if you extract them into CSS classes and use classes on the table elements instead.
  3. You are loading jQuery twice, which is unnecessary. You first load version 1.4.2 from jquery.com and then you immediately override that by loading version 1.11.3 from googleapis.com, which means technically you have downgraded the version of jQuery you are using. I know that SPServices has a minimum version of jQuery you need to use, but that does not mean you need to use exactly that version. I would load the latest version of jQuery, and only do it once.
  4. You are using SPServices. There's nothing inherently wrong with SPServices, it's a very helpful library, but if you are on any version of SharePoint beyond 2010, it's probably easier to just query the REST API directly. Even if you did still want to use SPServices, from the looks of your code, you have some problems with how you have that set up too.
  5. From the looks of your code, it seems like you are trying to append rows to your table, instead of inserting the data directly into a table cell. Not only that, it looks like you are trying to insert a two column row into a nine column table.

I would go back to what I had suggested before: setting yourself up with placeholder divs that have unique IDs that you can use to locate them later. I am assuming the places you want to insert the data you get from the list are here

<td style="background-color:Black; color:White; text-align:center; font-weight:bold; border-style:inset">Domesticshipping &nbspBusiness Days</td>

and here

<td style="background-color:Black; color:White; text-align:center; font-weight:bold; border-style:inset">Canadashipping Business Days</td>

So here's how I would set up that row of the table. I am omitting all the styling to make it a bit easier to see what's going on with the HTML itself:

<tr>
    <td colspan="3">Domestic</td>
    <td><div id="domesticShippingDays"></div></td>
    <td></td>
    <td colspan="3">Canada</td>
    <td><div id="canadaShippingDays"></div></td>
</tr>

Then, in the script block I would do this:

<script type="text/javascript">
    // this tells jQuery to wait until the page has loaded before executing
    // the inner code.  this way we know the table has been rendered and we
    // will be able to find the placehoder divs
    $(document).ready(function () {

        // _spPageContextInfo is a built-in SharePoint javascript object
        // that has quite a bit of useful info.  in this case we're using
        // it to get the URL of the current site as the basis of the REST
        // query we will build.  as far as the REST query itself, it seems
        // that you do not want _only_ the row that has the domestic shipping
        // days, but you want the row that has the canada shipping days info
        // as well, so we are just going to go ahead and ask the list for _all_ items

        var timeFrameRequestUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('CurrentTimeFrame')/items";

        // now we make the query
        $.ajax({
            url: timeFrameRequestUrl,
            method: "GET",
            headers: {
                accept: "application/json;odata=verbose"
            }
        }).done(function (response) {
            // the done function fires once the request succeeds
            // and the response has been received from the server.
            // since the query was for _all_ the list items, they
            // will be in an array called "results" that is on a property
            // called "d" which is part of the regular SharePoint REST response.
            response.d.results.forEach(function (listItem) {
                // we are iterating over each list item, so we can check to
                // see what the value of the Title field is, and take action accordingly
                if (listItem.Title === "domesticshipping") {
                    // here we grab the placeholder div by using it's ID
                    // and insert the text we want in it, including the number of days
                    // in the "days" column (internal name "c3t9")
                    $("#domesticShippingDays").text(listItem.c3t9 + " Business Days");
                }
                // you only showed a screenshot of what the domestic shipping
                // title column value is, so i'm just guessing at this. needless
                // to say, you should be checking for what the actual value is
                if (listItem.Title === "canadashipping") {
                    $("#canadaShippingDays").text(listItem.c3t9 + " Business Days");
                }
            });
        }).fail(function () {
            // the fail function will only fire if the request fails.
            // you can look up the documentation of this to see how you
            // can get more information about exactly what error occurred.
            alert("Oops! Something went wrong.");
        })
    })
</script>

I would also forewarn you that the weakness of this setup is that you are relying on the values of the Title fields, which can be easily edited and changed. If someone at some point goes in to the CurrentTimeFrame list and changes "domesticshipping" to "Domestic Shipping", the code will break because

if (listItem.Title === "domesticshipping")

will never find the correct list item to get the days from. Unless you go in and update your code to reflect the new field value like

if (listItem.Title === "Domestic Shipping")

As you can imagine, having to go in and update your code every time someone edits the Title of one of those list items will get annoying really quickly.

16
  • Thank you so much for the direction to go! I'll do some research into the REST API this weekend and give it a try! – mehumphry Jan 9 '20 at 12:57
  • Can you recommend a Javascript library that you like to use best? I was trained in C++, so I'm just learning java on my own. I don't know if there is a better library than another. Thanks again! – mehumphry Jan 10 '20 at 18:53
  • @mehumphry updated my answer with some suggestions. – Dylan Cristy Jan 10 '20 at 19:51
  • That does help a lot! I do have to support IE, so I'll def look into JQuery. Thank you! – mehumphry Jan 10 '20 at 21:50
  • Well, I've tried to figure something out, but still can't quite get it. Can you possibly look at the new code entered above and see if you can point me in the right direction of what I'm doing wrong? I've tried to update the info in the post. I need to get the numerical value from the column 'days' (that has an internal name of 'c3t9'). – mehumphry Jan 21 '20 at 14:24

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.