Sort list Dereferences by another column, without extra virtual column

[PGold]

Hi all,

I want to have a Show column inside an order form with a sorted list of previous orders for the selected customer.
As I understand from the appsheet documentation, I will need to make a separate virtual column to generate a sorted list and only then i can dereference the list in my show column.

My question is, is there a way to avoid making this virtual column as I am really trying to avoid any expensive virtual columns, where not absolutely necessary? 

[Steve · Accepted solution]

You could use a normal (non-virtual) column instead, which would only recompute when the row is being updated, but then you have unnecessary storage.

If the sorted list is only needed in form views, you could wrap the expression that generates the sorted List of Ref values like IFS(("Form" = CONTEXT("ViewType")), ORDERBY(...list of previous orders..., ...orderby parameters...)).

[PGold]

Wow I hadn't realised that could work, thank you Steve

Now i'm wondering, why does this only work with form view and not with detail view i.e   IF(("Detail" = CONTEXT("ViewType")) , to compute ref_rows and derefs only in a detail view?

[Steve]

A virtual column's value is only recomputed when a sync occurs, or when its row is updated, which includes while the row is displayed in a form view. Its value is not recomputed when displayed in a detail view. The goal of the expression is to prevent unneeded recomputation. Since recomputation in a detail view doesn't happen already, there's no reason to protect against.