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.
Solved! Go to 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:
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 ) )
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]
Ahh, whoops, I meant ANY(ORDERBY(
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?
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.
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?
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:
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!!
@Marc_Dillon wrote:
ORDERBY( [Comments][key-column]
For this portion, maybe just ORDERBY([Related Work Order Comments]
would suffice.
User | Count |
---|---|
27 | |
25 | |
22 | |
14 | |
6 |