View most recent entry from deref

[Dialect_Junk]

Hello. I have a parent table called "Work Orders." I have a child table called "Work Order Comments."

I want to view only the most recent entry in the work order comments related to a work order.

I created a virtual column in the parent table with a deref to the child table (via ref row called "Comments"). Looks like this:

[Comments][Comments]

This works great except it compiles all of the comments related to that work order. Is there a way to only select/view the most recent entry via a deref or is this only possible via a SELECT expression?

Thanks, in advance.

[Marc_Dillon]

You need a second column as an intermediate step in order to use the ref_rows. 1st column: ORDERBY( [Comments][key-column] , [timestamp] , true ). 2nd column: [1st-col].[comments]

Otherwise, LOOKUP(MAXROW()) will get you there with one column/expression, but could be a lot less efficient if you have lots of records.

EDIT: forgot the ANY() in above crossed-out portion.

ANY( ORDERBY( [Comments][key-column] , [timestamp] , true ) )

[Dialect_Junk]

Hi Marc. Thanks for the reply. Tried the suggested changes but received this error message. 

"Column ORDERBY COMMENTS in expression '[ORDERBY COMMENTS].[Comments]' does not contain a reference"

For that 1st column, I created a VC called "ORDERBY COMMENTS" with this expression:

ORDERBY( [Comments][Date/Timestamp], [Date/Timestamp] , TRUE )

For the 2nd column, I used the following expression::

[ORDERBY COMMENTS].[Comments]

I'm sure I'm missing something with key columns...

[Marc_Dillon]

Ahh, whoops, I meant ANY(ORDERBY(

[Dialect_Junk]

Ok, I gave that a try and got this error message:

"The expression is valid but its result type 'Ref' is not one of the expected types: List"

Both of the VC's are List type columns. Do these need to be changed to Ref types?

[Marc_Dillon]

1st should be Ref, 2nd should be same as the [comments] column, probably Text. The VC auto-chose the type from the initial, incorrect, expression, so now they need changed.

[Dialect_Junk]

The expression worked but it was only pulling the very last comment from the entire table and "duplicating" that across all the work orders (hard to explain).

I tried several variations and this one seemed to work:

ANY(
TOP(
ORDERBY(
SELECT(Comments[Date/Timestamp], [Unique ID] = [_THISROW].[Unique ID]), [Date/TimeStamp],TRUE),1
)
)

Is there a more efficient way?

[Marc_Dillon · Accepted solution]

That issue suggests that your [Comments] is not actually a REF_ROWS, or not a correct REF_ROWS. [Comments] should already be filtered down per work order.

I recommend sticking to default appsheet behavior in most cases. Here, that means sticking with the default auto-generated [Related Work Order Comments] VC. Did you just rename that to "Comments" or have you done something else to it? One thing you can do is to "reset" the situation by:

1. Delete [Comments] and/or [Related...Comments]
2. In comments table, change the work-order Ref column to text
3. Save the editor
4. Change work-order column back to Ref
5. Save the editor again
6. This should regenerate the correct [Related...] column.

[Dialect_Junk]

That worked. Looks like I renamed the original [Related Comments...] to something different while also creating another VC named "Comments." Not sure what the heck I was thinking 🤔. Thanks, Marc!!

[dbaum]

---

@Marc_Dillon wrote:

ORDERBY( [Comments][key-column]

---

For this portion, maybe just ORDERBY([Related Work Order Comments] would suffice.