How to reset/make blank an Enum column when another column data in same row is changed? TYVM!

[StephenMattison]

Howdy! I have two columns in the same row [Person assigned to Role] (Ref type) and [Confirm your role] (Enum type). When PersonA enters a negative confirmation of "No can't attend" into [Confirm your role] column, we will then enter a new PersonB into that [Person assigned to Role] and wait for them to enter their confirmation choice in [Confirm your role].  When PersonB is entered into [Person assigned to Role], I need the [Confirm your role] column to reset back to blank.  I think I need a _THISROW_BEFORE   _THISROW_AFTER expression for the  [Confirm your role] column based on the [Person assigned to Role] column. TYVM!!

[WillowMobileSys · Accepted solution]

Oh no!  Egg on my face!!  I hate that!!

You are so right!  And now that I think of it, I believe I have taken advantage of that once before.

THEREFORE, you can use the  [_THISROW_BEFORE]   and [_THISROW_AFTER] checks.

So I just tested this with the following "Reset on Edit" expression:

[_THISROW_BEFORE].[Test1] <> [_THISROW_AFTER].[Test1]

The Initial Value of  number column was set to zero.  Opening the existing row and changing the Test1 column value resulted in my number column being properly reset to zero.

[WillowMobileSys]

UPDATE:  As pointed out by @MultiTech,  Reset on Edits can work in this use case with then [_THISROW_BEFORE]  and [_THISROW_AFTER] checks.

******************************************************

You will need to do this with a Bot on row Update.  There you can use the [_THISROW_BEFORE]   and [_THISROW_AFTER] checks to see if the Person has changed and if so then use an action in the Bot to reset the row.

It won't work in the Form because....

When opening an existing row in a Form,  the only way to automate the "resetting" of a value is through an App Formula or with "Reset on Edit".   In your current use case, neither will work.  

You can't use App Formula because you need the user to be able to choose values from the dropdown of the column you want to reset.

UPDATE:  The below was not accurate.  If there is a proper expression in the "Reset on Edit" property, it can be triggered when updates are made to a column AFTER the Form is opened.

"Reset on Edit" only works on opening of the row to apply the Initial Value.  Once the row is opened, it will no longer trigger an update so you have no chance to reset the value based on another column changing.

[MultiTech]

---

@WillowMobileSys wrote:

"Reset on Edit" only works on opening of the row to apply the Initial Value.  Once the row is opened, it will no longer trigger an update so you have no chance to reset the value based on another column changing.

---

Not quite:  Reset on edit will trigger on any edit made in the form, depending on the formula you've got inside the Reset on edit.

------------------------------------------------------------------------------------------------------------

The following would work I think 🤔

1. Create a ChangeTimestamp, set to watch the [Person assigned to Role]
     - This will grab a timestamp every time you modify that column
2. In the [Confirm your role] column, use a reset on edit that's watching your ChangeTimestamp for something like:
        - [ChangeTimestamp] = NOW()
   
   You might have to add a time-window inside here, to account for a few seconds to get things to work smoothly every time.
   
   

[WillowMobileSys · Accepted solution]

Oh no!  Egg on my face!!  I hate that!!

You are so right!  And now that I think of it, I believe I have taken advantage of that once before.

THEREFORE, you can use the  [_THISROW_BEFORE]   and [_THISROW_AFTER] checks.

So I just tested this with the following "Reset on Edit" expression:

[_THISROW_BEFORE].[Test1] <> [_THISROW_AFTER].[Test1]

The Initial Value of  number column was set to zero.  Opening the existing row and changing the Test1 column value resulted in my number column being properly reset to zero.

[StephenMattison · Accepted solution]

Great news! This is the solution!!  It's working perfectly, and with my Quick Edit, Delayed Sync & Automatic Updates set up, it works live, on the fly as I change the [Person assigned to Role] column, and it even updates the Conditional Formatting that I use for [Confirm your role] column! Yes, this is awesome, SO simple, TYVM!!

In my Update Behavior >> Reset on edit expression for [Confirm your role] column, I used this

[_THISROW_BEFORE].[Person assigned to Role] <> [_THISROW_AFTER].[Person assigned to Role]  

So simple, so elegant! Thank you very much @WillowMobileSys !!

[MultiTech]

---

@WillowMobileSys wrote:

So I just tested this with the following "Reset on Edit" expression:

[_THISROW_BEFORE].[Test1] <> [_THISROW_AFTER].[Test1]

---

This sort of stuff is magic!  (^_^)

[WillowMobileSys]

But Magic is just a lack of knowledge!