Set value in column "Current status" as result of ...

Alejandro_Delg1 · 01-13-2021 01:38 AM

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.

LeventK

Alejandro_Delg1

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.

Alejandro_Delg1

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…

LeventK

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]
			)
		)
	)
)

Steve

Yep, but that’s how AppSheet works…

Alejandro_Delg1

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

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