Experiences of a FileMaker Pro Database Developer

FMS 15, ESS & AWS Redshift

This is part 2, configuring for Windows and FileMaker Server. Click here for Part 1 and configuring for a Mac.

fmsAmazonredshift_220x110Now, to get FileMaker Server running on Windows 2008 configured for ODBC access to Amazon Redshift. I first tried the specific Amazon Redshift ODBC driver and this did not work with the Actual ESS Adapter. The Adapter would not recognize Amazon’s driver. Maybe Actual can work on this. So, you will need to use the recommended PostgreSQL driver.

  1. Download the PostgreSQL driver and run the installer. The direct zipped installer can be downloaded here.
  2. After the driver is installed, go to the Start Menu, All Programs, Administrative Tools and open the Data Sources (ODBC), the ODBC Data Source Administrator.Screen Shot 2016-05-18 at 3.34.47 PM
  3. Click the System DSN tab, then click the Add button and select the PostgreSQL Unicode driver that you just installed and click Finish.Screen Shot 2016-05-18 at 3.38.06 PM
  4. I entered my server and credentials and set the SSL Mode to prefer, then left all the other default settings.Screen Shot 2016-05-18 at 3.41.37 PM
  5. Once setup, click the Test button and you should see a successful test dialog. Click OK and then Save. You are now done setting up the driver with Redshift.Screen Shot 2016-05-18 at 3.43.58 PM
  6. Next, download the Actual ESS Adapter for Windows from Actual Technologies. You will need to purchase the Server Edition for FileMaker Server for $299.
  7. After you purchase the license, download the driver and double-click the installer. When finished you should get this success dialog.Screen Shot 2016-05-18 at 2.44.56 PM
  8. Now you can jump back to the ODBC Data Source Administrator if it’s still open, or go back to the Start Menu, All Programs, Administrative Tools and open the Data Sources (ODBC), the ODBC Data Source Administrator.
  9. On the System DSN tab, click Add and then choose the newly added Actual ESS Adapter and click Finish.Screen Shot 2016-05-18 at 3.46.12 PM
  10. The Actual ESS Adapter DSN Configuration wizard will now pop-up, so first enter your license key, click OK and then click Next.Screen Shot 2016-05-18 at 2.52.12 PM
  11. Name your DSN and give it a description and select the PostgreSQL Redshift System DSN that you setup in the above stepson click Next.Screen Shot 2016-05-18 at 3.47.29 PM
  12. Then click Next if you want to set a log entry if you want to log long running queries, otherwise click Finish.
  13. Test your connection and if successful you are done configuring the ODBC setup.Screen Shot 2016-05-18 at 3.51.38 PM

The External Data Source configuration in FileMaker is the same setup as I described in Part 1, except that the Data Sources available are those defined by what is setup on the Host machine, in our case the FileMaker Server.Screen Shot 2016-05-18 at 4.21.07 PM

The above screenshot is from FileMaker Client 14 because I have not installed 15 on the Windows Server box yet. What I think this means is that the Actual ESS Adapter has been built to conform other SQL datasources to FileMaker’s ESS requirements and is just exactly that, an ESS adapter. I am not sure what this means yet and if it’s actually compatible and will work with other versions of FileMaker. Here is a screenshot of FileMaker Pro client v13 accessing the local PostgreSQL Redshift data source I configured in Part 1.FMP13_Redshift


There is a lot more testing needed to see if it would actually work in previous version or not. Just because I can add the table and do a simple find, doesn’t mean all things SQL will work. I need to dig deeper and find out if it was designed and created to only work with version 15. Regardless, I’m just happy that I now have a connection using ESS to our Redshift data sources.

Next, will be testing speed, adding and editing records, etc.

Post to Twitter

FileMaker 15, ESS and AWS Redshift

icon_fmpAmazonredshift_220x110I’m rather excited about the release of FileMaker 15. One of the hidden gems is the expansion of SQL database support within ESS (External SQL Data Sources) and the addition of PostgreSQL databases. This means FileMaker can integrate directly with Amazon’s Redshift Data Warehouse. ESS Redshift tables can be dropped directly as a TO in FileMaker’s relationship graph.

What you need:Redshift_ESS

How it works:

The ESS Adapter will present the Postgres data from Redshift to FileMaker in a format that FileMaker will accept as a supported ESS data source. As per Actual Technologies, here’s a simple diagram of the data flow:

FileMaker <–> Actual ESS Adapter <–> Postgres ODBC Driver <–> Postgres Database

Installing and configuring on a Mac:

You need to install both the Actual Technologies ODBC Driver for Open Source Databases and their ESS Adapter. First, setup and configure the ODBC Driver using the ODBC Manager from Actual Technologies. If you have already installed other FileMaker ODBC drivers, then most likely you already have this app. You can also use the iODBC Administrator from OpenLink Software.

  1. Create a new system DSN using the Actual Open Source Databases driverScreen Shot 2016-05-11 at 2.07.13 PM
  2. Name the data source, give it a description if you want and select PostgreSQLScreen Shot 2016-05-11 at 2.07.59 PM
  3. Enter the connection information but make sure not to check off the “Connect to server t0 obtain default settings for the additional configuration options.” I think there is a bug here and it tries to use a default database name that will produce and error.Screen Shot 2016-05-11 at 3.07.02 PM
  4. Since the connect to server option doesn’t seem to work, you need to know the name of your database, so enter it in the next screen, then choose your other options.Screen Shot 2016-05-11 at 6.01.32 PM
  5. Click continue, then test your connection.Test_Data_Source_and_Actual_Open_Source_Databases_DSN_Configuration
  6. Once the main ODBC driver is setup, tested and configured, you must create another DSN using the ESS Adapter driver.Screen Shot 2016-05-11 at 6.15.42 PM
  7. Name the driver so you won’t get confused, I added ESS to the name, add a description and choose the real ODBC DSN entry you just added in the above steps.Screen Shot 2016-05-11 at 6.17.16 PM
  8. When you click continue, the next screen lets you determine if the access is read only, otherwise it’s full access as long as your PostgreSQL login allows it.Screen Shot 2016-05-11 at 6.20.31 PM
  9. Once done here, you are finished with the driver setup and you can do another test just to make sure everything is working.Screen Shot 2016-05-11 at 6.21.25 PM
  10. Next, configure your External Data Source in FileMaker. Make sure to select the ESS Adapter DSN and configure your other settings.Screen Shot 2016-05-11 at 6.24.45 PM
  11. Now, in manage database on the relationships tab, you can add a Redshift Table to your Table Occurrence.Screen Shot 2016-05-11 at 6.46.29 PM

So far, I have only tested and configured the drivers on my Mac using FileMaker Pro Advanced. I have been able to do finds, create records, edit records and delete records in the Redshift tables. I’m working with tables that have a few hundred records to tables with over 400,000. At first glance and try, the speed seems doable. More to come soon.

In the next few days, I plan to setup FMS 15 on our Windows Server and will then provide instructions and details. After that I will be testing various scenarios and will post the results here.

See Part 2 for configuring FileMaker Server.

Post to Twitter

Unix Timestamp Custom Function

I needed to create a custom function to convert a standard FileMaker timestamp to the Epic Unix Timestamp. We are beginning to develop in Redshift on AWS and need to pass data from FileMaker to a table in Redshift.

I created the custom function to automatically adjust to daylight savings time here in the US. The function would need to be adjusted for use outside the US. You will also need Howard Schlossberg’s isDayLightTime custom function.

UnixtimeStamp ( _date; _time; _UTC_offset )
//UnixtimeStamp ( _date; _time; _UTC_offset )
//Written by Mathew Greger, matt@mattgreger.com
//Converts FileMaker Date and Time to Unix Timestamp
//Automatically adjusts for Daylight Savings Time (US)
//Requires isDaylightTime custom function by Howard Schlossberg

//_date = standard FileMaker Date
//_time = standard FileMaker Time
//_UTC_offset = Current Timezone Offset from UTC

Let ([

//daylight savings time = 1, otherwise 0
_dst = isDaylightTime ( _date);

//convert time to GMT, must pass UTC offset
_gmt = _time + ((5 - _dst) * 3600)


//convert timestamp to number
GetAsNumber ( 
//must use GMT for time
Timestamp ( _date;  _gmt ) )
//convert number to Unix Timestamp (difference in second from 1/1/1970 to 1/1/0001)
- 62135596800


Post to Twitter

It’s Been a While

It’s been a while since I posted to this blog. No excuses, just adjusting to working full time. There has been a lot going on with my FileMaker development and I even heard from Commander “Devil Doc” Baker who’s now stationed at Camp Lejeune. It was sure good to hear from him and more on that in a separate post soon.

I’ve been caught up in the day-to-day routine trouble shooting of data that I find it hard to carve out time for pure development. This is one of the challenges that I face working full time. Are data issues due to system development, user input or bad or missing data within exchange systems? They are any number or combinations of these. Only if…

  • Users would just read those dialog boxes that we so thoughtfully place within the routine.
  • We could always test ever single iteration and option of any new piece of code with reliable testers, and then do it again.
  • Third parties that we rely on to suck data into our systems would just be consistent.

So, here’s a glimpse of what’s coming up in the very near future, in no particular order:

  • Update on Commander “Devil Doc” Baker
  • FileMaker 12 – Part 2
  • Pieces of Code & Things That Go Bump During Development
  • What Was I Thinking? Revisiting Code You Once Thought Was Good

I plan to make a few changes and to spend a little more time contributing here. There will be a new section I can deposit snippets of development pieces, primarily for my own use so I can find them again and re-use them. And, to share with you, hopefully helping in some small way.

Post to Twitter

FileMaker 12 Review – Part 1

We’ve been running the FileMaker 12 family of products since August 2012. These are my impressions and experiences in a real world, live development environment. My experience is taking an existing FileMaker 11 solution and converting it to run in FileMaker 12. I wrote this up a few months ago and have noted a few updates since then. I will have a followup post with any new developments or findings soon. My findings are based on FileMaker Server (FMS) running on a Windows Server 2008 with both FileMaker Pro clients on Mac and PC. My workstation is a Mac, so many descriptions are based from FileMaker Pro on the Mac.

The Good

  1. Overall, bottlenecks are fewer. FMS seems to manage multiprocessors much better. And, the extra cache seems to help.
  2. Progressive backups and hard links really help solve a huge bottle-neck.
  3. The ability to control and restart specific processes on FMS is huge. This is really helpful if a server-side script is hung. The users never know I restarted the script process and I’m back in business.
  4. CSS, themes, fewer objects, new button controls, image fills, etc. are good things, although I’m just now beginning to learn and use.
  5. I’m trying to find other positive things about FMP client, maybe it’s ExecuteSQL but that’s a big nut to crack, see conclusion. I just have to keep reminding myself that this is version 1 of a new file format and good things will be coming.

Problems, Issues, Complaints

  1. Temp files haven’t really changed. What goes to temp, still goes to temp. Although the cache has increased considerable, things like imports still go to temp files, among other things.
  2. Things are sluggish, but it’s hard to explain. I’m not sure if it’s due to the “classic” custom theme due to conversion or not. Maybe when I redo my layouts, it will be better. Here are some of the slow areas, IMHO.
    1. Switching layouts.
    2. Going to Manage Database (this is not consistent and seems to be file related). Also seems like it’s more of a delay (with beach ball) the first time you enter. I also notice this in other development areas when you click OK to a calc or to a script. Just seems like saving schema changes are slightly paused or delayed.
    3. Same is true with layout work. Just switching to layout mode may cause a delay or beach ball, and saving it too. Again not consistent.
  3. Our overnight routines just take longer. I was hoping FMS 12v2 would solve this with the import issue being fixed. It did help, but only by about 30 minutes. So, it’s still a good hour longer than FMS 11. UPDATE: By the way, FMS 12v3 has not improved this either. I guess the good news here is, I rewrote my overnight routines, so it forced me to think about optimizing my code.
  4. There are problems with layouts after conversion. Not everywhere, but some elements get shifted. I’ve had to adjust a few summary sections in reports.
  5. Some sub summaries don’t update on a re-sort. I had to put in the old standby of Browse Mode -> Preview Mode -> Browse Mode. UPDATE: The FMP 12v3 updater solved these problems.
  6. I have sporadic problems in both lists and portals where clicking on a button selects the record above. This is extremely annoying and I cannot pinpoint when it happens and how it happens. I’m thinking it has something to do with the status bar if you show the status bar and the layout shifts down it’s not updating all the elements on the layout. This was a problem early on when FileMaker changed the position of the Status bar from the left to the top . UPDATE: Seems like the FileMaker 12v3 updates have solved these issues.
  7. I don’t think FMP 12 likes XP as well as Windows 7. Seems to be more problems on my XP machines that in Windows. Specifically font issues and FileMaker Crashing when saving a report to PDF. It’s more proof that XP is at the end of it’s life cycle.
  8. PDF files are a general issue, from bloating to crashing. Just MAKE SURE all your scripts are updated to save in Acrobat 7 or higher. If not, you’ll have PDF bloat of 10 times or more of the size the file should be. I think some of the crashing issues may be due to layout issues or Trebuchet! UPDATE: I’m not positive about layouts causing crashing and our crashing issues seem to be less and less now.
  9. Layout tools and behavior is taking some adjusting, but I’m sure I’ll get used to it. I just wish somethings had been thought out better. Here are a few annoying ones (UPDATE: This is not much better after some extensive use):
    1. Drag copy is totally bastardized – wish we had constrain back. Why can’t it at least snap to the grid when dragging? And, don’t decide to click the option key after you’ve positioned the element, it won’t copy. You must click the option key while the object is moving.
    2. If we have snap to grid turned on, why can’t the guides snap too?
    3. Selecting a guide and being able to enter it’s position numerically would be helpful.
  10. Themes – someday these will be great, but much improvement is needed. Some thoughts:
    1. More themes and customized themes, need to be able to create your own.
    2. Apply themes to multiple layouts and then manage these as a theme group. Change the group theme and all layouts get updated.
    3. Would love to have Custom Styles more like Quark and Pagemaker where we could attach a custom style to elements, edit this style and all the elements are updated across all layouts, etc. that have that style.


Because some of the major bottle necks are gone, specifically with Finds and other areas related to server, the “sluggishness” is tolerable and a trade off. I hear less grumbling. I really wish we had FileMaker Pro 11 with FileMaker Server 12. I really think FMP 13 is going to be what 12 should have been, but we’ve been down that road before.


I really tried to put this in practice for an addition to a report we put in place last week. Specifically, calculating the response rate based on Nielsen Household Demo impressions and calls received from commercials running on the networks. Thanks to Kevin Frank and his blog for great ExecuteSQL examples and a good place to start learning. I still need to wrap my head around this a bit more and I just wasn’t successful in a real world application. I found it too slow to calculate what I needed and it’s so un-FileMaker like. I ended up going back to the traditional FileMaker route and adding a few more fields and some more TOs than I really wanted, but the end result is quick and solved the need. I’ll try to revisit when I can carve out some time. I just may have had tables or statements not in the right order or something. I see the application for using ExecuteSQL for some things, but it’s not a be all and end all and you have to be very careful not to make it too static. We love to change field and TO names without having to think about the impact and ExecuteSQL will break if you do and you didn’t make the code dynamic. UPDATE: I’ll have a separate post soon on ExecuteSQL since I have used it since I wrote this, but I feel it only has specific uses.

Post to Twitter

Creating QR Codes with FileMaker and KAYWA API



I want to thank Kevin Murphy for submitting his FileMaker QR Code solution using the KAYWA API for creating QR Codes. With the fate of Google’s API for creating QR Codes unknown, it’s good to see alternative solutions. Kevin’s recent submission has spark me to look into creating a self contained QR Code generator within FileMaker. I hope to use the ScriptMaster plugin along with HTML5 to create QR Codes without needing to be connected to the Internet and use an API.

Kevin wrote:

We’re building an application that needs Web URL QR Codes generated in it, and I happened across your solution.  I noticed that you were concerned about the Google API going away, but looked through your code with some interest.  At the risk of telling you something you already know, I thought I would share what we eventually figured out for our solution.

I happened across kaywa.com, and they have a reasonable API for generating QR Codes.  It turns out that they have a direct URL for creating just a web URL QR Code – exactly what I was hoping to find.

We are developing in FMPA12, so while we grit our teeth through some of the growing pains, we benefit from several of the new functions.  One of the new script steps – Insert from URL – greatly simplifies the process of generating QR codes from URLs – as long as you have a simplified URL to draw them from.

I’ve attached a very quick sample file, if you’re into FMP12.

You can download Kevin’s sample file here:


About Kevin Murphy: Kevin is a typical FileMaker Pro developer that loves FileMaker because it just gets the job done. He is the IT Team Manager for Hancock Lumber Company in Casco, Maine.

Post to Twitter

Recent Changes

I’ve recently made several changes and have been absent for a while in both the FileMaker community as well as my blog. I hope to be on track in the next few weeks as I dive into some new tools for optimization and new areas with FileMaker products. I also want to get back on track with my Case Study for the Marine Psychiatrist.

One of the reasons that I’m behind in blogging is that I made a career move this year and gave up being a full-time independent developer, after more than 12 years in business. I am in the process of closing The Business Helper, Inc. and I now work as an in-house developer for a great client of mine, DirayTV. I’m very excited about this new venture and I get to be involved in more than just programing and development. As Director, Emerging Technology, I’m responsible for developing leading-edge applications to analyze broadcast, cable, and online media results, merging TV and Web analytics; while also being responsible for the strategic planning and research based on client needs assessments.

I also made design changes to my site and installed a new theme that is now mobile responsive, the Eleven40 theme by StudioPress. Brian and his team are doing great things over at StudioPress and they are now making many themes geared towards mobile devices. It’s nice to know that your site is mobile responsive and adjusts to various screen sizes from the desktop to tablets to phones.

Post to Twitter

Helping Our Soldiers Quit Smoking, Part 3

Planning the Database

The way I see the Tobacco Cessation database laying out is that there will essentially be two tables. One table stores the Marine & Sailor’s basic information. This is the people table. In this table is everything that doesn’t change. The second table is what I’m calling Appointments. I’m assuming Commander  Baker is collecting this information based on having an appointment with one of his Marines or Sailors. One person can have multiple appointments, based on them being seen multiple times over their treatment period.

Appointments is where you store everything that changes. I used my best judgement here to get started, so some of this information could go in the people table, but I figured this data could change sometime during a  future appointment.

Right now we are creating a method to collect the data. Once you have the data entered I can help you with reporting and making something meaningful out of the data.

Download the PDF to see Table and Field descriptions:

After sending the outline and how I planned to setup the databases, Commander Baker replied:

Commander Baker: I read the case study and I enjoy it very much. I hope this case helps others like [me] in trying to learn more about FileMaker Pro. I forgot to include additional fields involving 2 tests that I have used to asses Nicotine Dependency and psychological reasons for tobacco use. The first test is called the “Fagerstrom Test for Nicotine Dependence” and the “Horn Psychological Test.” The Fagerstrom Test is composed of 6 questions that depending on the answers, the total points can indicate highly, moderate or minimal nicotine dependency. The Horn test goes into why the person smokes and it has a total of 18 questions created in such a way that a total of 6 sets of 3 questions help identify the psychological reasons for smoking such as due to stimulation, handling of the cigarette, pleasure, reduce tension, craving and habit. Scores can vary from 3 to 15 points. Any score of 11 or above is considered high and score of 7 or below is low. Both tests are taken by all my patients so they can better understand their nicotine addiction and to find other healthy ways to handle their needs, for example, using diaphragmatic breathing to relax instead of smoking to release stress. Could you include the scores of these tests as part of the database? Enclosed are the 2 tests. For the Horn test, I just want the input scores for each section (Stimulation, Handling, Pleasure, Reduce Tension, Craving and Habit). I would like the database to show how many individuals had scores emphasizing “Stimulation” versus ‘Habit” for example. What was the most common psychological needs based on the Horn tests?, etc.

I want to thank you for your help in setting this up. I am going to read the materials you suggested in prior email. Thanks.

Very respectfully,

CDR Alfredo “Devil Doc” Baker, MD, MC, USN
Operational Stress Control and Readiness Team Psychiatrist
Regimental Combat Team 8
Delaram, Afghanistan
“With My Marines and Sailors, to the Gates of Hell and back, anytime, anywhere…” Iraq, circa March 2003

With this new additional information, I sent the following email with more questions. This is typical when building a database. It’s important to try and capture exactly what’s needed, but keep in mind this will probably change along the way too.

Matt: Did you get my other email entitled “TBC FileMaker Database” that I sent before this one? It had 3 attachments including an outline.

Are these tests a one time test or could they be given to your same patient more than once? Yes, we can add the scores to the database. For the Fagerstrom test it would just be one field for a numbered score, correct? I would then use a calculation field to interpret the score to the one if the 3, High, Moderate or Minimal. You could then search or sort by this data and you could group all your High Dependencies together, etc.

As for the Horn, I would include 6 fields with their scores then calculation fields that indicate High or Low for each.

We could then developing reporting based on the scores of these tests.

Let me know if you can’t find my other email.


PS. What’s the hour difference between here on the East Coast and Afghanistan? It’s 8:30AM now and I think it’s 5PM your time. Are you guys 8.5 hours ahead of us?

Below are the answers to my questions. It’s also great to hear that Commander Baker’s dedication to our soldiers is helping them deal with their addiction and that he’s having positive results with his program.

Commander Baker: Good morning Matt. Yes, it is 1708 here which means it is 0838 in the East coast, you are correct, we are 8.5 hrs ahead of you guys. Yes, my apologies, I saw the files and I like the way you organized it. While in the process of printing them, the power went off and it just came back. In terms of both tests, the are only given once. I agree on one field for the Fagerstrom test. Agree with the other fields for the Horn test. I am so glad that I was able to find you website. I will be starting another new group next week as the success of the first group is spreading like a wild fire.

I have not taken objective data or exact amount of cigarette these Marines and Sailors have already cut down as I told them not to stop smoking until after 2 weeks of taking Wellbutrin SR and starting the Nicotine patch. Some of them have told that the desire of smoking again is almost gone, others reported decreased desire to smoke as it tasted bad and it reminded them of the carcinogenic substances in the cigarette or dip can. Most of them have decreased their smoking (since the start of the course 2 weeks) about 40-70% of their daily consumption. I never expected such dramatic drop but it appears that the way I prepared the program, it looks like it is working; however, as I said before, it is early in the game but these are positive results.

Again, I am so glad that you are helping me putting this together. Tonight, I am going to print the reading material you recommended. We have our numbers of power outages so access to the internet and/or the electric power may be disrupted and sometimes the internet is so slow that not even a web page is able to load in less than 3 minutes. But most of the time is good.

Again thanks for your help.

In my next post I will have instructions on how to create the database file along with an example file.

Back to Part 2

Part 1 

Post to Twitter

Helping Our Soldiers Quit Smoking, Part 2

Recommendations for Resources: Where to start with FileMaker Pro

This is part 2 of a case study that will follow the development of a FileMaker database by a military psychiatrist to assist with his smoking cessation program for his Marines & Sailors in Afghanistan.

FileMaker Inc. has some great resources directly within FileMaker Pro and online at FileMaker.com. Before you get started, it’s important to have an understanding of some basic database and relational concepts. Start here and read all 3 parts.

This will give you basic concepts of relational databases. You’ll need to understand this before we can dive into FileMaker. After you read about relational databases, open up FileMaker Pro 11 and click on Help from the menu, then on Product Documentation and select Tutorial. This will provide a good read on how to use FileMaker Pro as well as hands on lessons. You can also access this from the Quick Start Screen or online directly here:

Also from the Quick Start Screen under Video Tutorials, click “Visit the Resource Center” or click this link:

There are some videos there that will help explain some of the features of FileMaker Pro as well as other video tutorials. You can also review starter solutions and access the tutorial and other resources under the Learning Library tab.

FileMaker offers intensive training and you can choose self-paced or instructor-led training. Information about FileMaker’s Training Series (FTS), finding a course that is right for you and how to order the manual that is used for training, can be found here:

Once you get through the above resources, check my Favorites for third party FileMaker Pro books and other resources. I will be updating my favorites in the next few days and listing some additional getting started resources and books, so please check often for updates.

Another link that I find helpful, is FileMaker’s Webinars. These are Web Seminars conducted by FileMaker Experts, other developers like myself as well as engineers and presenters from FileMaker, Inc. Click here for a current list:


After posting part 1, I was contacted by Kevin Mallon, FileMaker’s Sr. Public Relations Manager:

Kevin Mallon: We caught your blog about the military psychiatrist creating a database to assist with smoking cessation among the marines in Afghanistan and I wanted to know what you think we could do to help.

We could provide our FTS along with third party FileMaker Pro “how-to” books.

I was also wondering if he could use FileMaker Go running on an iPad?

Look forward to hearing from you.

I forwarded the email to Commander Baker and also asked him what computer equipment he was currently using. FileMaker Go would also be a great addition, especially if going paperless (more on this later). Here’s Commander Baker’s reply:

Commander Baker: Gentlemen:

Greetings from Afghanistan. Wow, I am overwhelmed from the great support. In terms of equipment, I have been an Apple user for the last 2 years. I have a Mac Book Pro and I used the iPad for my daily operational, administrative and medical practice. By having the information and database in the iPad definetely sounds like a superb idea.

Kevin, I would love to have a book or training material so I can learn FileMaker. I am kind of a visual/kinesthetic learning so a lot of pictures help my learning. I have FileMaker Go on my iPad but I have not used it yet as I didn’t have any database at this time.

Matt & Kevin, you both have been heaven sent angels and I greatly appreciate your support. As my mother always says, there are indeed angels among us.

Thanks guys

Part 3, Designing the Database

Back to Part 1

Post to Twitter

Helping our Soldiers Quit Smoking, Part 1

*This is part 1 of a case study that will follow how we get organized and setup a relational database with FileMaker Pro.

Today was an interesting day and one that makes me a bit more proud to be an American. Although I have relatives and friends that serve in our armed forces, at times I, and we all do, tend to take them for granted. We forget that they are defending our great country.

I started my day as usual by reading email. I received an inquiry asking for help with FileMaker and how to get started setting up a database. This request was from a Commander in our Navy, CDR Alfred “Devil Doc” Baker, MD, MC, USN. Below is our email transcript. I have asked CDR Baker to share this as a case study in order that it might help others when they begin a database. To me, this is more than just a case study, it reminds me how I first started but more, it enforces why I love developing and why I use FileMaker Pro. And, maybe in some small way, I can do something to help our soldiers.

CDR Baker: Greetings from Afghanistan.  My name is CDR Alfredo Baker.  I am presently deployed to combat operations to Afghanistan with the Regimental Combat Team 2 as a psychiatrist.  I recently purchased FileMaker Pro 11 because I wanted to learn how to use a database for first time and this software was highly recommended especially to those using a Mac.  I have a question.  I recently started a Tobacco Smoking Cessation Program for my Marines and Sailors and I would like to gather not only their information (no personal ID) but their amount of tobacco used, type, years, quitting times, etc., so I follow their progress and probably gather enough data to publish my findings later on.  Where do I start?  Could you be kind in guiding me on the basics steps in building a database and relational tables? Thanks.


Matt:  I’d be honored to help anyway I can. Let me formulate an outline along with a few questions and I will email you again later today or by tomorrow. I will even put together a starter file for you in the next few days in order to help get you going.

Would you mind if I make this a case study on my blog, FMPro Database? It might help others like you that are beginning a database for the first time as well. However, I understand the privacy if you’d prefer not to.

One question before I get started, if you are not going to store a personal ID, how are you going to keep track of your Marines and Sailors? Just by their name? If you can send me a list of things (fields) that you want to track both for the personnel data (First Name, Last Name, DOB, etc.) and the tobacco data (type, amount, year started, total years using, how many times try quitting, etc.), I will use these fields in my starter file.

Thank you for your duty and service to our country. All that you and your men do is appreciated beyond what words can express.


CDR Baker:  Greatly appreciate you words of support.  The attached document have the list of the fields I am using as part of the registration to the tobacco cessation program but also as to the determine their specific treatment based on the amount of tobacco consumed.  I can use their last 4 of their social as ID or I can assign a random number as their ID that I will give each of them.  These kids are smoking so much and some of them are dipping as well (40%).  In addition, their spouses are also smokers with little kids at home.  This is a way for them to deal with the every day danger they are exposed to. The good news is that I am having over 90% success rate although it is still in early phase but these Marines and Sailors are determined to stop.  These young men are indeed working hard to preserve freedom and they do it with a smile int their face.  That is one of the many reasons I enjoy taking care of them besides that I am serving this wonderful country of ours.  That is the reason we deploy overseas so nobody will come to our country and tell us how to live our lives or take our freedoms away, I will be fighting to death before that happens.

Again, thanks for your kindness.  I downloaded their user manual and it was not as helpful in explaining how to set it up.  Do you have any recommendation in terms of something that I can read to better understand this software?  I don’t mind at all if you use my project as to teach others how to set up a database in this situation. Thanks.

Very respectfully,

CDR Alfredo “Devil Doc” Baker, MD, MC, USN
Operational Stress Control and Readiness Team Psychiatrist
Regimental Combat Team 8
Delaram, Afghanistan
“With My Marines and Sailors, to the Gates of Hell and back, anytime, anywhere…” Iraq, circa March 2003

In my next post, Part 2, I will answer CDR Baker’s request for recommendations that will help better understand how to use FileMaker Pro. I will also share my outline and questions.

Part 2, Recommendations for Resources: Where to start with FileMaker Pro

Post to Twitter