Sheetjs related question

I had this question after viewing jQuery appending new key/value pair to JSON object in a nested loop.

I need help with a javascript function that returns a json object
sheetjs.txt
LVL 1
roger vAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

roger vAuthor Commented:
The function in question is BindTable(jsondata, tableid, invalidreqs). In this function, I have a nested loop (but it doesn't work as expected now), with outer loop to loop over jsondata, and inner loop to loop over invalidreqs, match the inner row with outer row, and if a match is found, if the inner row has MSG1 (which means there is an error in that row), then append the error key/value from the inner row to the outerrow (of jsondata), and add whatever css styles need to be added, and display jsondata in the html table (denoted by tableid).

The 2 problems I'm having are: 1. the right logic to append the error key/val pair into jsondata
2. Append the error key/val pair into each row of the jsondata object, as a new key/val inside the json element
0
Julian HansenCommented:
As a matter of interest why are you not using .find() instead of the inner loop to check for existence.

When you say it does not work - in what way - what is expected and what are you getting.

There is quite a bit of code to digest here so need some shortcuts.
1
roger vAuthor Commented:
@Julian - I've been following the nested loop way that I have in other parts of my code, and I thought I'd just stick to that format. Additionally, I haven't used find too much before and the concept of that was a bit confusing, and I didn't want to further confuse the already complex logic that I have. No other reason than that.

It doesn't work in that the inner loop is faulty - For eg: jsondata (outerloop) has 10 rows, invalidreqs has 4 rows. All 4 rows in the invalidreqs are contained in  the jsondata, and when I do the compare of the two rows inside the inner loop, they should match up, and set the has_error flag to true. Just outside the inner loop and before the close of the outer loop, I'm appending the error key/val pair (MSG1/MESSAGE) to the specific element in jsondata. But when I console.log the jsondata here, there are two of each json element - so for some reason they're being duplicated! Obviously there is something wrong in the way I'm doing the compare inside nested loop.

Also, the row$.addClass( 'response-errors' ) to add the css class to display the error row in jsondata in red inside the html table doesn't work - the class is not added. It could be due to the fact that the logic above it in the nested loop isn't right or maybe the .addClass block itself is faulty.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Julian HansenCommented:
Ok I am going to need some assistance here because I want to help but I am a bit constrained by a deadline I am on.
Firstly - I am only focusing on the resolution of the returned promises - where you do the $.each on the excelJSON and then the inner loop - is that the right place to be looking?

What is the data we are dealing with
excelJSON - what does this contain
returnedRows - what does this contain
1
roger vAuthor Commented:
@Julian - no, the promises part is working. It may not be the optimal way, but it's doing what it's supposed to do. The code in question is the BindTable helper function, after all the promises and after the ExportToTable() function. There are a couple of helper functions - BindTable() & BindTableHeader() and the first function there - BindTable() - is what needs work.

In BindTable(), there are 2 json objects being passed in parameters: jsondata & invalidreqs. The data in each of those would look like following:
jsondata: [{"AUTHOR": "JOHN DOE","BOOKNAME": "BOOK 1",  "ID": 1, "COUNTRY": "USA", "REGION": "N.AMERICA"},{"AUTHOR": "JANE DOE", "BOOKNAME": "BOOK 2",  "ID": 2, "COUNTRY": "CANADA", "REGION": "N.AMERICA"},{"AUTHOR": "JANINE EDWARDS","BOOKNAME": "BOOK 3",   "ID": 3, "COUNTRY": "UK", "REGION": "EUROPE"},{"AUTHOR": "SHAKESPEARE","BOOKNAME": "BOOK 4",   "ID": 4, "COUNTRY": "UK", "REGION": "EUROPE"}];

invalidreqs: [{"AUTHOR": "JOHN DOE","BOOKNAME": "BOOK 1",  "ID": 1, "COUNTRY": "USA", "REGION": "N.AMERICA", "MSG1": "THIS ROW ALREADY EXISTS"},{"AUTHOR": "SHAKESPEARE","BOOKNAME": "BOOK 4",  "ID": 4, "COUNTRY": "UK", "REGION": "EUROPE", "MSG1": "THIS ROW ALREADY EXISTS"}];

Open in new window


So invalidreqs has 2 rows matching with the jsondata. So inside the inner loop in the BindTable() function, when I match the
(jsondata[i].AUTHOR==invalidreqs[u].AUTHOR &&  jsondata[i].BOOKNAME==invalidreqs[u].BOOKNAME && 
                  jsondata[i].COUNTRY==invalidreqs[u].COUNTRY)

Open in new window

there should be 2 matches. I need to then append the "MSG1": "THIS ROW ALREADY EXISTS" key/value pair into the jsondata json object. So once this is done, jsondata will look like this:
jsondata: [{"AUTHOR": "JOHN DOE","BOOKNAME": "BOOK 1",  "ID": 1, "COUNTRY": "USA", "REGION": "N.AMERICA",  "MSG1": "THIS ROW ALREADY EXISTS"},{"AUTHOR": "JANE DOE", "BOOKNAME": "BOOK 2",  "ID": 2, "COUNTRY": "CANADA", "REGION": "N.AMERICA"},{"AUTHOR": "JANINE EDWARDS","BOOKNAME": "BOOK 3",   "ID": 3, "COUNTRY": "UK", "REGION": "EUROPE"},{"AUTHOR": "SHAKESPEARE","BOOKNAME": "BOOK 4",   "ID": 4, "COUNTRY": "UK", "REGION": "EUROPE",  "MSG1": "THIS ROW ALREADY EXISTS"}];

Open in new window

If you notice now in jsondata, the 2 elements for author: john doe and author:shakespeare, the new error key/value par (MSG1:THIS ROW ALREADY EXISTS) is added to the two elements, but the other 2 elements (author: jane doe & author: janine edwards) do not have the error key/value pair since those rows are only in jsondata but not in invalidreqs.

Once that is done (jsondata is updated with the error key/value pair), I need to add a css class - "response-errors" - to the row in jsondata that has the error key/value pairs. In this case - author:john doe and author:shakespeare - rows will be added with the css class "response-errors". This is done so that when the entire jsondata is displayed in the html table denoted by tableid, those 2 rows will show up with text in red color, denoting that they're errors.

Not sure if that was too long, but that is essentially what needs to happen inside BindTable() function.
0
Julian HansenCommented:
Take a look at this code and let me know if it produces the right result. You can see it working here

The codes uses the .map() and .find() functions

map() - applies the specified function to every element in the array
find() returns true if an element is found in an array using the custom function
<script>
var jsondata = JSON.parse('[{"AUTHOR": "JOHN DOE","BOOKNAME": "BOOK 1",  "ID": 1, "COUNTRY": "USA", "REGION": "N.AMERICA"},{"AUTHOR": "JANE DOE", "BOOKNAME": "BOOK 2",  "ID": 2, "COUNTRY": "CANADA", "REGION": "N.AMERICA"},{"AUTHOR": "JANINE EDWARDS","BOOKNAME": "BOOK 3",   "ID": 3, "COUNTRY": "UK", "REGION": "EUROPE"},{"AUTHOR": "SHAKESPEARE","BOOKNAME": "BOOK 4",   "ID": 4, "COUNTRY": "UK", "REGION": "EUROPE"}]');
var invalidreqs = JSON.parse('[{"AUTHOR": "JOHN DOE","BOOKNAME": "BOOK 1",  "ID": 1, "COUNTRY": "USA", "REGION": "N.AMERICA"},{"AUTHOR": "SHAKESPEARE","BOOKNAME": "BOOK 4",  "ID": 4, "COUNTRY": "UK", "REGION": "EUROPE"}]');

// FOR EACH jsondata APPLY THE FUNCTION. 
// b CONTAINS THE CURRENT ELEMENT
jsondata.map(a => {

  // SEARCH FOR AN ELEMENT IN invalidreqs THAT MATCH THE 
  // CRITERIA TESTED FOR IN THE FUNCTION
  if (invalidreqs.find(b => {
	return a.AUTHOR == b.AUTHOR && a.BOOKNAME == b.BOOKNAME && a.COUNTRY == b.COUNTRY;
  })) {
	a.MSG = "THIS ROW ALREADY EXISTS";
  }
});

console.log (jsondata);
</script>

Open in new window

The above produces this output
[
   {
      "AUTHOR":"JOHN DOE",
      "BOOKNAME":"BOOK 1",
      "ID":1,
      "COUNTRY":"USA",
      "REGION":"N.AMERICA",
      "MSG":"THIS ROW ALREADY EXISTS"
   },
   {
      "AUTHOR":"JANE DOE",
      "BOOKNAME":"BOOK 2",
      "ID":2,
      "COUNTRY":"CANADA",
      "REGION":"N.AMERICA"
   },
   {
      "AUTHOR":"JANINE EDWARDS",
      "BOOKNAME":"BOOK 3",
      "ID":3,
      "COUNTRY":"UK",
      "REGION":"EUROPE"
   },
   {
      "AUTHOR":"SHAKESPEARE",
      "BOOKNAME":"BOOK 4",
      "ID":4,
      "COUNTRY":"UK",
      "REGION":"EUROPE",
      "MSG":"THIS ROW ALREADY EXISTS"
   }
]

Open in new window


Is this what you want to achieve?
1
Julian HansenCommented:
Note the above is presented to clarify the question .find() is not supported by IE (surprise) so you might need to use this polyfill
From the .find() page.
if (!Array.prototype.find) {
  Object.defineProperty(Array.prototype, 'find', {
    value: function(predicate) {
     // 1. Let O be ? ToObject(this value).
      if (this == null) {
        throw new TypeError('"this" is null or not defined');
      }

      var o = Object(this);

      // 2. Let len be ? ToLength(? Get(O, "length")).
      var len = o.length >>> 0;

      // 3. If IsCallable(predicate) is false, throw a TypeError exception.
      if (typeof predicate !== 'function') {
        throw new TypeError('predicate must be a function');
      }

      // 4. If thisArg was supplied, let T be thisArg; else let T be undefined.
      var thisArg = arguments[1];

      // 5. Let k be 0.
      var k = 0;

      // 6. Repeat, while k < len
      while (k < len) {
        // a. Let Pk be ! ToString(k).
        // b. Let kValue be ? Get(O, Pk).
        // c. Let testResult be ToBoolean(? Call(predicate, T, « kValue, k, O »)).
        // d. If testResult is true, return kValue.
        var kValue = o[k];
        if (predicate.call(thisArg, kValue, k, o)) {
          return kValue;
        }
        // e. Increase k by 1.
        k++;
      }

      // 7. Return undefined.
      return undefined;
    }
  });
}

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
roger vAuthor Commented:
@Julian - I didn't try it out in my live code, but looking at your output, yes, that is exactly what jsondata should look like once the matching is done, and the error key/values are appended to 2 of the rows (since 2 rows match). Once I have this updated jsondata object, the challenge is adding the response-errors css class to the html table (which is notated by the tableid argument in the BindTable() function), to then display the jsondata object. So the html table would look something like this:

  AUTHOR           COUNTRY               REGION
*****************************************
JOHN DOE          USA                         N.AMERICA
ERROR: THIS ROW ALREADY EXISTS
*****************************************
JANE DOE          CANADA                  N.AMERICA
*****************************************
JANINE EDWARDS   UK                     EUROPE
*****************************************
SHAKESPEARE        UK                       EUROPE
ERROR: THIS ROW ALREADY EXISTS
*****************************************

Open in new window


Each of the rows is separated by the asterisk here to show that they're separate rows in the html table. As you can see, the first row and the last row (John Doe & Shakespeare) have an additional <tr/> that has a colspan of 3 (to span all 3 columns), in order to display the error message. The entire row of row 1 and row 4 will then be displayed with red text (to denote an error row) by using .addClass method to add the respons-errors class.
0
roger vAuthor Commented:
@Julian,

I used your code in my live code, and it works as intended (I've only tested it in Chrome so far; I'll add the polyfill if .find() breaks in IE). The jsondata object now has the error key/val pair of "MSG": "THIS ROW ALREADY EXISTS" in all its elements where there is a match between jsondata & invalidreqs.

But the code errors out in the BindTableHeader() function, because when BindTableHeader() is called from inside BindTable(), the jsondata that is passed as an argument to it, does not have the "MSG": "This row ..." key/val in it. BindTableHeader() builds it's column list based on the unique key in the jsondata object, but my requirement is NOT to have "MSG" as a separate column but instead, in a <tr/> that is appended at the end of each error row in the table. the example html table is posted in my previous post.
0
roger vAuthor Commented:
Also: IE 10 is throwing a "SYNTAX ERROR" at the line that has jsondata.map - EVEN AFTER adding the polyfill code right above it. I'm not sure if IE is erroring out on the .map or the => (fat arrow)

UPDATE: The cause of this error is the => (fat arrow syntax) that IE was barfing on (rolleyes) - I fixed it by removing the => and instead using function(a). It now works in IE! [sigh]
0
Julian HansenCommented:
you might need to change the map and find functions to use actual functions instead of arrow functions i.e

jsondata.map(a => {

Open in new window

Becomes
jsondata.map(function(a) {

Open in new window

And
invalidreqs.find(a => {

Open in new window

Becomes
invalidreqs.find(function(a) {

Open in new window

1
roger vAuthor Commented:
@Julian - yes I already did that - your answer overlapped my update that I posted above. That issue is resolved now. thanks.

What's remaining is being able to call BindTableHeader() from BindTable(), and displaying the error message in the error row in a <tr/> for each error row.
0
Julian HansenCommented:
What's remaining is being able to call BindTableHeader() from BindTable(), and displaying the error message in the error row in a <tr/> for each error row.

Ok, give me some background here ... what is the expected and actual outcome.
1
roger vAuthor Commented:
I changed the logic to leave the jsondata object as is and instead manipulate the BindTableHeader() function to add the addtional rows. This feedback helped a lot in that!
0
roger vAuthor Commented:
Hi @Julian - I got it figured out - it's a hack, but got it to work. I changed the logic to leave jsondata as is, and instead add the error row based on the invalidreqs object. Thanks for your help as always!
0
Julian HansenCommented:
You are welcome.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.