Keep only latest date in a lookup field

[Steven_Hambleto]

Is it possible to limit a Lookup field to the latest entry?

I have a Notes table with a date field which is related to the Companies table. I want to show the date of the last note record related to that company. That way I know when I last spoke to that company and filter the table accordingly.

Any thoughts?

[Tuur]

You can’t limit the lookup, but you can use a formula to change the order of the entries.

E.g. I’ve used a ‘Disabled account’ checkbox to add a red cross in front of the primary field (which of course has to be a formula in this case). That way the ‘disabled’ entries show up at the bottom of the lookup selection.

In your case you could automatically ‘cross’ all entries (with a circular reference trick) that are not the latest so that the latest would appear on top.

You can also create a view with only the latest entries in it this way, but you can’t point a lookup to it…

[Steven_Hambleto]

Shame there isn’t a “limit=‘1’” parameter or something like that.

[W_Vann_Hall]

There is a somewhat clunky way of doing this:

1. In your ‘Notes’ table, create a field called “createdTime”. Make it a Formula field with the formula CREATED_TIME().

2. In your ‘Companies’ table, create a field called “latestNoteDate”. Make it a Rollup field that rolls up the “createdTime” field of the ‘Notes’ table with a method of MAX(values). This will store the date and time of the most recent note for each company.

3. In your ‘Notes’ table, create a field called “latestNoteDate”. Make it a Lookup field based on the “latestNoteDate” field of the related ‘Companies’ table.

4. In your ‘Notes’ table, create a field called “latestNote”. Make it a formula field with the formula IF(createdTime = latestNoteDate,Note,""). In all but the most recent ‘Notes’ record, this field will be an empty string (""); in the most recent note, it will be a duplicate of the full “Note” field.

5. In your ‘Companies’ table, create a field called “latestNote”. Make it a rollup field that rolls up the “latestNote” field of the ‘Notes’ table with a method of CONCATENATE(values).

This will result in the “latestNote” field of each ‘Companies’ record containing the text of the most recent note.

https://airtable.com/shrC0EP2qexnGNqMN
See also:
Most Recent Date from another table](https://airtable.com/shrC0EP2qexnGNqMN)

[Tuur]

Ah. Sure. I was thinking about the lookup window you get when you select a record.

Old habit.

[Brendon_Johnson]

Thanks so much for this!

[Mark_Maidique_S]

Oh man, this is fantastic! Thank you so much for posting. I’ve been trying to figure this out for a year.

[Pierre_Lombard]

Amazing! Thank you for this @W_Vann_Hall :pray: