Solved: How to Return A Value Based on Max Value Of Anothe... - Page 2

Anton_Dickens · 11-10-2024 02:47 PM

Hi,

I have a table "Clients" with a child table "Timesheets". I currently have a VC returning the date of the most recent timesheet which has [Chargeable Type] of "Tree Topper" entered against this client, expression is:

MAX(SELECT([Related Timesheets][Date],CONTAINS([Chargeable Type],"Tree Topper")))

I also want another VC to return the [Operator] column from the same record as selected above (so the most recent timesheet, I want to know the operator), but I can't seem to get the correct expression...

How would I go about putting this expression together? I had a LOOKUP expression that I thought was right (but wasn't, but it was still valid) based on the above expression, but it caused the editor (and Chrome) to crash.

Any ideas on how to put this expression together would be great.

Thank you 🙂

Suvrutt_Gurjar

You may want to try below

1. Please create a virtual column called say [Maxrow_By_date] in the parent table with an expression something like

INDEX(ORDERBY( SELECT([Related Timesheets][Timesheet Table key],CONTAINS([Chargeable Type],"Tree Topper")), [Date], TRUE) ,1)

2. Then another vc called [Operator] in the parent table can have an expression something like [Maxrow_By_date].[Operator]

This will pull the [Operator] column from the child table.

3. Then another vc called [Date] in the parent table can have an expression something like [Maxrow_By_date].[Dater]

This will pull the [Date] column from the child table.

As an alternative approach, instead of pulling the date and operator column in the parent table , you could simply display the Timesheet child record as an inline record in the parent table with the latest date that has [Chargeable Type] containing "Tree Topper". This will avoid adding VCs in the parent table.

In that case your rev ref expression in the parent table to pull the child table row as an inline table with the latest date and containing "Tree Topper" can be

LIST(INDEX(ORDERBY( SELECT([Related Timesheets][Timesheet Table key],CONTAINS([Chargeable Type],"Tree Topper")), [Date], TRUE) ,1))

Column type will be List type virtual column with element type as reference and reference table will be child table.

View solution in original post

How to Return A Value Based on Max Value Of Another Column