Sum of editable columns

[Annie_Candy]

I have a table component in my app that is getting all rows from a retool database that fall within a certain date range. At the end are five columns that contain data from the database initially, however I am making them editable.

I want the fifth column to be the sum of the displayed values of these four editable columns. I want to use their edited values, but currentSourceRow is only using the original value. CurrentChangeSet is not defined and displayedData is not working either. What should I do?

[Mike_M]

Hi @Annie_Candy, the only way to access those edited values is either through tablename.changesetArray or tablename.changesetObject. The edited values are basically temporary values until they are actually saved to the source of the table. In that way,currentSourceRow will only access the saved values of the row.

Below is an example of something that might work:

Basically when summing up any editable column you can use a ternary to check if it has changed, and then either use the changed value or use the current value.

The other option is to just use currentSourceRow, and then on a save action you update the table, fetch the data again on success, and the sum column will then be updated. In that case the sum will only update after clicking "Save".

[Annie_Candy]

I tried your solution for the mapped value, but it only returns the data for the first row. What is item referring to? How do i get the index of the row?

I just need the last column to be equal to the sum of the columns before it.

[Mike_M]

Great questions, I could have definitely explained that better! In this example, item refers to the source of data for that row. If you look at your other columns, for example Sss loan, the source would be something like sss_loan (or whatever the column name in the database is), and the default mapped value is {{ item }}. In my example above, I made the id the source of my custom sum column, so that I could refer to it to access the correct object in the changesetObject.

Something I forgot to mention that is crucial in having this work, is making sure the primary key of your table is set to ID. Without this, the changesetObject will organize changed rows by the index instead of their id. If you have it set up this way, everything should work.

To answer your second question, you can access the index of the row by using i in the mapped value of the column. Also, like I said before, if the primary key isn't set in your table settings, the changesetObject will organize changed rows by index. So, not to complicate things, but an alternate solution could be to leave the primary key blank, and then use the index instead of the item in the mapped value. Modifying my previous example would look like this:

{{ (table12.changesetObject[i] ? table12.changesetObject[i].distance : currentSourceRow.distance) + currentSourceRow.user_id }}

Keep in mind that in my example, only the distance column is editable, so changes in the user_id wouldn't be seen. I'm also realizing that the logic is slightly different with multiple columns changing, and we'll need some optional chaining. Based on the image you shared, if I had to guess what your logic would look like, you would have something like this:

{{ (table1.changesetObject[i]?.sss_loan ? table1.changesetObject[i].sss_loan : currentSourceRow.sss_loan) + (table1.changesetObject[i]?.pagibig_loan ? table1.changesetObject[i].pagibig_loan : currentSourceRow.pagibig_loan) + (table1.changesetObject[i]?.canteen ? table1.changesetObject[i].canteen : currentSourceRow.canteen) + (table1.changesetObject[i]?.others ? table1.changesetObject[i].others : currentSourceRow.others) }}

Not the shortest piece of javascript ever, but it should work! Let me know how it goes and maybe share what your mapped value looks like if its not working and we can make any necessary tweaks.