Problem summing items in changesetArray

[dr.andre]

Hello All,
We've been working on our academic sequencing core internal apps for a while now and this is my first post. Running into a simple (I think) problem when building our internal invoicing app. We have a simple table (tableAddLineItems) with invoiced line items, containing sample#s and unit pricing, with a custom column at the end that calculates the total price (item_total). I was originally having problems getting that last column to update in real-time when you made changes to the other cells, but through other forum posts I got access to the changesetArray working with the following:

item_total value =

{{ _.find(tableAddLineItems.changesetArray, {id:currentSourceRow.id})?.sample_number * 
_.find(tableAddLineItems.changesetArray, {id:currentSourceRow.id})?.unit_price }}

...so all good there. Now, when I make a change to either quantities or unit pricing, the last cell updates with the correct number.

Now the problem: I have a read-only number input component (need to tweak to work - I like the look of it over the stats component, but just need for display) I'm eventually going to use to show the sum of all those line items (subtotal), however right now I'm just using a stat component to test it out and the following code doesn't seem to be working:

subtotal value =

 {{._sum(tableAddLineItems.changesetArray[i].item_total)}}

...even though similar wording/structure works for summing other Retool components elements (not "changesetArray.things", but other "element.things" when you just directly suffix the column of interest and it uses all of them). It does work correctly if I just display one of the items in the array, such as:

subtotal value =

{{tableAddLineItems.changesetArray['0'].item_total}}

...

{{tableAddLineItems.changesetArray['2'].item_total}}

...etc.

...so the data is already accessible/in the right format for the component to use and display, but it just will not work when I try to say "all of the item_total items" in the array.

I know this is probably something very simple in the JS syntax about calling "all the items in array" (I tried [i], ['i'], [_], ['']), but my searching the forum and online doesn't seem to have a simple explanation of this, mostly also because changesetArray doesn't seem to be a general JS function, but something specific to Retool. There are no specific Retool Docs either that seem to go into detail on all the use cases/syntax of changesetArray, just a small intro and a couple of highlights. Thanks for the help everyone!

[bobthebear]

you could try reduce()

{{ tableAddLineItems.changesetArray.reduce((total, currentItem) => total + currentItem.item_total, 0) }}
above, 0 is the initialValue.
The function syntax is
reduce(callbackFn)
reduce(callbackFn, initialValue)

I'm not sure where you've got this code, but if you need pure js to help with debugging this might help:

const my_array = tableAddLineItems.changesetArray;

function sumReducer(accumulator, currentItem, index) {
  const returns = accumulator + currentItem.item_total;
  console.log(
    `accumulator: ${accumulator}, currentItem: ${currentItem}, index: ${index}, returns: ${returns}`,
  );
  return returns;
}

my_array.reduce(sumReducer);

here are the mdn docs also.

[dr.andre]

Thanks for the push in the right direction. It is working now when using the following code:

{{ tableAddLineItems.changesetArray.reduce((accumulator, current) => accumulator + current.unit_price, 0) }}

...for the unit_price (example in the photo being used for the stats component) and for the sample_number values, but not for the one value I actually want to use: the item_total. You'll see from the screenshot below that all three values are in the changesetArray and seem to be coded similarly...however when I start auto-completing those elements, the first two come up as string, but the item_total comes up as an object, so won't sum correctly.

Is this maybe due to the fact it is a "calculated number" (using the first code listed at the top of the post) inside a custom column, instead of something more "native" such as the inputted pure sample#s and unit pricing? I tried using the toString() function to fix, but doesn't seem to be working (maybe my syntax is wrong).

Thanks for any ideas again!

[dr.andre]

Forgot to attach the screenshot...

[dr.andre]

For some reason, today the item_total is now showing as a string and is working...not sure I really changed anything to convert it from an object in the other playing around I did. Weird...

[dr.andre]

I had to remove the "solved" status on this because my original problem above of the item_total coming up as an object is still happening now...I'm not sure why above it transiently came up as a valid string for a short while, but it is back to object now this weekend and hence my subtotal is not working if use item_total as the thing to sum.

So, currently these four lines work (= two different syntaxes working for two values):

{{ tableAddLineItems.changesetArray.reduce((accumulator, current) => accumulator + current.sample_number, 0) }}
{{ tableAddLineItems.changesetArray.reduce((total, currentItem) => total + currentItem.sample_number, 0) }}
{{ tableAddLineItems.changesetArray.reduce((accumulator, current) => accumulator + current.unit_price, 0) }}
{{ tableAddLineItems.changesetArray.reduce((total, currentItem) => total + currentItem.unit_price, 0) }}

...but not for when the last (calculated) column value in the table is used:

{{ tableAddLineItems.changesetArray.reduce((accumulator, current) => accumulator + current.item_total, 0) }}
{{ tableAddLineItems.changesetArray.reduce((total, currentItem) => total + currentItem.item_total, 0) }}

I can't seem to get the toString() function to work anywhere, but probably wrong syntax.

I assume there must be two ways: 1) you can either convert the item_total to the string inside the element doing the sum at the end:

{{ tableAddLineItems.changesetArray.reduce((total, currentItem) => total + currentItem.item_total.toString(), 0) }}

...or 2) convert the original values in the table column to string before you even try to sum them below:

{{ toString(_.find(tableAddLineItems.changesetArray, {id:currentSourceRow.id})?.sample_number * 
_.find(tableAddLineItems.changesetArray, {id:currentSourceRow.id})?.unit_price) }}

OR

{{ _.find(tableAddLineItems.changesetArray, {id:currentSourceRow.id})?.sample_number.toString() * 
_.find(tableAddLineItems.changesetArray, {id:currentSourceRow.id})?.unit_price.toString() }}

...but I can't seem to get either #1 or #2 to work. Of note, the second option inside of #2 (using the .toString() method on each variable) is accepted OK by Retool (no errors), but still does not convert the value to string (still shows as object on the autocomplete option when choosing for the subtotal element).

[dr.andre]

Hmm...just after writing this above, I was playing around with inputting numbers in the table again and I just found out why it transiently seemed to work above (showing item_total as a string all of a sudden), but not again now: as soon as I click to add a new line to the table (see below), the item_total value converts from an object to a string...weird, right?!

So it is altering something in the array (it seems) that by adding blank values (to start) that that suddenly shifts it to being seen as a string instead? I thought the newRows were separate from the changesetArray, so not sure why turning on that newRows would suddenly affect the changesetArray...

...and the weirdness continues - if I then go to the lines above the newRows and edit values, the subtotal no longer works again...item_total flips back to an object. Only when I got back down to the newRows and edit that value again will the subtotal rework since it has flipped back to string.

[bobthebear]

wow, ya i'm at a bit of a loss. about the only thing I can think of is to stuff more code into that little area . you could check the type of item_total and process it as a string or obj depending on the result:

{{ typeof item_total === 'string'? (process var as string here) : typeof item_total === 'object'? (process var as json object here) : null }}

I've never tried to throw an error in one of those areas you need {{ }} to use js, but you could try replacing null with some kind of error handling: throw Error("uhh oh, SpaghettiOs");