How to add a new record in a view filtered on a lookup value

Hi all,

As I’ve noticed on the community forum it’s a common issue so I decided to share with you our solution on “How to add a new record in a view filtered on a lookup value”.

We use Airtable as a project management tool that consolidates financial and operational data. It means we work project based and one or more people work on a project. It results in big tables where for example financial data is filtered per project (which is a lookup value from another field). Because this filter is applied on a lookup value, the record disappears when you add a record because it fails to fill in the field automagically.

Solution
Thanks to the recently introduced nested filtering, we have found a solution which makes use of some kind of “timer”. By filtering on the timer and an automated “Created by” field, this record will stay visible for the user who inputs it.

The following steps should be followed:

  1. Add a “Created by” field
  2. Add a “Created” field
  3. Add a “Last modified” field
  4. Add a formula with this code (I called this field “Datetime Last Modified”)
    IF(LAST_MODIFIED_TIME() = BLANK(),Created,{Last Modified})
  5. Add a formula with this code (I called this field “Minutes since modified”)
    DATETIME_DIFF(NOW(),{Datetime Last Modified},'m')
  6. Now create a view with the following nested filter, where my “Project” field should be the field you want to filter on. The filter on “Minutes since modified” is set in this examples on 10 minutes. This implies the record will stay 10 visible for the person who’s adding a record in the view, as long no “Project” is linked. Any person who should be able to add records should be selected in the “Created by” filter.

Hope this helps! Feedback is appreciated.

Pello

1 Like