Portal filters and global fields: A cautionary tale

I'm up and running with FileMaker Pro 12! Playing around with portal filtering, I discovered an odd little nuance that can be quite important.

I have a "Menu" table/layout and three portals to three tables: Process, Task, and Project. The Menu table includes two global variables, Selected Process # and Selected Task #; Task has a Process # that indicates which process the task belongs to, and Project has a Task # that indicates which task it belongs to. The portals appear like this:

Process 1 --------------------------------- Task 1 ------------------------- Project 1
Process 2 etc.
...

The idea is that if you click on Process 1, the Task portal will only show those tasks essential to Process 1. (So if the Process is "Walk the Dog," the Tasks will include "Get leash" but not "Cast vote".) Once you select a Task (say, "Put leash on dog"), the Project portal will show only the relevant projects (e.g. in this case we only want dogs, so we'd see "Fido" and "Spot" but not "Dumbo").

Clicking on a Process sets the Selected Process # to the process's number, and likewise clicking a Task sets the Selected Task # to that task's number. The portal filters use these to determine which Tasks and Projects to show, by matching them against the relevant fields in the Task and Project tables. So far so good.

But things began behaving bizarrely. Although the Process and Task portals worked and updated properly, the Project portal—which should only be dependent on the Task #—would only update after I clicked the Process portal.

After much agonizing, I discovered the culprit. My table relationships looked like this:

Menu::Selected Process # ---------- X ----------- Process:: ID #
Menu::Selected Process # ---------- X ----------- Task:: ID #
Menu::Selected Process # ---------- X ----------- Project::ID #

All of the relationships were tied to Menu::Selected Process #. Lulled into a false sense of security by portal filter, I figured that since I was using 'X' joins it didn't matter which field served as the link between the tables. WRONG!

Once I changed the last relationship to

Menu::Selected Task # ---------- X ----------- Project::ID #

everything worked fine. Boo-yah!

Pre-Troubleshooting FileMaker databases

It's important to think ahead when designing software (or anything, for that matter). 10 minutes of planning will save 90 minutes of work.


So I'm designing a database and thinking to when IT (i.e. me) will have to troubleshoot it.

There are places where I might want to show certain types of data only when troubleshooting. I can put fields on all the layouts (of which we have a lot), but it's a lot of adding fields and then removing them when you're done. I'd rather just make them visible/invisible at will.

So for the moment, I've created a variable $$MetadataVisibility, a global one (obvious from the "$$") set by the following script:

1. Set Variable [$$MetadataVisibility; Value: If ( $$MetadataVisibility = 1 ; 0 ; 1 )]
2. Refresh Window[]

Then I add a "Conditional Formatting" formula to all of the troubleshooting fields:

not $$MetadataVisibility

 which if true sets the field text to match the layout background color.

In effect, by running the script (easily by pressing Cmd-1, since it's first in my scripts menu), I can turn all the troubleshooting fields on or off just for me (since global variables are for the local user, not for all users).

Of course, once we go live I'll need some safeguards in place: making sure only admins can access the script, that no one accidentally clicks on a field and modifies the data, etc. But that's trivial.

Counting unique values in MySQL

One of my areas of interest is digital humanities and text mining. Today I was looking for a simple way to count each unique value in a MySQL table.

For example, given a table "Text" with a column "Word", and with the following values:

Word
_____
To
be
or
not
to
be

... could we produce the following?

Repeats/Count
____________
to/2
be/2
or/1
not/1

A quick search brought up a number of possible solutions, of which the easiest was this one:

SELECT COUNT(*) AS Repeats, Word
FROM Text
GROUP BY Repeats
ORDER BY Repeats DESC;

With large texts, you may wish to eliminate some of the results:

SELECT COUNT(*) AS Repeats, Word
FROM Text
GROUP BY Repeats
HAVING repetitions > 1
ORDER BY Repeats DESC;

Voila'! :-)