Help

Show only first record from lookup field?

Topic Labels: Base design Formulas
Solved
Jump to Solution
2021 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Jean_Thoensen
6 - Interface Innovator
6 - Interface Innovator

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.

1 Solution

Accepted Solutions

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

See Solution in Thread

5 Replies 5

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!

Screenshot 2023-08-04 at 2.16.45 PM.png

faiz78644
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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'"

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

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