+13Our 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.
Best answer by TheTimeSavingCo
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
+31If 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!
+2Hello 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
+25Hello 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'"
+13If 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!
Adam, the MAX function is not available to me because the shift worked field is text. I can't change that.
+31Adam, 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.