Log in
Hot & Trending on FMT

Searching Across Relationships for “Empty” Fields

Searching Across Relationships for “Empty” Fields Searching Across Relationships for “Empty” Fields

Have you ever wanted to find records where a certain field is empty? Suppose you are updating your contact database and trying to capture email addresses for as many people as possible.  You need to find every person for whom you do not have an email address. When searching in FileMaker, the equal sign operator (=) is used to find an exact match; when used by itself in a field, with nothing else following it, it tells FileMaker to find records where the field is empty. So… easy enough, right?  Just go to the contact detail view, enter Find mode, type an equals sign into the email field (or select the symbol from the Operators popup in the toolbar), and click the Perform Find button.

But… suppose you are tracking contacts that may have multiple emails.  Such a setup probably requires a relational system where emails are stored in a separate table, with a relationship defined from the contact table to the email table. On your contact detail screen, a portal can show you the many related email addresses for each person.

In this scenario, typing the equal sign in the (related) email field isn’t going to work. The problem is that we are no longer trying to find contacts records where an email field is empty. Instead, we’re looking for contact records that do no have a related email address record. In other words, it’s not that the email field is empty (using the equal operator will find contacts where a related email address record was created but not filled in); it’s that the email record doesn’t exist. And finding a non-existent record is kind of hard. I mean, you can’t… it doesn’t exist.

To find our records, we need to invert our original approach, in a way.  Start by finding contact records that do have a value — any value — in the related email field: enter Find mode and type an asterisk (*) into the email field, to find email addresses with any character. Next, before clicking the Perform Find button, comes the key step: in the toolbar, next to the label Matching Records, choose Omit instead of the default Include. FileMaker understands this as a request to subtract records that have any value in the related email field. Starting from the complete set of contacts, this leaves us with all those without an email address.

What if we need to perform a search for contacts where some fields are filled in and other, related fields are empty? For example, how do you find all email-less contacts that are also active (a field in the contacts table, in this case formatted as a checkbox). In this case, we need to first search for active contacts, and then omit from that group those with emails. Start by entering Find mode and select the Active checkbox, leaving the Matching Records option on the default Include. Next, from the Requests menu, choose New Request command, and repeat the above omit procedure (enter an asterisk in the related email field, and choose Omit). Finally, click Perform Find. FileMaker first finds the active contacts, subtracts those with emails, and leaves us with the remaining active contacts without emails.

Want to see more tips on searches with operators, multiple requests, and different actions?  Check out the Finding and Sorting Your Data webinar that our Chad Novotny, our Vice President of Technology, presented last year.

12ti Studios

12ti Studios was founded to bring exceptional software design and experience together with a venture capital-like approach to helping small businesses, and businesses-to-be, create innovative software products.

It all started when people came to founder Chad Novotny with ideas. During his career as Vice President of Technology for a custom software development company, he had been approached by many people, each with an idea for a product or service. In return for equity, they would ask, could he build their ideas for no charge? Unfortunately, he had to say no; that wasn’t the business his firm was in. But Chad always wondered what could have happened had he been able to say yes.

When Chad left his previous position and joined The Venture Capital Fund of New England as Entrepreneur-in-Residence, (after having personally been involved in several VC investment opportunities and “gotten the bug”), he decided to also launch 12ti Studios. The mission is to provide not just software development, but to partner with and assist in the development of businesses that traditionally would be unable to take advantage of venture capital.

12ti continues the tradition of Chad’s contributing to the FileMaker community — experienced developers and beginners alike — by sharing techniques, tips, and thoughts through its blog. We welcome your feedback and ideas.

To find many more in depth articles from Chad and some must have tools and services from 12ti Studios visit their site at the link below:

Website: 12ti.com