FileMaker Pro Import Problems

Am working on a tutorial for my eBook and using spreadsheets and databases for data analysis.  I created a file with 85 pretend records in Excel to import into FileMake Pro.  I copied the field names in FileMaker Pro, but I could not get the field names to match up exactly!  I did not see a choice to match up the field names - only to import or not.  Am sure that it is because I have used the application for less than a year.  My work around was to change the tutorial to import a file into a new table.

Will have to go back at another time to see if I can figure out what was happening.  

FileMaker Pro vs Access Update

I am working on a second tutorial for the eBook I am writing about using spreadsheets and databases for data analysis.  The FileMaker Pro database I have created has only two tables and four layouts, but it is over 500 MB in file size.  The comparisons I have thus far are:

• Access allows you to easily format text as zip codes and phone numbers, but FileMaker Pro does not
• Access allows you to do a Compact and Repair, but FileMaker does not have that feature in the menu
• Creating a button to open a URL was very easy with FileMaker
• Creating list features with Value Lists in FileMaker is child's play - so easy!
• FileMaker Pro works on the Mac and Windows computers and provides a 30-day free preview, but Access works only on Windows computers
• After you create a database with FileMaker Pro, you can sync it to your iPad or iPhone FileMake Go app.  Once it is synced, you can edit the database and enter data (a real plus!).  FileMaker Pro has settings for themes that work best on the iPad and iPhone.
• Calculating an age is more complex calculation in FileMaker (although may be more accurate) than using Access.
• If/then and other mathematical calculations are basically the same in both database software packages.

History of Database Software

Databases originated in the 1960’s with the development of computers designed to manage large business data sets.  The term “data base” was used in the 1960’s until the single word, database, became popular in the 1970’s (The Linux Information Project, 2006).  The concept of a desktop computer database did not develop until after the 1970’s with the development of personal computers.  A personal computer (PC) was a term used to differentiate a computer designed for use of an individual, rather than a business organization. 

Early Relational Database Development

Two early PC relational databases were dBase and FoxPro.  dBase was developed in 1978 by Wayne Ratliff, a programming contractor at the Jet Propulsion Laboratory in Pasadena, California (Martel, n.d.). Ratliff developed the database to win the office football pool (Unarchived, 2012). He ran the program on his kit-built home computer. Early PCs were kit-built for users as a hobby. Later, Ratliff collaborated with George Tate to create a company and market the software.  dBase was very popular during the 1980’s and later sold to Borland in 1993. dBase began to lose popularity as competition database software was released to the marketplace.  However, dBase is still available today from http://www.dbase.com/. 

Two other early PC database programs were FoxPro and Paradox. FoxPro was developed in 1984 by Fox Software and was acquired by Microsoft in 1992 (Wikipedia Authors, 2012a).  Richard Schwartz and Robert Shostak developed Paradox for DOS in 1985.  Their company was Ansa Software and later purchased by Borland in 1987.  Like dBase, dBase and Paradox were designed for developers with programming knowledge rather than home users (Wikipedia Authors, 2012b).  

The development of Windows with a graphical user interface (GUI) forced developers to redesign software.  In the early 1990’s all developers were scrambling to use GUI.  Paradox was designed for Windows by a different team of programmers.  Paradox for Windows was released in 1993 two months after the release of Microsoft Access, which had already had taken hold in the marketplace.

Two prevalent commercial personal databases used today are FileMaker Pro and Microsoft Access.  An historical view of the development for the two applications provides an understanding of the differences noted in the products used today.  Microsoft Access is available only for the Microsoft Windows operating system (OS). In contrast, FileMaker Pro is available for Microsoft Windows OS, Mac OS X, and the Apple iPad.

FileMaker Pro

The origin of FileMaker was the early 1980’s. The developers were Spec Bowers, Alan Albert, Dan Chadwick and Jega Arulpragasam, employees of Wang Labs in Lowell, Massachusetts (Koenig, 2004).  They designed their first database application, Nutshell, to provide an efficient user interface.  Leading Edge, a company that manufactured and sold PC clones, marketed the software.  About the same time, Apple introduced Macintosh computers to the marketplace.  Leading Edge would not sell to Mac market, so the developers redesigned Nutshell for the Mac computers, naming it FileMaker (Koenig, 2004).  Later, Claris, a subsidiary of Apple, purchased FileMaker. Claris redesigned, FileMaker to work with Microsoft Windows.  Afterwards, Apple dissolved Claris and established FileMaker as a separate company.  FileMaker, Inc., a subsidiary of Apple, markets FileMaker Pro software today.

Microsoft Access

Microsoft Access was first released in 1992 (the same year FoxPro was purchased) for the Windows OS (Beitler, 2010).  In 1993, a second version was released to be more compatible with Microsoft Office.  Microsoft released additional versions of Access to be compatible with newer iterations of Windows operating systems.  Access was a component of Microsoft Office Professional. Access 2010 is the most recent version of the relational database software, although 2013 is scheduled for release in 2013.  The target audience for the software continues to be small and medium-sized businesses. 

Summary

Database software development has mirrored the development of personal computers and mobile devices since the 1960’s.  The early databases were designed and used by computer programmers.  When desktop database solutions were marketed to small business owners, the software design began to change so that programming knowledge was not required.  Two popular desktop database solutions used today are Microsoft Access and FileMaker Pro. 

References

Beitler, B. (2010, December 29). A brief history of Microsoft Access  Retrieved November 25, 2012, from http://accessdatabasetutorial.com/tag/history-of-microsoft-access/

Koenig, G. (2004, April 2). The origin of FileMaker Pro  Retrieved November 25, 2012, from http://www.dancing-data.com/filemakerhist.html

Martel, J.-P. (n.d.). A personal history of dBase  Retrieved November 25, 2012, from http://www.staubassociates.com/dbase/bu03hist.htm

The Linux Information Project. (2006, June 22). Database definition  Retrieved November 25, 2012, from http://www.linfo.org/database.html

Unarchived. (2012). History of Microsoft Access  Retrieved November 25, 2012, from http://articles.webraydian.com/article31784-History_of_Microsoft_Access.html

Wikipedia Authors. (2012a, November 21). FoxPro  Retrieved November 25, 2012, from http://en.wikipedia.org/wiki/Foxpro

Wikipedia Authors. (2012b, April 19). Paradox (database)  Retrieved November 25, 2012, from http://en.wikipedia.org/wiki/Paradox_%28database%29

Database Learning Resources

Am still working on writing an eBook about using spreadsheets and databases for data analysis.  In the research, I reviewed the Geekgirl's Plain English Computing website at http://www.geekgirls.com/category/office/databases/ 

I especially enjoyed reviewing the 2007 article comparing database apps at http://www.geekgirls.com/2007/11/the-year-of-the-database/ 

Geekgirl's site includes several short tutorials - have copied the  names of the tutorials below from http://www.geekgirls.com/menu_databases.htm

Databasics	Database design from scratch
i: records & queries & keys, oh my!	i: introduction
ii: creating your first database	ii: simple database design
iii: data entry design	iii: the relational design process
iv: streamlining data entry	a data dictionary
v: getting information out
vi: exploring query types
vii: parameter queries
viii: queries with custom dialogs

Classifying the BMI Using the WHO Classification System with FileMaker Pro

In the previous blog, there was basic information for creating a calculation of the BMI in FileMaker Pro.

The Metabolic Syndrome database uses the World Health Organization (WHO) classification system online at http://apps.nccd.cdc.gov/dnpabmi/ and shown below.

The FileMaker Pro Formula is a nested formula that uses the BMI field in the Visit table.
The double quotes ("") around each classification level instructs FileMaker Pro output text, such as Obesity Class III. Note:  The Metabolic Database used the term "Overweight" instead of "Pre-Obese."

If (BMI >40; "Obesity Class III"; If (BMI>35; "Obesity Class II"; If(BMI>30; "Obesity Class I"; If (BMI>25; "Overweight"; If(BMI ≥ 18.5; "Normal"; If(BMI<18 .5=".5" nderweight="nderweight" p="p">
The data is displayed in the table as seen below.

Table 1: The International Classification of adult underweight, overweight and obesity according to BMI

Classification	BMI(kg/m2)
	Principal cut-off points	Additional cut-off points
Underweight	<18 .50=".50" b="b">	<18 .50=".50" b="b">
Severe thinness	<16 .00=".00" td="td">	<16 .00=".00" td="td">
Moderate thinness	16.00 - 16.99	16.00 - 16.99
Mild thinness	17.00 - 18.49	17.00 - 18.49
Normal range	18.50 - 24.99	18.50 - 22.99
		23.00 - 24.99
Overweight	≥25.00	≥25.00
Pre-obese	25.00 - 29.99	25.00 - 27.49
		27.50 - 29.99
Obese	≥30.00	≥30.00
Obese class I	30.00 - 34.99	30.00 - 32.49
		32.50 - 34.99
Obese class II	35.00 - 39.99	35.00 - 37.49
		37.50 - 39.99
Obese class III	≥40.00	≥40.00

FileMaker Pro - Calculating BMI

Kevin asked about calculating and classifying a BMI using FileMaker Pro.  The two formulas will be included in the tutorials for the eBook I am writing.  In this blog, I will describe how to calculate the BMI.  In the Metabolic Syndrome Database the patient's height in feet and inches are separate fields in the Patient table.  Generally, the height of an adult does not change.  However, Kevin, as a pediatric nursing practitioner student, should include height in the visit table.   A child's height will change with age.

Use the CDC BMI calculation appropriate for the client.  According to the CDC, the adult BMI formula is (Source: http://www.cdc.gov/healthyweight/assessing/bmi/adult_bmi/index.html )

Measurement Units	Formula and Calculation
Kilograms and meters (or centimeters)	Formula: weight (kg) / [height (m)]2 With the metric system, the formula for BMI is weight in kilograms divided by height in meters squared. Since height is commonly measured in centimeters, divide height in centimeters by 100 to obtain height in meters. Example: Weight = 68 kg, Height = 165 cm (1.65 m) Calculation: 68 ÷ (1.65)2 = 24.98
Pounds and inches	Formula: weight (lb) / [height (in)]2 x 703 Calculate BMI by dividing weight in pounds (lbs) by height in inches (in) squared and multiplying by a conversion factor of 703. Example: Weight = 150 lbs, Height = 5'5" (65") Calculation: [150 ÷ (65)2] x 703 = 24.96

I prefer to create and edit FileMaker Pro tables from the File > Manage > Database view.  The first step is to instruct FileMaker that the field is a calculated value.  That instruction opens a window to enter the formula.  A really easy way to create the formula is to choose the fields from the pertinent tables from the tables drop-down menu.  You do not need to insert an equals sign (=) before a formula because FileMaker does that for you.

Next, I had to create a calculated field to determine height in inches.  The formula noted below in the Visit table is
(Patient::HtFt*12)+Patient::HtIn

The two :: tells FileMaker to obtain the value from the Patient table. HtFt and HtIn are two fields in the Patient table. The caret (^) is used to instruct FileMaker to square the height in inches.

The formula for BMI is (Weight * 703)/(Height In^2)

That is all there is to creating the BMI formula.  Kevin would need to use the BMI formula for children.  It can be adapted from http://apps.nccd.cdc.gov/dnpabmi/ 

Catching Up and more on FileMaker Pro

There is much to share and just not enough time! :-)

I have been working on four lessons for one of the classes I teach on how to use Excel and FileMaker Pro.  I wrote out the content because there is not a book that addresses how to use Excel and FileMaker Pro from a nursing perspective.  

The good news, is that I believe I have FileMaker Pro figured out.  I developed a Metabolic Syndrome Registry database and have incorporated calculations that:

• Calculate height in total inches height in feet and inches
• Calculate the BMI from height in inches and weight in pounds
• Analyze the BMI for World Health Organization Obesity classifications
• Analyze the weight goal based upon BMI classifications

The similarities between Excel formulas and FileMake Pro are remarkable.  Where you use a comma in an Excel formula, you use a semicolon in FileMaker Pro.  The creation of concatenation is identical.  Concatenation is where you link two fields.  For example if you have systolic blood pressure in one field and systolic blood pressure in another you can create a new field showing 120/80.  

I really like the fact that FileMaker Pro does not use words that are so "techie."  For example, the term layouts is used (the default is single form, running forms, and table view) for any table or queries.  The term "queries" is not used - just a different layout. The term "script" is used instead of "macro."

Microsoft Access 2010 or Apple Filemaker Pro 12?

I have used and taught others to use Microsoft Access for the past 15 years but must consider changing to Apple Filemaker Pro.  Why? -- because Filemaker Pro is available for both the Mac and the PC.  Furthermore, Filemaker Go is a free app for the iPad.  Finally, Filemaker Pro is must easier to use than Access!

The only plus for Microsoft Access is the cost.  Microsoft Access is one of seven apps available from Office University 2010 for $99.99.  The academic version of Filemaker Pro costs $179.

I am teaching an informatics course this summer and discovered that 1/3 of the students are Mac users.  What it means is that I cannot use the lessons I developed on relational databases using Access 2010.  As a result, I spent time over the last couple of weekends working through the Filemaker Pro tutorial to learn how to use the software.

Although Microsoft Access 2010 is a great improvement over earlier versions, it is still clunky and not intuitive for learners.  There are essentially four different views - tables, queries, forms, and reports.  In contrast, Filemaker Pro allows the user to do the same functions using different "Layouts."

Filemaker Pro is available as a 30 day preview, so I can incorporate it into the course without additional fees for the students.

My next step is to import the tables for the Metabolic Syndrome database I created from Access into Filemaker Pro.  That process should help shed new insight on the differences between the two applications.