Issues with App table custom column using javascript that works until js encounters a null value while concatenating

App Building
1

Hi,

I have two mongodb collection queries in Retool feeding tables in an app: a "transactions" collection that contains donation transactions for various organizations, and an "organizations" collection that contains organization specific details for those organizations receiving donations. The transactions collection contains "designations" keys (such as "A", "B", "C", etc) that pertain to "designation names" key-value pairs in the "organizations" collection ( such as [ {"A": "General Fund", "B": "Roof Support", etc...}].

I have been able to successfully add a custom column to the "Transactions" table that looks up the actual designation label in the "Organizations" collection that corresponds to the designation key using the following js function expression (screenshot included below the code snippet):

{{ (() => {
let orgKey = currentSourceRow.orgKey // for example "well"

let orgCollectionData = Organizations.data //

let desDetails = orgCollectionData.find( org => org.key === orgKey ).designations[JSON.parse(currentSourceRow.designations)[0].des]

// JSON.parse is used in the above because the json in the designations column is stored in Mongodb as a string instead of an actual json object

return desDetails

})()
}}

image
image1251×516 128 KB

The issue I'm having is when I try to expand the above code for those transactions that involve more than one designation, the expanded code below works for rows where more than one designation exists (more than one key-value pair in the json), but stops working for entries with only one designation per row (the values that worked for these with the code above disappear):

{{ (() => {
let orgKey = currentSourceRow.orgKey // for example "well"

let orgCollectionData = Organizations.data //

let desDetails = orgCollectionData.find( org => org.key === orgKey ).designations[JSON.parse(currentSourceRow.designations)[0].des] +' ; '+ orgCollectionData.find( org => org.key === orgKey ).designations[JSON.parse(currentSourceRow.designations)[1].des]

// JSON.parse is used in the above because the json in the designations column is stored in Mongodb as a string instead of an actual json object

return desDetails

})()
}}

image
image1361×515 85 KB

Is there a way to get this type of JS code working in retool where for rows only containing one designation, that mapping does not disappear in the "Designations Mapped" column?

For reference here also is a sample of the "Organizations" collection as a table in the retool app that the above code is doing a .find against:

image
image708×176 10.5 KB

2

Two suggestions (1) Set the JSON parse as a variable, and (2) iterate over the object for as deep as the object goes:

{{
(() => {
  let orgKey = currentSourceRow.orgKey; // for example "well"

  let orgCollectionData = Organizations.data; //
  //JSON.parse the Mongodb string
  let jsonObj = JSON.parse(currentSourceRow.designations);

  // Iterate over each element in jsonObj array
  jsonObj.forEach((item, index) => {
    // Access designation for current level and concatenate to desDetails
    desDetails += orgCollectionData.find((org) => org.key === orgKey)
      .designations[item.des];

    // If it's not the last element, add a separator
    if (index < jsonObj.length - 1) {
      desDetails += ";";
    }
  });

  return desDetails;
})();
}}

Give that a shot and see if it works; not having your original object, I'm not totally sure. I'm basically just thinking through options to check if levels exist before trying to move on to the next item. There is probably a way to do it where you explicitly check the length of the currentSourceRow.designations and then write if...else if statments to the bottom if you know how many levels there will be, but if iterating like this works, it would allow you to add an arbitrary number of levels in the future (I think).

1 Like