A Virtual Column Updated Automatically After Formula Applied

AppSheet AppSheet Q&A
,
1

Hi.

Here are two simple tables, Customers and CustomersDetails.

UID customer_name first_date
aa9915bb cus_a 7/20/2023
UID customer_id date
04e4f12a aa9915bb 7/25/2023

I set customer_id in CustomersDetails as a Ref column of Customers. I intend to add revisits info like date to Customers table as Related Items. I also added “last_date” as a virtual column to Customers table, and applied the formula below to check when I visited the customer for the last time.

IF(ISBLANK([_THISROW].[Related CustomersDetails]),
  [_THISROW].[first_date],
  LOOKUP(
    MAXROW(
      "CustomersDetails", 
      "date",
      ([customer_id] = [_THISROW].[UID])
    ),
    "CustomersDetails","UID","date"
  )
)

This formula works correctly when editing, but last_date automatically updated to first_date a few seconds later after saving. I don’t know why that.

ex. last_date shows 7/25/2023 when editing, but it is changed to 7/20/2023 a few seconds later after saving.

I might miss something simple, but I need your help.

2

VC formulas get recalculated with every sync. When you have a value that should not change, put it in a normal column instead.

3

Here’s a way better expression to use:

MAX( LIST([first_date]) + [related customerdetails][date] )

1 Like
4

This formula works correctly and doesn’t update last_date after saving. Thank you so much.

IF(ISBLANK([_THISROW].[Related PotentialCustomersDetails]),
  [_THISROW].[first_date],
  MAX(LIST([first_date]) + [Related PotentialCustomersDetails][date])
)

By the way, I haven’t seen this style [ColumnName][ColumnName] ever before. I know TableName[ColumnName] or [ReferencedID].[ColumnName].

Could you share any document about that?

5

Thanks for your reply. I set last_date as a virtual column because I need to check each customers’ last visit and it can be specified only in CustomersDetails Table. If it is set as a normal column, I need to save Customers Table itself too every time.

6

https://help.appsheet.com/en/articles/4575708-list-dereference

And my expression was meant to replace your entire expression, not just the inner part.

1 Like