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!!
Solved! Go to 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.
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 !!
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.
@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 🤔
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.
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 !!
@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! (^_^)
But Magic is just a lack of knowledge!
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |