FileMaker Pro Equivalent to SQL Query or View

Have you ever wondered how to create queries or views in FileMaker Pro on Mac?  I have, and it's one of those odd learning curves that come with being used to straight-forward but fairly technical SQL statements that are customary in Oracle, SQL Server, MySQL and even Access.  Searching for the word "join" returns no relevant results in the FileMaker Pro 9 Help files.

Well, for other SQL developers out there, I hope this tutorial will shed some light on how to join two tables and then create a query or a view: FileMaker Pro: Creating a "Query" or "View"

In summary, here are the key takeaways from the tutorial:

• A SQL join is analogous to a layout in FileMaker Pro with relationships between tables.
• A SQL query or view is analogous to using Find Mode in FileMaker Pro with a layout.
• Simple filters can be applied in Find Mode using Symbols and the Omit checkbox.

Let me know what you think of the tutorial!  Personally, I was super excited to figure out how to import two lists from two different databases into FileMaker Pro in order to identify discrepancies between the two lists, like I would've done using Excel or Access.  Why would I use FileMaker Pro instead of Excel on Mac to do this?  Because Excel is unfortunately case-insensitive.

ApplyYourself Hack to Use Free-form Text Filters for Choice Group Fields

I discovered an interesting bug in ApplyYourself that makes it possible to do something that should've been standard functionality: Setup normal text filters using the Contains operator with Choice Group fields.

Imagine trying to get a list of all records that have Program value containing the word "Bachelor" when your Program field is setup as a Choice Group with over 100 options. The standard query interface forces you to use the following filter:

• Program In this List ... (manually selecting every singe value using the tiny 3-line picklist)

Or, you may have smartly added "Bachelor" as an extra value to the associated Choice Group so that you can select that single value when using the Contains operator.

However, both of these methods are annoyances. What if I wanted to query something on the fly with a value that I haven't predicted to need before?

The hack workaround or solution is simpler than both alternatives:

1. Setup the filter with the desired field and the Contains operator with any value at all.

2. Click save and run.
3. Click the Back button in your browser, not in ApplyYourself. The picklist will have now magically turned into a free-form text field!