How to update database with a mapped computed value

[dru_nasty]

I have a table component pulling in data from a postgres database. One of the columns i'm pulling in called "Cost" is a dollar amount. I have two more columns in the table component that allow me to add a percentage markup to the Cost. The other two columns are "Markup" which is a percentage type that can be edited and the other column is "Total" which is the final computed amount from the Cost column multiplied by the Markup column. The total column has a mapped value of:

{{ currentSourceRow.cost * (1 + (table37.changesetObject?.[i]?.markup ?? currentSourceRow.markup)) }}

Which all works fine.

The problem is when I run a save action query (to update the database) with the computed column data. Do i need to create a transformer or something and not use GUI mode for the update query?

[edurmush]

Hello @dru_nasty, there are two options...

Adding fields to Changeset

Hello @Stuart, [image] It returns the entire row even though I only changed the created at value... {{ _.map(table1.changesetArray, (row) => _.merge(_.find(table1.data, { id: row.id }), row)) }} I built a quick example to give you some ideas. Good luck!

or

Adding fields to Changeset

I actually found a native way to do this. I didn't know this feature existed. [image]

[dru_nasty]

I guess there's a few things not working as needed. First off, I changed the table name from table37 to reconcileExpenses.
My mapped value for the Total column isn't calculating in real time when i update the Markup column. When I run the updateExpenses query it all works, but the Total column doesn't get added to the database table because it's a mapped value?
Here's the mapped value for the Total column:

{{ currentSourceRow.cost * (1 + (reconcileExpenses.changesetObject?.[i]?.markup ?? currentSourceRow.markup)) }}

You can see in this video what is going on:
https://www.dropbox.com/scl/fi/wcp907zl0r0g66v15552g/retool.mov?rlkey=4eua65fdcuchg1zjxgkk5rebb&dl=0

[edurmush]

Sorry @dru_nasty, but I don't know a local way to solve this problem. Inserting a transformer or changing the data in the update query may be a solution. But I think these are not what you are looking for.

[dru_nasty]

Seems like a transformer may be a way to do it, I'm just unsure of the approach. Could anyone point me in the right direction?

[dru_nasty]

Bumping this, as i'm hitting walls with how to do this.

[edurmush]

Hello @dru_nasty, you can try to mutate changesetArray before send to database.

The only drawback is that you have to do the same calculation again.

[dru_nasty]

Thanks for that, i'm still really stuck on the fact this mapped value doesn't update my total column in real time. Only after running a save as query on the table and updating the database.
So it's still two issues: 1) Can't see mapped value updates in real time on the table after changing data. 2) The total column data isn't saved to the database.

{{ currentSourceRow.cost * (1 + (reconcileExpenses.changesetObject?.[i]?.markup ?? currentSourceRow.markup)) }}

I used an exact solution i found from this post, which shows it working in realtime:

Use User Input In Table In Part of Calculation Of Another Field

Hello! One other workaround here is to check the changesetObject of your table in the calculated value of your column. For instance, if the mapped value of Column A is {{ currentSourceRow.columnB * currentSourceRow.columnC }} you could do something like {{ currentSourceRow.columnB * (yourTable.changesetObject?.[i]?.columnC ?? currenSourceRow.columnC) }} [image] It can get messy with more complex calculations so you might also try using Preloaded JavaScript to define a function that accepts cur…

[dru_nasty]

I've found that the changesetObject only works when i don't have a primary key defined for the table component. So i tried to use changeSetArray

{{ currentSourceRow.cost * (1 + (reconcileExpenses.changesetArray?.[i]?.markup ?? currentSourceRow.markup)) }}

And now it doesn't match up with the current row i have selected.
I'll select a row and check the values and the values are all from a totally different row.
My primary key id's are all unique.

[edurmush]

1 ) You can see mapped value updates in real time on the table after changing data.
Here is an example.

- YouTube

2 ) The total column data will saved to the database.

How to update database with a mapped computed value

Hello @dru_nasty, you can try to mutate changesetArray before send to database. [image] The only drawback is that you have to do the same calculation again.

[dru_nasty]

I get an error of "Primary key expense_id is not present in supplied array."
Doesn't matter if ia set the primary key to expense_id on my table component either, i get the same error. Only when I do that then my total column doesn't not update the value in real time.

[dru_nasty]

Maybe this explains it better, i changed the 2nd row markup to 10%. Nothing changes.
When i hover over currentsourceRow.cost in the mapped value, it shows 10 (which is the cost value for the first row in the table). Something not lining up for some reason. Apologies for not following or being able to explain.

[edurmush]

It doesn't work because there is a mistake. You try to find id of changesetArray but you don't have any id property on your table. You have expense_id property on it.

Just try to replace:
_.find(..., id: currenctSourceRow.expense_id with
_.find(..., expense_id: currenctSourceRow.expense_id

[edurmush]

Are you sure you checked Include full rows in changeset array property of table? You can't update your data without primary key. It's normal to get an error.

Adding fields to Changeset

I actually found a native way to do this. I didn't know this feature existed. [image]

[dru_nasty]

Thanks for helping me get there! To document what ended up working for my case:

1. Make sure to check "Include full rows in changeset array" within the table property.
2. On my "Total" column, mapped value to do real time calculation of "Cost" * "Markup":

{{ 
  currentSourceRow.cost * (1 + (_.find(reconcileExpenses.changesetArray, {expense_id: currentSourceRow.expense_id})?.markup || currentSourceRow.markup)) 
}}

3. For the update query, using the following: (having to omit a column that has some mapped data, not source data, that I want to exclude from the update):

{{
_.map(reconcileExpenses.changesetArray, row => {
    const updatedRow = {
      ...row,
      total: row.cost * (1 + row.markup)
    };
    return _.omit(updatedRow, 'assignedTo');
  })
}}

Just curious if using _.map ok here instead of _.each?