Set value in column "Current status" as result of selection from statuses log

Hello.

I have table when log values for estate object statuses are stored (table “EstateObjectStatuses”).

There are two statuses: “on sale” and “not on sale”. Each row in “EstateObjectStatuses” has timestamp (column “timestamp”).

Estate objects are stored in table “EstateObjects”. There is column “current_status”.

So, I need somehow keep value in “current_status” as a selection of last status by timestamp for each object.

1 Like

So I managed to get latest id by this expression:

MAXROW(
    EstateObjectStatuses,
    "timestamp",
    ([_THISROW].[estate_object_id] = [estate_object_id])
)

By this expression I get id from “EstateObjectStatuses” (“estate_object_status_id” column). But I need value of column “status”!

MAXROW(
   ...
).[status]

Doesn’t work.

I’ve done it by this expression:

SELECT(
    EstateObjectStatuses[status],
    (
        [estate_object_status_id] = MAXROW(
            EstateObjectStatuses,
            "timestamp",
            ([_THISROW].[estate_object_id] = [estate_object_id])
            )
    )
)

But is it correct?
Such a cumbersome expression for simple query…

MAXROW() returns a REF, so to retrieve the value of the [Status] column, you need to de-ref the valur. Set a VC with the MAXROW() expression only and then use this VC column for de-ref
[VirtualColumn].[Status]

OR

ANY(
	SELECT(
		EstateObjectStatuses[status],
		[_RowNumber] = 
		MAX(
			SELECT(
				EstateObjectStatuses[_RowNumber],
				[_THISROW].[estate_object_id] = [estate_object_id]
			)
		)
	)
)
2 Likes

Yep, but that’s how AppSheet works… :frowning:

:man_shrugging:
I can only suggest developers to take a look at Django ORM (sure they know but maybe…)