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.
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
Great! I should have known about this. Thanks!!
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |