Populate Table Column With Calculated Value

Queries and Resources
1

Hi, I have a table on an Azure SQL server.

It has an ID, a YieldPerPlant value, and a third Yield column the user should not interact with.

The idea is after entering the YieldPerPlant value the Yield column is populated based on that value and then saved to the database on update of the table component.

Below is the basic idea of the if statement I want to use.

if ({{ currentRow.YieldPerPlant }} > 0.84) {
'High'
} else {
'Low'
}

So if the user enters 0.7 in the YieldPerPlant column then 'Low' in populated into the Yield column.

Then when Save Changes is clicked some query or queries will run to append new records and update the existing ones that were changed.

I am aware I can simply make Yield a computed column in SQL Server Manager but I want to explore Retools alternatives.

Thanks for any help

2

Search the docs for ternary operators - I will post an example later today.

1 Like
3

Hey @JoshuaWaters, welcome to the community :hugs:

@ScottR is on the right path! You can create a custom column for the table and set the value to:

{{ currentRow.YieldPerPlant > 0.84 ? "High" : "Low" }}
4

Thanks for the welcome @minijohn :grinning:

The column does already exist on the server. Are you saying it's best to create a custom column them use some update/append query to define the custom column as the value for the existing one?

5

Ah got it,

so you want to basically save "high" or "low" in the already existing Yield column based on the input from YieldPerPlant?

In that case you would need to add this logic to your query that updates the row! If I'm not mistaken you will need to create an intermediary JS query that formats the data correctly to work with your table's "Save changes" component.

6

image
image951×555 32.2 KB

I see, so the logic would have to sit somewhere in 'Array of records to update' field?

7

Yess :v:

I have a similar use-case and my setup looks like this:

  1. Set the "Save change" event handler to a JS query
  2. The JS query takes {{.recordUpdates}} and transforms it with some logic
  3. Trigger the initial save query passing down that data
1 Like