Skip to main content

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?

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…

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…


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

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)


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



Old habit.

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)


Thanks so much for this!

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)


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

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)


Amazing! Thank you for this @W_Vann_Hall 🙏

Reply


Terms & Conditions