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

[Anton_Dickens]

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 · Accepted solution]

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.

[Anton_Dickens]

Great thank you very much Suvrutt that worked great! The VCs are fine in the parent table, as I'm using this column to filter on the map view to see spatially where each operator has been working.

I tried something similar with a dereference from my original formula but it wouldn't work, is that because the formula wasn't returning the key column? Just good information to know for the future 🙂

Cheers!

Anton

[Suvrutt_Gurjar]

You are welcome. Good to know it works per your requirement.

---

@Anton_Dickens wrote:

I tried something similar with a dereference from my original formula but it wouldn't work,

---

Could you elaborate what you tried?  Then we could better suggest.

[Anton_Dickens]

Thank you.

I had my VC in the parent table called [Most Recent Timesheet], so the formula I used was [Most Recent Timesheet].[Operator] but I guess because it was just returning a date it couldn't work like a dereference?

Cheers

[Suvrutt_Gurjar]

I believe it will depend upon the column type.

1) A dot notation (.) such as [Ref Column].[Column name] needs the [Ref column] type to be "ref". So in your case, the column [Most Recent Timesheet] should be "ref" type and of course with proper settings such as ref table /slice  configured etc. This expression format is most commonly used in the child table to pull column values from the parent table.

2) An expression without the dot notation (.) such as [Rev Ref Column][Column Name] is most commonly used to pull the child table column values into parent table. Since [Rev Ref Column] is list type column with element type as ref, the resulting expression  [Rev Ref Column][Column Name] produces a list of values.

Please not in our solution also we used INDEX() to get a single value from reverse reference column expression.

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

The articles mentioned below explain this well.

References between tables - AppSheet Help

Dereference expressions - AppSheet Help

If you further elaborate what the column expression for  [Most Recent Timesheet] , we could further discuss.