A Great Way To Spend My Retirement

Discussion created by richardsrussell on Dec 7, 2015
Latest reply on Dec 19, 2015 by keenatomko

I took early retirement from state service back in 2000 and have spent the last 15 years indulging my hobbies, one of which is pro bono FileMaker Pro database development for small non-profit organizations that couldn't conceivably afford my services if I were charging market rates. I also do a bit of work for small family-owned businesses.


I don't get money for this, but I do get back pats, attaboys, chocolate-chip cookies, and lots of smiles — something the State of Wisconsin (for all that I enjoyed my employment there) didn't really deal in.


Needless to say, I shamelessly repurpose work I've already created in order to satisfy the peculiar needs of each new client. And I've adopted standard naming conventions that let me easily copy and paste fields, layouts, scripts, etc. from one installation's system to another's.


One of my requirements is that each client come up with a unique, evocative name for its main database file. These are some of them:

• Aaron (after Aaron Copland), for Madison Youth Choirs

• All Eyes, for the UW School of Veterinary Medicine's Ophthalmology Department

• Belle Case (after Bob La Follette’s wife), for The Progressive magazine

• Bernstein (after Leonard Bernstein), for Wisconsin Youth Symphony Orchestras

• Bob (after Fighting Bob La Follette), for leftist political organizing

• Carl (after Carl Welty, Beloit-area environmentalist), for the Welty Environmental Center

• Fred (after Frederick Douglass), for the Liberty Tree Foundation for the Democratic Revolution

• Gandalf, for my own database of Dungeons and Dragons spells

• Gemini, for Millfab woodworking company

• Georgia (after Georgia O'Keeffe), for the Wisconsin Alliance of Artists and Craftspeople

• Harmony for DOERS, West Waubesa Preservation Coalition, and Fitchburg Fields (all run by the same incredibly active person)

• Horizon Rocks, for Horizon High School, a small specialty school for kids recovering from substance-abuse problems

• Kokopele (Hopi/Zuni god of chaos and tricks), for Planned Spontaneity event planners

• Lassie, for the Wisconsin Academy of Graduate Service Dogs (WAGS)

• Leo, the relative who got the proprietors of Á la Crate started on collectibles

• Morgagni (after Giovanni B. Morgagni, anatomist), for the Dane County Coroner's office

• Myles (after community organizer Myles Horton, founder of the Highlander Center), for Grassroots Leadership College

• Nelson (after your choice of Gaylord Nelson or Nelson Mandela), for generic Madison-area non-profits

• Paganini (famous violinist), for Music Makers, teachers of string instruments

• Snoopy, for Box Office Video, favorite cartoon character of the owner

• Solaris, for Full Spectrum Solar, an installer of solar electric and heating systems

• Solon (the lawgiver of ancient Athens), for Hestad Law Offices

• Steve (after Steves Jobs and Wozniak, Apple founders), for the Southern Wisconsin Apple Group (SWAG)

• Sushruta (father of optometry), for UW Hospital's Eye Pathology Lab

• Thalia (ancient Greek muse of comedy), for gThankYou, a gift-certificate company

• WWADB, the singularly unimaginative (but still unique) name chosen by the Wisconsin Wetlands Association


I also maintain a blog to which I can refer my clients for information on my standard ways of doing business. Saves me having to repeat the same explanations multiple times.


Each installation uses a standard folder/file structure:

Database Storage Structure.jpeg

The “Address Support” folder is static. It’s a lot of basic information needed by the main files in doing address processing and similar lookups:

Calculator — a simple 4-function calculator that can be made visible wherever it might prove useful in any of the other files

MSI — the Madison Street Index, unbelievably large amounts of data on every one of the 85,000+ addresses in the City of Madison (which you get even if you’re not in Madison, because the rest of the system expects it to be there)

WisXRef — the Wisconsin Cross Reference file, loaded with zip codes for every mailable location in Wisconsin plus a reduced set of them for the other 49 states, as well as many other goodies, including a full roster of the Wisconsin Legislature; all of the state's municipal clerks; 5000 personal names with their most likely sex; world airport abbreviations; a very handy list of common color names and swatches; a list of all the states or provinces for any nation in the world that has them; and state maps for the US, Canada, and Mexico


The “Active Databases” folder is where the client's main files reside. The contents of these files change constantly. Here's a typical set:

Addresses — a file dedicated just to the many ins and outs of address processing; it will allow (a) a single address to be linked to multiple people as well as (b) different addresses to be linked to the same person over different time frames or life situations (such as home vs. work), and will maintain a history of such links

Finances — somewhat of an overly broad misnomer, the “Finances” file tracks only income, not expenses. It includes within it a table for “Receipts”, but it also has tables for various codes that can be used to classify each receipt by source, project, etc.

Members — a unique record for each member of your organization per time period (semester, membership year, tour, etc.). It’s fully expected that lots of people you’re tracking in your master file will have multiple associated “Members” records, one for each time period of membership.

Universal — where you store info about your organization and staff

MasterFile — the big kahuna of your files, the place where you track information about “Beings” (comprising both people and organizations), including an “Electros” table where you record electronic means of communication (phone numbers, eddresses, and websites) and many other tables unique to your particular operation, such as shown in this E-R (entity-relationship) diagram for Horizon High School, which serves kids struggling to recover from substance-abuse problems:

Typical E-R Diagram.jpeg


As you can see, I also make extensive use of color coding. Each of my tables has an assigned color, usually alliterative with its name (blue for Beings, orange for Organizations, magenta for Members, etc.), tho sometimes evocative in different ways (green for Receipts, yellow for Electros). Each table's color gets used on explanatory diagrams (such as the one above) that appear on the master file's Router screen, in the "Relationships" tab of Manage Database, and on the header part of the data-entry screens for each table. I do this partly for my own benefit, because I'm a visual learner, but it also serves as kind of a subliminal "you are here" cue to my users.


I usually tell my clients that I view FileMaker Pro database development as pure enjoyment, my own personal version of video gaming. It gives me a chance to exercise both halves of my brain, the orderly, structured, logical, hierarchical side as well as the playful, colorful, imaginative, creative side. (You'd enjoy a lot of my error messages.) This has been a blast, and I have every expectation that it'll continue to be.