The best way to understand it, is by thinking of a spreadsheet with contact information: names, addresses, phone numbers, email, etc. Each person’s information is contained in a single row, and each column holds one specific piece of data. If that spreadsheet were a database, each row would be a “record” and each column would be a “field”. In a spreadsheet, you can view many records on a single screen, but very often, you cannot view all the fields for those records that same screen; you must scroll to the right. In a database, data can be presented in a spreadsheet format known as a table (or list), but more commonly, data is presented with all the fields for one record on a single screen, known as a form. In the form view of the data, multiple records can be viewed by “paging” through the data, one record at a time.
In my opinion, there are many advantages a database has over a spreadsheet. One is that custom views of the data, and custom manipulations of the data can be created, saved and re-deployed. For example, with a contact database, it is possible to build reports for mailing labels, call sheets, lists and rosters. It is possible to conduct a search by various criteria, sort the found records, switch to a report layout and store that process to be conducted again in the future. There is virtually no limit to the number of processes that can be stored in a database.
Another advantage is that a database can be used by more than one person simultaneously throughout an office, or over the internet. Spreadsheets can only be used by one person at a time.
I would be remiss if I didn’t acknowledge that very sophisticated data manipulations can be constructed and saved with spreadsheets. However, only someone capable of building that type of spreadsheet is able to use it, and even the most sophisticated users have been known to make mistakes which “break” underlying caclulations. With a database, it is possible to build a sophisticated application and to establish a set of user rights and privileges to protect the structure and contents of the database.
Sometimes, data needs to be stored separately to be made most useful. Related databases are put into use when there is a “one to many” relationship between data types. The best way to define a “one to many” relationship is by describing an example.
Imagine an invoicing system. There are customers, invoices and items. Customer records might consist of company, address, phone, email and contact name. Item records might consist of name, description, SKU, cost and price. Each invoice would contain the ordering customer’s details and the ordered item’s details. Each customer would have many invoices, and each invoice would have many items. In order to build a system that would customer details and item details onto an invoice, each data type would need to be stored in a separate database table.
I am a proponent of FileMaker Pro for several reasons.
For the developer, a FileMaker Pro solution it is easily taught to users, and for clients and end users, it is easily learned.
Depending on the client and the user community, as people become more sophisticated in their understanding of the application, users can be empowered to make changes to design and functionality. Some of my clients welcome this evolution and take over the responsibility for maintenance and development, and others reject the concept and prefer to bring all requests for database enhancements to me.
Lastly, for me anyway, it is an enjoyable medium to use.
Document and Email Handling in FMP
I hope you are having a wonderful holiday season, and I want to take this opportunity to send you my best wishes for a happy, healthy and prosperous New Year.
I also wanted to let you know I have been working with FileMaker Pro 13 and exploring the new version. I was initially skeptical about this version, but I have decided there are a number of new, worthwhile features and functions for both users and developers. But that’s not why I’m writing today. What I really wanted to tell you about is two discoveries I’ve made while working with FMP 13.
In response to a few client inquiries I have been experimenting with generating and handling documents in FileMaker Pro. It is now possible for me to deploy a technique to automatically create a document (letter, invoice, statement, newsletter, etc.), save it as a PDF file, insert a reference to the saved file into a database record AND attach the document to an outgoing email message. What previously had to be done in separate manual steps can be scripted to happen with the single click of a button. I should mention that this technique does not require FMP version 13; I can apply it in older versions as well.
I’m using this technique for my invoicing system.
The other email functionality I’ve been experimenting with is sending HTML formatted email directly out of FileMaker Pro, bypassing local email client software such as Outlook, Entourage, and Apple Mail, or web based email such as Gmail, AOL, Comcast or Yahoo. FileMaker Pro has been able to do this in plain text for quite a while, but it was impossible to send stylized text (special fonts, bold, italics, color and highlights) or insert graphics into messages. To make this work, I deployed a 3rd party plug-in from 360Works called “Email2″ which prepares HTML formatted email from within FileMaker Pro and sends it via an external SMTP connection. The plug-in costs $95 for a 1-user license, or $195 for a 10-user license, and is available for any version of FileMaker Pro. You can read about the plug-in at www.360works.com/email-plugin.
This post was originally prepared as a generic email newsletter and was sent to my entire list of clients, directly from my invoicing system running in FileMaker Pro version 12, using 360Work’s Email2.
If you would like to discuss how it is possible to automate your document creation process, and/or to discuss FileMaker Pro email for individual, group or mail merge messaging, please let me know. I would be delighted to set up a desktop sharing session and demonstrate these new techniques for you.