Max(list(DateTime, ChangeTimestamp)) workaround

[Kirk_Masden]

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:

New Bug Encountered: AUTO Changing column types Questions

I too would like to see this fixed. I’ve written about it more than once in the past. Here’s the latest post I could find:

Old bug encountered AGAIN: Column type changed by editor / Editing interface issues Questions

This is an old issue that I really wish AppSheet would address: Recently, I’ve been bothered by a DateTime virtual column that keeps getting changed to a ChangeTimestamp column type: [32] There is no conceivable situation in which this would help a user because ChangeTimestamp columns have to be “real,” don’t they? I can understand the AppSheet interface automatically assigning a column type when one has not been assigned yet by the app developer but why must the interface keep changing…

New Bug Encountered: Column type changed by app editor Questions

Today, in the Data --> Columns part of the app editor, I edited the formula on a virtual column. The “type” had be designated as “image” but editing the formula caused it to revert to “text.” For someone who knows what to look for, this is a minor irritation. For a novice, however, this kind of problem, which causes an unanticipated problem that can be hard for someone without much experience to debug, this kind of thing wastes a lot of time. I strongly recommend that it be fixed.

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.

[Suvrutt_Gurjar]

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

Kirk_Masden:

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

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

Basically type casting the ChangeTimestamp columns into DateTime columns

[Kirk_Masden]

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