How do I get the last record from a filtered table

I have a summary table of TRADES filtered by Date (top table)

I need to get the CumResult from the last record of the filtered trades records (bottom table). Record 5 in this example.

The records in the TRADES table are sorted by Date then by Time

I can’t use min or max since the value can be positive or negative. And it can go from positive to negative. I just want to grab the value from the last record.

Seems easy because I can see it :slight_smile: … but I’m not sure how to get it

a seprate but related question, how do I sort by 2 fields in this statement?

rows = Trades.lookupRecords(Date=$Date, sort_by = 'DateTime')

I have separate date and time fields but didn’t know how to sort by 2 fields, so I combined them. It works, but isn’t necessary if I can sort first by date, then by time


found the answer to getting the value of CumResult for the last record… stole from a couple different posts :wink:

cumResult = list(Trades.lookupRecords(Date = $Date))

return cumResult[-1].CumResult if cumResult else None

still need help on double sort

I think you could replace first line by following:

curResult = sorted(Trades.lookupRecords(Date=$Date), key=lambda r: (r.Date, r.Time))

Caveat nevertheless: performance-wise, it’s certainly much less optimized than a sort_by with a single column. On (very) big tables, this difference could matter.

thank you. I.ll stick to my combined time/Date field and a single sort_by since it’s working

oops. My formula to get the last record works most of the time, but not always

I am using this formula in the AdjR field of the TRADES [by Date] table

cumResult = list(Trades.lookupRecords(Date = $Date))

x = cumResult[-1].AdjResult if cumResult else None

return x

I stole it from some other posts so am not sure exactly what it is doing, but it’s doing it wrong

The formula is “usually” correct, but not always

any idea what I did wrong?

my bad… Again

I forgot to sort in the lookup equation so that even if records were sorted in my sub table, they weren’t necessarily sorted the same way in the function.

cumResult = list(Trades.lookupRecords(Date = $Date, sort_by="DateTime"))

x = cumResult[-1].AdjResult if cumResult else None

return x

You should use lookupOne in that case, easier and faster. Moreover, if this is really the whole formula, the x variable and the return are useless. So you could write your formula as follows:

Trades.lookupOne(Date=$Date, sort_by="-DateTime")

Reference here.

thanks jperon
I added .AdjResult to the end to get the value instead of the id and that worked.

Trades.lookupOne(Date=$Date, sort_by="-DateTime").AdjResult

the x and return are part of my debugging process. Makes it easier to try stuff until I get it right :slight_smile: