Need help, stuck on this one seemingly simple issue!
My app displays items and their associated prices.
Columns:
ITEM | CURRENT PRICE | DATE CURRENT PRICE | FORMER PRICE | DATE FORMER PRICE
When [CURRENT PRICE] is changed by user input, field [DATE CURRENT PRICE] is populated with =today() via a bot that is triggered upon data change in that table with the condition
[_thisrow_before].[CURRENT PRICE]<>[_thisrow_after].[CURRENT PRICE]
So far, so good.
Now, I want to populate [FORMER PRICE] and [DATE FORMER PRICE] with the respective [_thisrow_before] values of [CURRENT PRICE] and [DATE CURRENT PRICE].
I have added those actions to the bot (change [FORMER PRICE] to [_thisrow_before].[CURRENT PRICE] etc.), but in whatever order I call them upon change of [CURRENT PRICE], I am always left with today’s date in [DATE FORMER PRICE] and/or the current price in [FORMER PRICE]! I believe I have briefly glimpsed the correct values in the sheet before they were overwritten by the wrong ones in one or more of the variations I tested. So I think the issue is that with each change in the row the before and after values are reset, and the updates to [CURRENT PRICE], [DATE CURRENT PRICE] etc. are each changes in the row.
Can anyone shed light on the situation AND/OR maybe point me in the direction of a simpler solution, which I have a feeling exists?
Actions do not have access to the BEFORE state of the row.
To get the same desired result, I suggest to instead adopt a “record ahead” approach and save the CURRENT Price and Date anytime there is a change.
2 Likes
Now that explains a lot!
Not quite getting the suggested solution though, sorry (working on a very tired brain here). I am saving the CURRENT Price and date, whenever I update those I’d still have to move those values to the “former” columns, no?
1 Like
Yes, sorry I mis-understood. I had it in my mind you were writing the Former Price info to a separate table.
Now that I am on the same page. It is a little simpler. You don’t need actions or bots at all. You can do it all in the columns - and you can use BEFORE/AFTER.
In the “Former Price” column, set the Initial Value to be:
<em>**[_THISROW_BEFORE].[Current Price]**</em>
In the “Date Former Price” column, set the Initial Value to be:
<em>**[_THISROW_BEFORE].[Date Current Price]**</em>
Then set the “Reset on Edit” property in each of these columns with the expression:
<em>**[_THISROW_BEFORE].[Current Price] <> [_THISROW_AFTER].[Current Price]** </em>
Anytime the current Price changes, the “Former” columns “Reset on Edit” will be activated causing the Initial Value to re-fire updating the “Former” columns with the BEFORE values of the “Current” columns.
I hope this helps!
1 Like
Works like a charm! Used the same approach to update [DATE CURRENT PRICE] to today() without a bot. This is perfect, thank you so much!
1 Like