THISROW_BEFORE value not acting as expected.

[RedVox]

I have a members table with [status], [membership_type]  and [previous_status_change_date] fields.  If a member's status or membership_type changes, I want to record the date of that change.  If there's no change to status or membership_type, I don't want to update this field.   The following formula is the automatic formula for the [previous_status_change_date] field, and it's not working as I expect it should:

If(OR([_THISROW_BEFORE].[status]<>[_THISROW_AFTER].[status],
          [_THISROW_BEFORE].[membership_type] <> [_THISROW_AFTER].[membership_type]),
          today(),
          [_THISROW_BEFORE].[previous_status_change_date])

Any thoughts?

[RedVox]

Oh, and, fwiw, the [previous_status_change_date] field is not a virtual field.

[Suvrutt_Gurjar]

I believe you could try the column  [previous_status_change_date]  as  a Change Timestamp type column that listens to change in [status] and [membership_type] columns.

Track changes using Change column types - AppSheet Help

[RedVox]

Thanks SG, this looks like a much more elegant approach. I never really noticed these Change field types before.  However, I'm still puzzled as to why the code I used wasn't working.  

[Suvrutt_Gurjar · Accepted solution]

You are welcome 

For the expression you shared, I believe your code  needs to be

If(OR([_THISROW_BEFORE].[status]<>[_THISROW_AFTER].[status],
          [_THISROW_BEFORE].[membership_type] <> [_THISROW_AFTER].[membership_type]),
          today(),
          [previous_status_change_date])

I believe [_THISROW_BEFORE].[previous_status_change_date] is erroneous because there is no transition happenibg on that field, so [_THISROW_BEFORE] value is not available for [previous_status_change_date]

You simply want it to remain as it is if the other two have not changed.