Pulling information from a connected record

My Knack database has Member records and Office records; each Member is connected to one Office, every Office has multiple Members.

I would like for users to be able to filter Members based on information in their connected Office. For example, every Office has a field for the neighborhood where it is located, and I would like users to be able to filter the Members list to only show Members whose Office is located in X neighborhood. How can I accomplish this?

It seems to me that the easiest thing to do would be to create a field on the Members record for “neighborhood” which automatically pulls the neighborhood field from the connected Office–but I’m not sure how to do this. Alternately, is there some other way to make the Office fields searchable when looking at Members records?

Hi @caitlin,
There’s a couple of ways you might tackle this:

  1. As you mentioned, mimicking the same fields in your Members table. On your forms that add or edit a Member, you can set up record rules to update field values from connected Office records.
  2. On Search views you can add fields from connected tables for the search inputs. This means you won’t need to duplicate the fields.
  3. On Grid views, any connected Office field that is displayed on the grid will be available in the filters function

Hopefully that helps! Let me know if you need any additional assistance.