MightyData

FileMaker and WordPress Consultants

Getting Faster: Installing Multiple Instances of FileMaker

by 16 Comments

As you may have guessed by now, I love working in an optimized environment, designed for getting the crud out of the way so that I can focus on development. Just like a carpenter needs to not be thinking about the hammer, or the painter about the mechanics of the canvas, my computer and its operating system need to fade into the background, almost as if they were not really there at all.

Have you ever, in the course of working with a database, needed to do a long import, export or other time-intensive task?  For example, I have worked on a very large solution recently that required 4 hours to import the DDR into BaseElements. Not wanting to lose an entire 1/2 day to the task, I needed a solution where I could both perform the import and continue working on other projects.

More Than One Install

The best solution to this conundrum is to simply have more tools available. In this case, since I have two legal licenses of FileMaker 11 available to me, I have installed the application twice on my development machine.

Multiple FileMaker installs on Mac OS

Here are the steps to do so on Mac OS X:

  1. Rename the current installed version folder of FileMaker Advanced (or FileMaker). I used FMA11-A.
  2. Rename the installed FileMaker Pro Advanced application. Again I used FMA11-A.app.
  3. Install FileMaker/FileMaker Advanced again, using the second license key.
  4. Rename the folder and install application for this new installation. (FMA11-B in my case.)

That’s it! Now both versions can be run simultaneously. Note that when performing an update of the FileMaker software, the updater will ask you which installation you want to update. Select an installation, complete the first update, then run the updater again and it will update the other installation.

Two Tasks At Once

I can run that ginormous DDR import on one instance and be happily developing a solution on the other. It also means you can have two Manage Database windows, two data viewers or two script debuggers open at the same time. Or even two separate logins (Admin and User for example) into the same solution.

Here is a peek at what this all looks like in Finder.  Just for fun, I created new icons (download) for each of the installations. In OSX, use the finder info pane to change the assigned icon for an application:

Well, the icons are more than just for fun, as they provide a visual cue in the OSX dock and application switcher.  I hope you enjoyed this quick tip for setting up a more efficient FileMaker development environment.  I’d love to hear about your super-secret tips and tricks in the comments.

Monitoring the Web Publishing Engine in FileMaker Server 11

by 2 Comments

FileMaker Server 11 includes several components to publish databases to client machines. The Database Server is perhaps the most obvious component, as this hosts the databases that are shared with clients.

Any type of web publishing requires the Web Publishing Engine (WPE) to be active and running. Custom web publishing such as PHP communicates to the Database Server via the WPE. But what happens when a web page is no longer is responsive? Which process failed? Is it a page error, a Database Server problem, or an issue with the WPE? As you can imagine, manually checking the state of the server becomes tedious.

Components for web publishing with FileMaker Server

The ability to build an automatic monitoring method becomes critical for anyone managing web pages that communicate with FileMaker Server, to rapidly identify problems and minimize downtime.

The Admin Console is a GUI-based tool to manage databases, backup and script schedules, clients, statistics, and the current state of the database and WPE processes. However, as it is a GUI-based system, it needs eyes-on management. In order to automate the process we need to turn to some system-based tools.

Turning to the Command Line

One oft-used method is to create a PHP page that pings the Database Server. If the ping – a PHP request – returns a valid result, then all is well. If the ping returns an error, the administrator intervenes. However, when setting this up, I ran into issues with PHP sendmail failures, and I wanted a process that minimized manual intervention.

To fully automate the process I used one set of tools to check the state of the WPE, and another to automatically switch on the WPE if it wasn’t running. Although FileMaker Server comes with a command line interface – fmsadmin – there is no documented process to manage the WPE in FileMaker Server 11.

When FileMaker, Inc. released Server 12 on April 4, 2012, new options were added to start and stop processes; now you can stop and start the WPE process via fmsadmin. However, if you’re running FileMaker Server 11, you need to take advantage of some undocumented processes. (Here the usual caveat applies: test to make sure this works under your circumstances).

In the Mac OS X environment you can use Unix commands to check running processes. One such command “nc” lets you scan for listening daemons to check if a process is running on a specific port, without sending data. By scanning a WPE port you then can see if this is active for a given IP address.

nc -zw 3 127.0.0.1 16008

This command will return nothing if the process is inactive. If the process is running, the command returns a text string with a success message.

Once we’re able to run such a command in the Terminal, the next step is to set this up in a shell script. If we first check for the WPE and find that it isn’t running, we next verify that the Database Server is running. Once we know this process is active, we then run a sequence of fmsadmin commands. These I gleaned from a website (thanks to Andrew Duncan from Databuzz) that provides a preference pane to control FileMaker Server 11. The key command is:

fmsadmin start|stop adminserver|wpc|cwp

When I tried this out in the Terminal, I found that it only would work for me when I ran them on separate lines, and in a certain sequence.

fmsadmin start wpc fmsadmin start cwp

After all this is combined into a shell script, you can run this as a cron job on Mac OS X. I simply saved the code below into a wpe.sh file, made this executable and set up the cron to check the process. The #lines are comments that tell me what happens inside each branch, and the if statements check to see whether there is a value returned from the query or not.

#!/bin/bash #wpe running up_wpe=`nc -zw 3 127.0.0.1 16008`
if [ -z "$up_wpe" ]; then #echo "wpe is not running"
  up_fms=`nc -zw 3 127.0.0.1 5003`
if [ -n "$up_fms" ]; then #echo "DBS running"
  fmsadmin start wpc fmsadmin start cwp fi fi

This probably is an un-orthodox method to check the WPE process and start it up, but it appears to work. With FileMaker Server 12 you still need to step outside FileMaker Server’s built-in tools to check the process, but you’re able to run the documented “fmsadmin start wpe” command. Possibly there are other ways to monitor the WPE process, and refinements to this method. We’d love to hear any that have worked for you.

Further Explorations in SQL for FileMaker

by 1 Comment

Once you start down the path exploring new features or technologies, often the deeper you dig the more cool stuff you expose. In my first post on SQL and FileMaker I focused simply on pulling and comparing data between tables. There are several advantages to employing SQL in FileMaker: speed and context perhaps the primary ones. While SQL the graph remains clean, as the queries remain inside the script, no additional Table Occurences are required.

One area in FileMaker, where we tend to rely on jumping through hoops in the graph, deals with report creation. This is especially true when pulling together reports from multiple sources. Solutions require extra TOs to build queries through relationships, and significant scripting to hop between contexts and gather data.

A recent game changer is Bruce Robertson’s Virtual List (see Lisette Wilson’s recent article and her follow-up for an overview), which gathers data into a utility table for the report output. Combining SQL and the Virtual List seems like a perfect marriage. Martha Zink, Advocate of Awesomeness at MightyData, recently showed me an example of SQL combined with the Virtual List. This post borrows heavily from concepts in her solution.

SELECT From ( SELECT )

Again, for the SQL queries I used a plugin. Several plugins expose FileMaker’s internal SQL. I selected MMQuery from CNS Plug-ins. One nice feature of its Execute SQL function is the ability to set your own delimiters between fields returned in the result. The default delimiter is the comma, but when returning number you may end up with extra commas and I set my delimiter to the pipe symbol. Other plugins may not have this feature, and require you to substitute the comma for the pipe symbol before sending the data through the Virtual List process.

(I recently discovered another plugin, from the makers of Base Elements, that is not only free but lets you execute Shell and SQL, plus a host of other features.)

Although I’m not going to show long query examples in detail, I want to highlight one SQL idea used to build the queries that get sent to the Virtual List “engine”, and how to parse the output from SQL in order for the Virtual List engine to parse the data. The beauty of the SQL in Martha’s solution turns on an elegant and powerful technique in SQL: subselects.

In essence, this is a query nested within another query. The advantage lies in removing joins, which makes the query more readable. Subselects also allow you to build result sets against which you then can run other queries. For example, imagine if you want to pull a list of fields using an ID value or set of IDs. You first want to get that ID set, and then you can select information based on this list.

select <> from Table as T where T.ID in
( select O.ID from OtherTable as O where age < 45 )

To illustrate this process I added some formatting. The key lies in bracketing the select that creates the initial result, and then selecting from this above the result. This “inner” query inside the parentheses is processed first. With “in” the outer query looks at a list of records.

The inner select in the above example is a very simple statement. As with any other select in SQL you can change this to look across multiple tables, using multiple criteria, AND/OR, and other options. Using subselect is almost like creating virtual tables that exist solely within a query. Inside the <> would be all the fields required in the final result.

Dynamic Criteria by the User

In the solution that called for SQL, each report called for user-selected criteria, and searching across multiple tables. Relying solely on the graph or scripting to build the data would have required complex TOs and scripts jumping from layout to layout to build data, possibly even hard-coding criteria. By making the reporting dynamic, users could select from drop downs for different criteria, such as “single” and/or “double” graft. The query would build a pair of select statements, with the sub-select handling building a second list.

SELECT
DISTINCT P.id
FROM (( Patient as P
JOIN Admission as A ON P.id = A.idPatient )
JOIN Intervention as I ON A.id = I.idAdmission )
JOIN allvalue as AV on I.id = AV.idIntervention
WHERE lower( AV.Type ) = 'proximal'
AND lower( AV.Field1 ) = 'aortic arch'
AND lower( AV.Field2 ) = 'graft replacement'
AND lower( AV.Field4 ) LIKE '%single y-graft%'
AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'

OR P.id IN (
	SELECT DISTINCT P.id
	FROM (( Patient as P
	JOIN Admission as A on P.id = A.idPatient )
	JOIN Intervention as I ON A.id = I.idAdmission )
	JOIN allvalue as AV on I.id = AV.idIntervention
	WHERE lower( AV.Type ) = 'proximal'
	AND lower( AV.Field1 ) = 'aortic arch'
	AND lower( AV.Field2 ) = 'graft replacement'
	AND lower( AV.Field4 ) LIKE '%double y-graft%'
	AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'
	)

While both queries end up fairly complex, imagine the complexity if she had to rely on joins to perform both queries. All of this takes place inside FileMaker, using (in her case) the SyncDek plugin for the SQL. Combine the power of SQL with the Rapid Application Development (cool interfaces) of FileMaker, and you get true development synergy.

Select * From FileMaker

by 11 Comments

FileMaker Pro succeeds as a database system because of its many layers. As a rapid development database you can create a robust and elegant solution fairly quickly. Dig deeper, invest some time and effort, and you expose powerful tools and options that extend your solution. One such hidden tool is FileMaker’s internal SQL engine, which intrepid developers expose via plugins.

You might ask why bother, since SQL is a complex language with specific syntax we must learn outside FileMaker. Also, almost everything that SQL does within FileMaker can be natively accomplished without adding plugins. However, I believe there are times when the power of SQL combines nicely with FileMaker to produce better, smarter solutions, and this makes learning and using SQL worthwhile.

Some of the reasons to look at SQL in FileMaker include context-free queries, a simplified the relationship graph (fewer TOs in the graph), avoiding complex finds in scripts, and flexibility in building reports. In FileMaker, context is king. When creating or finding records through scripts, for instance, failing to start on the right layout could lead to drastic consequences. SQL removes these context constraints.

Several plugins enable access to FileMaker Pro 11’s internal SQL via functions. These include:

For my testing I used MMQuery, and primarily its ExecuteSQL( query ) function.

My task required comparing two tables with ca. 1 million records each and finding the records from Table A that were missing in Table B, and then make some adjustments to these records. Although I probably could have accomplished this in FileMaker, I like the elegance of SQL as it allows you to select data from various tables, and return only the results that you need. Never before having tried SQL in FileMaker, I started small, testing a basic query to discover constraints or differences in the SQL language from my experience with MySQL.

While testing the SQL queries I discovered that the tablename in each SQL statement refers to FileMaker’s table occurrence name, not the base table name. In my project database, one of the tables I queried lacked a corresponding table occurrence with the same name. The first query resulted in an error that the table did not exist, and when I switched to a table occurrence name, the query worked. Table names are not case-sensitive, so if you have a “Contacts” table occurrence name, you can use “select * from contacts” and the query works.

The Select statement returns single or multiple columns (fields) from single or multiple tables. The syntax follows a certain sequence. FileMaker, Inc’s ODBC/JDBC Developer Guide shows some SQL examples. The initially daunting syntax can be broken down into just the pieces you need from the options:

Syntax for SELECT clause in FileMaker

All text inside the square brackets are optional. I created a script to set two variables – a $query with “select * from contacts” and a $result with ExecuteSQL( $query ) so I could check these in the Script Debugger and use $result in subsequent steps in my script. The “*” in SQL is the same as “all” and gathers all the fields from all the records into the result. To narrow down the query you need to specify the field, or in SQL terms, the column. Querying across a single table requires just the column name, such as “select nameFirst from contacts”, but when querying multiple tables you need to include the tablename or table alias as a prefix to the column name, such as “select contacts.nameFirst from etc.”.

Now, returning to the problem at hand: how to get values from Table A that are not in Table B? Imagine two tables: author and book. In the author table we have an author ID field and the author name. In the book table we have a book ID field, an author ID field (foreign key), and the book title. I need to find authors with no titles in the book table. Starting with a simple join, I can see all the records that match (for these examples I have capitalized SQL commands, and added returns for the sake of clarity):

SELECT *
FROM author, book
WHERE author.id = book.author_id

But this query returns authors and titles that are in both tables, and I need the reverse. Using a LEFT JOIN produces the records that matches and those that do not match.

SELECT * FROM author
LEFT JOIN book
ON author.id = book.author_id

Since this returns all the data from both tables, I need to isolate the records that do not match. In SQL terms these would be any records with NULL values in the related field, ie. the book.author_id. By adding a WHERE clause to limit the found set, the result is what I need.

SELECT author.* FROM author
LEFT JOIN book
ON author.id = book.author_id
WHERE book.author_id IS NULL

Using “author.*” instead of “*” also limits my result set to just the records from the author table. No joins are required in the relationships graph, and my script can execute the query from any context. The excitement of seeing values populated into $result from the SQL query–even the most basic of queries–told me that using SQL as part of FileMaker opens up new vistas in developing solutions. In another post I’ll cover another powerful feature of SQL called sub-selects, and how this can simplify complex reports.

Reporting Dashboards

by Leave a Comment

With the advent of native charting in FileMaker Pro 11 developers can take advantage of powerful tools to design reporting dashboards to visually display critical information.

Images can convey information at a glance. Images can also overwhelm. A dashboard design should recognize that in order to communicate information effectively, the key is to illuminate, not overwhelm. Bright graphics and visual effects may have the opposite impact, hiding the data among fancy design and slowing down the user’s ability to interpret information.

Reporting in FileMaker entails sorting and sub-summary layout parts. The basis for any dashboard is almost the same. Data must be sorted and grouped. Charts can exist either as graphs alongside text, such as lists or sub-summary reports, or on specially crafted layouts focused on images only. The latter could display a variety of data from several different sources, all gathered and grouped in ways that make logical sense. I would argue that only the latter is a real dashboard, for while charts can greatly enhance data around it, a dashboard is essentially summary data only, and mostly graphical in nature.

Native FileMaker Pro charts come in only five variations—bar, horizontal bar, line, area, and pie. In addition, there are limits on how one can customize the appearance of each type. Labels with the chart appear untouchable; here a calculation option with Tooltips can prove invaluable. We can, to a limited degree, manipulate the dimensions of the charts by affecting other elements around it, such as the title and label size.

In order to best convey information about data through charts one has to select not just the right type, but consider size, what should appear in the labels, and color. One set of data might make perfect sense as a horizontal bar chart, and no sense in a pie chart.

Charts displayed within a record often require minimal preparation. Dashboards, where different sources can be combined into a single layout, usually require scripting in order to combine all of the elements. In brief, these scripts gather data points into global variables, for labels and/or values, with the last step switching to the dashboard layout where the charts are set up to receive the global variables and display the data.

Incorporating dashboards into FileMaker solutions is now easier than ever, with no need for plug-ins or external data such as the Google Chart API through web viewers. This graphical way of viewing reporting data can enhance solutions and provide developers and users with new tools to understand their data.

Let’s get started on your project.

It will be more fun than you think.

Get in Touch