Query Builder

by Mislav

QueryBuilder

Overview

  • This module provides a simple utility for writing and troubleshooting SQL queries, including abstracting field and table names, for use with FileMaker’s ExecuteSQL function.
  • Use this module if you prefer typing out your query manually instead of using a wizard (like the excellent SeedCode SQL Explorer) but would like to make the troubleshooting and field/table name abstraction process faster. There’s also a simple query formatter to help you keep your queries readable.
  • Read more about this module here.

Usage

  • See video.
  • Don’t forget to check out the sample queries available in the Scripts menu.

Installation

  • The installation takes about a minute.
  • See installation instructions in the readme script.
  • Note: This module includes three custom functions that require a copy of FileMaker Pro Advanced to install in another FileMaker file.
  • Requires FileMaker version 12 or higher.

License

  • MIT License – see the readme script.

Download

  • Download from here.

2 responses to “Query Builder”

  1. You assume that that for every BaseTableName there is at least one TO that carries the same name. The code breaks if this is not the case. It might be a good idea to
    “SELECT DISTINCT TableName FROM \”FileMaker_Tables\” WHERE baseTableName = ? ORDER BY TableID ASC FETCH FIRST 1 ROWS ONLY”
    to get the PTO of the BaseTable, if it’s the same name, cool, no problem, if it’s different, at least this very helpful utility will continue to work.

    • Mislav says:

      Peter, thanks for pointing this out. You’re right – if there are tables which don’t have table occurrences with the same names, the “field names” section will not show the fields from those tables.

      But as I understand it, your query would require a custom function, and I’d like to minimize the use of custom functions, especially when they’d be used only in such outlier cases.

      The following calc will get us the list of table names which don’t have table occurrences of the same name. Each of these would need to be fed in to the “?” of the sql you suggest to use, and because we’d be using “fetch first 1 rows only”, we would need to loop (or recurse) through the list, and that can’t be done in a regular calculation field, which is where the list of field names is generated.

      Let (
      tablenames = ” ( ‘” & ExecuteSQL ( “SELECT DISTINCT BaseTableName FROM FileMaker_Tables WHERE TableName = BaseTableName” ; “” ; “‘ , ‘” ) & “‘ ) ” ;
      ExecuteSQL ( “SELECT DISTINCT BaseTableName FROM FileMaker_Tables WHERE BaseTableName NOT IN ” & tablenames ; “” ; “” )
      )

      Can you think of a way to write the query so that it doesn’t require recursion?

Leave a Reply

Your email address will not be published. Required fields are marked *