Lookup to show latest entry

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…

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)

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)

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)

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)