Lookup to show latest entry


#1

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?


#2

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…


#3

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


#4

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)


Automatically Populate One Cell in Last Row of a Data Set
Conditional display of Linked records
#5

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

Old habit.