Create a table from an API JSON response

3 min read

Summary

I was working on a proof-of-concept for an integration piece on a Dynamics 365 CE and Finance & Operation project.

The project involved taking rows of data from D365 FO and surfacing that data as a table in D365 CE.

The architecture is as follows:

  1. Export the related tables from D365 FO using BYOD into an Azure SQL DB.
  2. Create a REST API Azure Function which will read data from the SQL DB.
  3. Create a D365 CE Webresource that queries data from the current record (Customer ID).
  4. Display tables in the current record.

The web resource contains three parts (JavaScript, CSS and the HTML page that is needed to be displayed in an IFRAME.

JavaScript web

function callService() {
  var json;
  uri = "https://eaxqueryservice.azurewebsites.net/api/payments/";

  var xhr = new XMLHttpRequest();
  xhr.responseType = "json";
  xhr.onreadystatechange = function () {
    if (xhr.readyState == XMLHttpRequest.DONE) {
      json = xhr.response;
      //console.log(xhr.responseText);
      loadData(json);
    }
  };

  xhr.open("GET", uri, true);

  xhr.send(null);
}

async function loadData(json) {
  var selector = "results";

  //call the jsonToTable Function
  jsonToTable(json, selector);

  function jsonToTable(json, selector) {
    //begin function

    //array to hold the html for the table
    var html = [];

    //add the opening table and tablebody tags
    html.push("<table>\n<tbody>");

    //begin adding the table headers
    html.push("<tr>");

    html.push("<th>" + "Voucher" + "</td>");
    html.push("<th>" + "Transaction Date" + "</td>");
    html.push("<th>" + "Description " + "</td>");
    html.push("<th>" + "Credit" + "</td>");
    html.push("<th>" + "Debit" + "</td>");

    html.push("</tr>");

    console.log(json); //add the closing table and table body tags

    //loop through the array of objects
    json.forEach(function (item) {
      //begin forEach

      //add the opening table row tag
      html.push("<tr>");

      //loop though each of the objects properties
      for (var key in item) {
        //begin for in loop

        //append the table data containing the objects property value
        html.push("<td>" + item[key] + "</td>");
      } //end for in loop

      //add the closing table row tag
      html.push("</tr>");
    }); //end forEach

    html.push("<table>\n</tbody>");

    //testing display of results
    document.getElementById(selector).innerHTML = html.join("");
  } //end function
}

callService();

CSS

th {
  border-bottom: #868ca0;
  border-width: 1px;
  border-bottom-style: solid;
  text-align: left;
  font-size: 0.8rem;
  font-weight: 400;
  font-family: "Segoe UI Regular", SegoeUI, "Segoe UI";
  padding: 9px;
  color: grey;
}
td {
  padding: 6px;
  border-bottom: #868ca036;
  border-width: 1px;
  border-bottom-style: solid;
  text-align: left;
  font-size: 0.8rem;
  font-family: "Segoe UI Regular", SegoeUI, "Segoe UI";
  color: rgb(4, 89, 153);
}

table {
  table-layout: fixed;
  width: 100%;
}
tbody > tr > *:nth-last-child(2) ~ * {
  width: 50%;
}
tbody > tr > *:nth-last-child(3) ~ * {
  width: 33.3%;
}
tbody > tr > *:nth-last-child(4) ~ * {
  width: 20%;
}

tr:hover {
  background-color: #f5f5f5;
}

HTML

<!-- UIkit CSS -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/uikit@3.5.8/dist/css/uikit.min.css" />

<!-- UIkit JS -->
<script src="https://cdn.jsdelivr.net/npm/uikit@3.5.8/dist/js/uikit.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/uikit@3.5.8/dist/js/uikit-icons.min.js"></script>

<div id="results"></div>

Codepen

You must be logged in to post a comment.