Drug and Alcohol Rehabilitation Center (FMP 12)

Struggling to manage their clients and programs with a closed, discontinued, unsupported system, a Drug and Alcohol Rehabilitation Center with multiple locations on the East Coast approached me to build a new system from scratch. The old system was no longer able to properly keep track of the changing nature of their work. Several functions had ceased to operate and had been migrated to Excel and Word "processing".

Using screen shots and field lists from the old system, and a wish list from the managers, I built a feature-rich new system to handle recording Clients, Addresses, Drug Test Results, Agency Referrals, Program Scheduling & Attendance, Document Archival, Behavioral Infractions, Court Apperances and Prescribed Medications. With this data recording function established, we constructed a number of counters, calculations, forms and reports: Monthly Program Review, Enrollment Rosters, Clinical Behavioral Summaries, and ID Badges (with bar code scanning scanning) and Weekly Attendance Summaries.

Click thumbnail(s) at right for larger image.

Contact, Work Order, Invoicing and Statement System

Early in 2012 I was having lunch with a fellow independant service consultant and we were lamenting the sorry state of our work logs and billing systems. I realized that a project I had been putting off for years, namely building a work order and invoicing system could benefit us both.

The system I built contains a record for each client and a record for each work order. The work order record allows for infinite number of line details describing the work done. The work order can cover one day, one week, one job, or one month; it is completely open ended and can be closed off any time by generating an invoice. The invoice layout can be turned into a pdf and emailed to the client. The system also has a module to record payments received, track the status of Accounts Receivable, and present a view of an accounts history or the history for all accounts. I also tacked on a notes/task system to keep track of pending items for client and vendor.

Migrating to this new system has paid unexpected dividents for my associate and me. Each of us has a separate, independant copy. For my installation, I have eliminated a cumbersome spreadsheet, paper work-log notes and eliminated hours I used to spend doing my monthly invoicing. Now I have the ability to monitor the status of each account in real time, and I can generate an invoice or a statement on demand in seconds. In addition, I can look over my past history with everyone I've worked with and construct marketing and communications campaigns.

Click thumbnail(s) at right for larger image.

Event Management

I was retained to build a complex relational database for a company that stages one-day educational seminars.

Each seminar they produce is offered between 10 and 100 times throughout the country. The logistics for each event, and the people engaged to operate and speak at each seminar are tracked in this FileMaker Pro database. Each event consists of sponsoring Organization, Venue, Speaker(s), Staff Assigned, Organizational Contacts and Venue Contacts.

Introducing the Filemaker Pro Database eliminated the use of dozens of spreadsheets, introduced standardized reporting and saved significant operational man-hours. In addition, data was housed centrally and shared across the organization inside their office LAN, and with remote workers over the internet.

Click thumbnail(s) at right for larger image.

Baseball and Softball Scorebook Statistics

I was a volunteer manager for my son's Little League team in the spring of 2009 and devised a FIleMaker Pro database to store the batting and pitching statistics of each of the players. My fellow coaches on the team and managers in the league encouraged me to re-sell this solution and Turn 2 Stats was born.

The program is a FileMaker "Run Time Solution" which means that users do not need a FileMaker Pro license to run the application. The program collects data for one team, in one league for one season. It is easy to enter game totals from the scorebook, and even easier to generate statistical summaries for each player and for the team as the season progresses.

Click thumbnail(s) at right for larger image.

The software is available for sale and to try at http://www.turn2stats.com.

Event Registration

I have created several different custom Event Registration databases for Event Planning companies that produce User Conferences, Sales Meetings, Product Roll Outs, Incentive Reward Trips and Executive Retreats for their corporate clients. Each of these Event Planning companies I've worked with deploys a complex FileMaker Pro relational database containing tables for Attendees, Hotel Rooms, Air Travel and Session Enrollments. The data abase provides the event planners and their clients and vendors with tools to manage registration data in advance of the event, as well as during the event itself.

Attendee rosters and lists, hotel rooming list, arrival / departure manifests and session enrollment reports, as well as counts and summaries are all part of the database solution and can be generated on demand.

Click thumbnail(s) at right for larger image.

Time Tracking - Project Scheduling - Revenue Forecasting

After shopping available software and web services, one of my Event Planning clients asked me to construct a time tracking system for their employees to use so that the Accounting group could use it as the basis for preparing bills to clients for staff hours worked. In discussing the specifications for this basic recording and reporting system, we realized if we could create a module to record Clients, and another to record Event Details, we could tie them all together and replace several labor intensive spreadsheets used by the executives of the company for planning and forecasting.

We set up a FileMaker Pro solution to provide employees with a convenient way to record the time they spent in various activities planning events. The Sales team enters the projects they are working on, and updates project status daily. The result was a system that allowed several groups to share data in a very dramatic way. The Operations Group was able to look at events on the books in terms of the calendar and the staffing needs. The Sales & Marketing Group records each event Request for Proposal and by tracking the sales status could look at projected sales revenue into the future. With all employees using the time tracking module, the Accounting Department could look at billable hours worked by all employees on all events and use the information to prepare final bills.

This system replaced dozens of spreadsheets and saved uncountable man hours. Previously, for time tracking, each employee kept a separate weekly time sheet that the accounting department used to compile a labor spreadsheet for each event. The operations department maintained a complex spreadsheet of event details and staff assignments. The Marketing department maintained their spreadsheets for Requests for Proposals and Business on the Books. The FileMaker Pro solution tied all of this data together and resulted in a dramatic improvement in efficiency.

Click thumbnail(s) at right for larger image.

Contact Management

Most contact data can be held in a spreadsheet quite nicely, but when it's time to do something with that data, or when the amount of information on each contact gets beyond a certain point, a spreadsheet is inadequate. I've constructed several contact management solutions in FileMaker Pro that allows the user collect and present large amounts of data for a single contact on one screen, to set "flags" on each record and take action at scheduled intervals. I've also added modules to generate (and preserve) mail merge letters and email messages, record dates they were sent, and calculate when the next action should be taken.

Click thumbnail at right for larger image.

Catalogue Sales, Purchases and Invoicing

For a company in the business of selling specialized music CD's, I developed a complex FileMaker Pro database solution to record vendors, items, customers and invoices. The system contained data details that could be used for presentation of inventory on web site. Predominately used by the fulfillment group for generating invoices and packing slips, it is also used to present historical sales data and is used by purchasing agent to order inventory.

Commercial Property Management

For a property management company, I constructed a complex Filemaker Pro database that handled rental buildings, rental units, owners and tenants.