PiVOT details

As the meeting rolls on, I’ll write some of my thoughts and observations here.

A sample version of this database is hosted on the beezwax server for a short time to play with connect to:

fmnet:/fmnexus.com   from within FileMaker, and open either PiVOT 9 or 10 to match your version of FileMaker. User: portland, pass: beezwax. This allows you to explore the graph, fields, scripts, and functions demonstrated at the meeting.

If you didn’t realize, FileMaker plug-ins can use SQL code against FileMaker data, such that scripts can call those plugins to query records, create, edit, and delete. Also, some design features can be accessed, such as creating and deleting tables. Rick’s solution uses this power to great effect.

First off, by using the DoSQL Plug-in, Rick gains great access to speed and features that don’t exist natively in FileMaker. This certainly adds some layers of complexity, and that is largely managed by a series of custom functions that are called in scripts. The custom functions build sql code based on the need, for selecting and returning data, creating records, deleting or updating, etc. Then, these functions call a simplified single custom function that contains all actual calls to the plug-in. This is great, as it would allow a change to a different plug-in to happen in one place, should that ever become desired.

One of the things he shows is how using SQL calls with portals is so great for searching on partial words, and on the middle of word, as well as searching on one or more fields in the table, which can be controlled by selecting fields from a value list. Also sorting the portal is handled in SQL by the use of a custom function, using a very clever method. Basically, the portal itself has just one sort field, but the contents of that field are set by the custom function that looks at the records currently in the portal.

As each line in the portal is clicked, a field is set with the ID of that line, which is used with conditional formatting to hilight the line, and also keeps that key value to do work on the line, such as navigation elsewhere, or showing more detail for that selected record.

All the basic functions use SQL in the background, but don’t require a great knowledge of SQL, which is nice. If you want to delve in to the SQL, you can also easily do that by either typing code into a field.

Actual implementations based on this framework have stayed very close to the original. Simple solutions can be built in about an hour per table, which is really fast. After that time, I guess the work is just the specific custom needs for each module, which would be greatly simplified because of the tools in here.

Naming conventions are very clear, and require very little explanation. This is both for the graph, fields, scripts, and layouts. One cool idea is naming scripts with a clear English name, then some single characters for unique aspects about the script, such as .f.d.c. for a script that runs with full access and both creates and deletes records, and then a number of spaces at the end of the script. In this way, you can avoid using folder names, and instead have an empty script called ‘Customer Stuff    ‘  (with 4 spaces at the end) and then if you want to see all your separator scripts, you can just type 4 spaces in the search. Tricky.

Navigation is multiple window friendly. It remembers navigation settings and hilights in each window that is open, which is very polished.

Icons in the menubar are in small or large sizes, The large size is shown when you’re actually in that module/table. These are set with a function. Further, they can be screen resolution dependent based on the size of the layout you choose in preferences.

On the graph, everything to the left of the PiVOT table (the main table in the template) is Utility. Everything to the right is Data. Another great idea here is for any relationships that use referential integrity (that checkbox that deletes related records from table B when they’re deleted in table A). The idea is that these TOG are in a separate, clearly marked portion of the graph, so you never have to go searching for them. This is a great idea.