Max(list(DateTime, ChangeTimestamp)) workaround

Hi!

I needed to have one virtual column that displays the most recent date and time that a change has been made to the record. There are several records to compare to find the most recent, some of which are ChangeTimestamp columns, while some are DateTime columns.

AppSheet won’t let me compare ChangeTimestamp and DateTime columns in one max expression as follows:

max(list([DateTime1],[DateTime2],[ChangeTimestamp1],[ChangeTimestamp2]))

This produced a “LIST has elements of mismatched types” error so here’s what I did. First, I made one virtual column to compare the ChangeTimestamps, and set the column type to DateTime;

max(list([ChangeTimestamp1],[ChangeTimestamp2]))

Let’s say I called this ChangeTimestamp Max. Then, I made another virtual column:

max(list([DateTime1],[DateTime2],[ChangeTimestamp Max]))

This worked nicely. Now, here’s my question: Did I have to do it this way? Could I have done everything in one virtual column?

By the way, I’d like to take this opportunity to carp a bit more about how the editor automatically assigns column types. With the following

max(list([ChangeTimestamp1],[ChangeTimestamp2]))

the editor automatically assigns ChangeTimestamp as the column time because that’s the type of column that the expression handles. However, you cannot have an expression in a ChangeTimestamp column, so instead of helping, the editor is doing something that will inevitably cause an error. The only setting I’m aware of in this case that won’t cause an error is DateTime, but that’s not what the editor chooses. Moreover, if I alter the expression in the slightest, even after having manually set it to DateTime, the editor flips it back to the error causing ChangeTimestamp.

Here are previous post I have made regarding the editor changing column types improperly:

Two of the three seem either not to have been noticed or to have been ignored by AppSheet personnel. @Aleksi was kind enough to give a positive reply to one of the three (thanks @Aleksi!) but the issue remains unresolved. I’m hope for some improvement in this aspect of the editing interface in 2020. :slight_smile:

Hi @Kirk_Masden,

I have yet to read the entire post. However for combining list of Date time type and Change Timestamp type, you could try following expression.
In place of

Please try
max(list([DateTime1],[DateTime2],DATETIME([ChangeTimestamp1]),DATETIME([ChangeTimestamp2])))

Basically type casting the ChangeTimestamp columns into DateTime columns

4 Likes

Great! I should have known about this. Thanks!!

1 Like