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

Hi all -

I've created a table (new) that has three columns: (A, B, and C).

  • Column A is a calculated field. It multiplied column B by column C.
  • Column B is data loaded from an API, but it's a whole number (e.g., 10).
  • Column C is both a user inputted field and data pulled from a backend (editable field). So for example, on initial load, the data in column C might be 25%.

In the above examples, if the data in Column B is 10 and Column C is 25%, then Column A would be 2.5. However, the user can edit Column C to a different percentage. If they edit it to 5%, the expected behavior would be that Column A, in realtime, gets updated to .5.

I can't seem to get the above to work without having to save the edited field to the backend and reloading all of the data. Is there a smarter way to do this asynchronously? I would like for the calculation to happen immediately, and I can save the edited field to the database in the background.

This is the one thing blocking my company from really using retool and covering all of our needs.

Thank You

You might have to take the data in that table and store it in a temp var.
Or maybe just the selected row and then when the user makes a change it manipulates the temp var and the table/row would be updated. Than you can save the changes using a bulk edit/upsert

how can i access the temp var in another column and map it to the right row?

I will try and work on something in a bit... I think this is doable but maybe not so straight forward...also wondering if a different component could be used? Unless you must use a table...

Maybe someone else can come up with a better solution (@Kabirdas or @Tess or @victoria or @bradlymathews or @AnsonHwang ) I tried using a table but IMO a ListView may be an easier component to implement for what you need.

1 Like

Yeah, I would also go with ListView in this situation. You can hack a table as @ScottR earlier suggested, but it would be a tad complex. ListView is also not particularly easy to pick up at first, but it clicks after some practice.

Here is a sample app for using ListView with a Temp as its source. It has more moving parts than you need, but hopefully it lights your way: Full advanced List View component example.

You also need to have a way to get the data from the API into the Temp var and then back to the API. I do that in the App my example came from, but I do not remember if I kept any notes on this for the stripped down version I posted to the forum.

Let us know if you have any specific question while you figure this out.

1 Like

Ill give this a shot, thank you @ScottR and @bradlymathews. I assume Ill lose some table-like functionality like sorting columns, correct?

You may but there are other ways you can manipulate data and using the initial query to sort by default etc. hope it works for you

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) }}

It can get messy with more complex calculations so you might also try using Preloaded JavaScript to define a function that accepts currentSourceRow and yourTable.changesetObject?.[i] as parameters if need be!

changeset_in_custom_column (2).json