Show only first record from lookup field?

[Jean_Thoensen]

Our main People table is linked to a Work History table that shows the date and position of each shift worked. I want a field in the People table to show only the most recent date that a person worked. Lookup fields do that, of course, but return all of the shift records for each person. TIA!

UPDATE: The date of the shift worked is stored as text.

[TheTimeSavingCo]

If you're trying to get the most recent date, try using a rollup field with the formula "MAX(values)" instead?  Should do what you want!

[faiz78644]

Hello Jean
I think the best way is that first create a lookup field after that create a new formula field and formula will grab the first value from lookup field

Thanks
Faiz

[Alexey_Gusev]

and formula will grab the first value from lookup field - yes, but most recent might be the last value. Or they can be ordered in other way.
I solved similar task in a following way - find last date as @TheTimeSavingCo said, then created lookup of that field back in the second table, add formula like  "if(current date = last date,'yes','no')" and created lookup in a first table filtered by "formula='yes'"

[Jean_Thoensen]

Adam, the MAX function is not available to me because the shift worked field is text. I can't change that.

[TheTimeSavingCo · Accepted solution]

Can you create new fields?  If so, you can create a formula field and use DATETIME_FORMAT() to format that text into a date, and then you can use MAX()

If you can't create new fields then I think you're going to need to use a script for this I'm afraid