Return Latest Record for Each Row

[NormanStanleyBadger]

Hi,

Does anybody know how to bring back the latest record for each row for a field from another table?

I have a field (Transport Job Date) in my Table (SKUs) that is from the Transport Table (Collection Transport Jobs) that can potentially have multiple instances of each SKU or, importantly, none at all.

As you would expect, when I bring this Field (Transport Job Date) on to my table, I get multiple rows for SKUs with more than one transport job.

I would like to bring back only the latest Transport Job record for each row, so that I only have one record for each SKU.

However, I also need a solution that keeps the null values in the Field (Transport Job Date) on my Table for those SKUs that do not have any linked Transport Jobs.

Thank you.

[rubenmarin · Accepted solution]

Hi, if you have a table by SKU, the you only need Date(Max([Transport Job Date])) to show the last date for that SKU.

You can add a check for those without any transport:

If(Count([Transport Job Date])
  ,Date(Max([Transport Job Date]))
  ,'None'
)

[NormanStanleyBadger]

Thank you for your reply.

The formula returns a value in a textbox outside the table, but when I add it to the table I get "Error : Calculation Timed Out" and the table crashes. 

*Update* 

Sorted this now. The 'None' part was bringing back all the vehicles in the Transport Table.

Date(Max({$<Set Analysis>} [Transport Job Date])) 

The above did the job and does include the null values (for SKUs not in the Transport Table)

Thank you very much for your help! 👍