How to calculate value based on another field for row update

I have a Retool form which, when submitted, updates the corresponding row in a Snowflake table. I have a field which would calculate based on one of the fields in the form. I am struggling with how to create the calculation. I am a Retool notice (also JS novice), so I apologize if this is a very elementary question.

I have tried creating a JS switch function in the key value pair in the Form Submit query. I have tried creating a JS query and called on that value in the key value pair. I am not having any success. I appreciate if anyone can point me in the right direction!

@StaceyG Welcome to the forum!
If you could share the field you are trying to calculate and perhaps a screenshot - this will help me help you!

Hello @ScottR ! I have a form field called "Front Quality" which has a list of available options. Based on the Option selected, a field called "Front Quality Group" would populated when the form is submitted to updated the record in Snowflake. The values are below:

if Back as Front then Other

if Blur then Focus

if Blur High then Focus

if Crop Not Cropped then Crop

if Crop Other then Crop

if Crop Over then Crop

if Crop Pixelated then Crop

if Crop Under then Crop

if Damage then Dirt Damage

if Dirty then Dirt Damage

if Distorted then Crop

if Expired ID then Other

if Front as Back then Good ID

if Good Quality then Crop

if Incomplete Image then Other

if Landscape Instead of Portrait then Glare

if Lighting Dark then Glare

if Lighting Flash then Glare

if Lighting Hologram then Glare

if Lighting OverExposed then Glare

if Lighting Overhead Lights then Glare

if Lighting Shadow then Other

if Low DPI then Other

if Not Found then Other

if Other then Crop

if Out of Frame Images Submitted then Other

if Portrait Instead of Landscape then Other

if Unsure then Other

if Upside Down then Other

if Invalid Image then Other

if Not in Scope then Other

I appreciate any help/guidance you can give!

OK so Front Quality is a select field, let's name that frontQuality
frontQuality should have the label and values stored in it accordingly such as the first option you have listed: The label would be Back as Front and the value would be Other.

When selecting "Back as Front", the value would be "Other"

Front Quality Group is the field to be populated with the selection made in frontQuality.
Let's call Front Quality Group frontQualityGroup. You should set the Default Value for this field to be {{frontQuality.value}}

Hopefully I understand what you have posted correctly.

Hi @ScottR Both FrontQuality and FrontQualityGroup are captured in the Snowflake table to enable reporting at both the granular (Front Quality) and Summarized (FrontQualityGroup) levels. If FrontQuality has a label that differs from the value (label being granular, value being summarized), then it will not allow that level of reporting, correct?. I was basically trying to create a SWITCH for FrontQualityGroup based on the FrontQuality value. Does that make sense? I apologize if I'm just not getting it.. very newbie. :slight_smile:

OK so you will have to use a ternary operator in the Default Value for frontQualityGroup:
{{frontQuality.value == 'Back as Front'?'Other':'' || frontQuality.value == 'Blur'?'':'Focus' || frontQuality.value == 'Blur'?'':'Blur High'.... and so on }}

Though because you have multiple selected values that contain if then with a result of Crop, you can put them together in parenthesis...but that may be for another time because you first want to get the basics down... JavaScript in Retool

1 Like

Thanks, @ScottR ! I appreciate your help!