Problem resetting dependent columns when the user changes a previous variable (ResetOnEdit does not work with manual selection)

AppSheet AppSheet Q&A
, , ,
1

Hello everyone, I hope you are having a great day!

I’m currently having a problem with my app and after trying several solutions I haven’t been able to resolve it. I would be very grateful if someone could guide me.

1). Context of the problem

I have the following tables in my app:

  • Order_History
  • Order_Details
  • Products
  • Sku_Brands
  • Sku_Sizes
  • Sku_Models
  • Sku_Designs (this table stores the ID of the final SKU)
  • Brands_Products
  • Product_Sizes
  • Models_Products
  • Product_Designs

In the Order_Details table I have a sequence of columns that the user uses to define the variables of a product. These columns are:

-Product_Code → reference to the Products table
-Brands → reference to Sku_Brands (which in turn reference to -Brands_Products)
-Sizes → reference to Sku_Sizes
-Models
-Designs

Each column has formulas that allow only valid options to be displayed according to the previous selections (product, brand, size, model or design).

Additionally, I implemented the following:

  • Initial Value: if there is only one possible option, it is automatically selected.
  • Show If: hides the column if the ID corresponds to “Unique”.
  • Required: prevents the user from having to manually select if only one option exists.

The result was very good and the flow worked exactly as I had imagined… until I encountered a major problem.

2). The problem

When the user changes a previously selected variable, the application “sticks” the previously selected value.

Flow example:

The user opens the form.

  • Select a product.
  • Select a brand.
  • Select a size.
  • The Model and Design columns are hidden because the system detects that they are unique values.

Up to this point everything works correctly.

The problem occurs when the user changes the brand to see another variation (for example, to compare prices between brands).
At that point, the Sizes column retains the previously selected value, even though it is no longer valid for the new brand, resulting in a validation error.

I tried to fix it by using the Reset on edit property, with a formula that resets the column when the flag changes. However, this doesn’t work.

3). Behavior I have observed

I detected something interesting:

If the Size value was automatically assigned using Initial Value, the system does reset correctly when the brand is changed.
But if the user manually selected the size, the value is “pasted” when the brand is changed.

For example:

  • The user selects Size XL.
  • Then change the Brand.

The Sizes column still shows XL, but now a validation error appears, forcing the user to correct it manually. This same behavior occurs with the other variables (Model and Design).

In practice, this makes the form cumbersome and tedious to use.

4). Possible causes I am considering

  • An AppSheet limitation.
  • Any errors or possible improvements in my formulas.
  • The structure of my tables.
  • Something I’m just overlooking.

5). Possible solution that I am evaluating

I’m considering changing my column references. Currently, for example:

Sizes reference to the Sku_Tallas table. I’m thinking about referencing Product_Sizes instead, and using a formula that filters the valid sizes as defined in Sku_Sizes. However, I suspect the problem might persist even with that change.

6). my question

Is there a way to force a column to reset when a previous variable changes, even when the value was manually selected by the user?

Any suggestions or approaches would be greatly appreciated.

1 Like
2

Reset on edit? is only evaluated when the user opens the form, not as the user interacts with the form after the form is already open. If you want to allow the possibility of updating the value automatically after the form is open, you must use a more-permissive Reset on edit? expression, or just enable it outright.

Reset on edit? is not used at all when the row is created (e.g., in the form to add a new row).

A big problem you have is that once a user changes a column value, all automatic changes to that column stop for as long as the user is in the form view.

Nope. :frowning:

3 Likes
3

Hi Steve :waving_hand: , thanks for the clarification :saluting_face: . I understand that the user’s manual selection overrides any automatic recalculation while the form is open and that ‘Reset on edit’ isn’t real-time reactive.
Given this limitation, what alternative would you suggest for clearing dependent fields and avoiding validation errors? Is there a way to automate this, or is the user required to manually clear each column?
My goal is to eliminate these ‘orphan’ values ​​and reduce clicks to make the flow smoother and less frustrating.

Thanks in advance!"

4

Off the top of my head, consider exploring the use of a detail view with Quick Edit columns. With this, every single column change 1) saves the new value and 2) restarts the editing process with the new value in place. It’s as if the user saves and reopens the form after each column value change (not exactly, but close). The down side is you can’t use the Form Saved event action, and bots that respond to row updates can’t assume the row update represents a “final” save. You’d probably want a new column to indicate the row’s status, require the user to press a button to mark the changes as complete, then use that to handle the row in its “final” form.

2 Likes