Experiences of a FileMaker Pro Database Developer

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
(FS/FMFQO/SWMDO)
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.

Thanks,
Matt

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:

Update

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
(FS/FMFQO/SWMDO)
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

Getting Down and Dirty – Part 1

Tractor

The old work horse of the farm

Donna Long from the Ponca Hills area just north of Omaha, Nebraska contacted me by posting a comment on my company website about using FileMaker Pro to help run their small organic farm operation. I thought this project would be a great case study on what’s involved in developing software using FileMaker Pro.

I plan to provide guidance and help Donna get started building her database. Together we are going to follow the steps necessary to build a database system. This is the first post of a series that will be published under Case Studies. Here you can follow the information gathering process, planning the database, initial builds, feedback, questions and answers and final deployment. My goal is to guide Donna as she does the work, but who knows where this will go and evolve. I welcome comments, feedback and suggestions as we take this journey. Let’s get started!

The first step is gathering information. In my initial interview with Donna you’ll see it’s important to take a genuine interest which helps to understand the project and, more important, the people behind the project. To me, the best part of any new project is getting to learn about a new business.

MATT: If you can tell me more about your farm and exactly what you need, that will be the first step. Are you using anything right now, Excel, Word, other? What kind of timeline are you on? I’ll do my best to help you get started, but it may take me a little while to carve out some time.
Where are you located? I love gardening and grew up with a father that always gardened and spent his last job as the Horticulturist for Winter Park, FL. I live in Connecticut and can’t wait for Spring and for the feet of snow and ice to melt!

DONNA: We’re in Nebraska, a little north of Omaha in the Ponca Hills area.
No, I have merely used pen and notebook so far.  We planted our first crop last fall….2000 garlic cloves….asleep under the snow for now, I’m hoping.  I will start some seeds indoors soon, and when Spring arrives will plant more asparagus, blackberries, blueberries, raspberries, strawberries, and annual vegetables.
We’ve established an early relationship with some restaurant owners and hope to entice Whole Foods and Trader Joe’s to sell our products.  And of course we’ll make the rounds of the Farmers Markets.  Buying “local” has just begun to take off here, and the same is true for the response to organic produce.  Our intention is to work toward certification, so we’ll be using only natural pest and weed control, and fertilization.
I’d like to track our costs, field preparation, planting information(including location to facilitate crop rotation), maintenance of plants(including weed/pest control), and harvest/yield information. Then on to sales information, including where sold and price.
All through this I think we should try to track our hours of labor, as well as ‘diary’ what that labor entailed.  So, maybe that’s leaning towards a “calendar approach”….I don’t know, just kind of free-thinking as I go here.  There may be something I left out…it’s late and I’ve been studying FMP11 all day….probably have a little ‘brain freeze’….
I’ve lived on this 10 acre plot for 19 years and have spent most of that time flower gardening with minimal amounts of vegetable and fruit efforts.  I’ve worked as a nurse anesthetist for over 40 years and would like to spend more time soaking up the smell of dirt!  So this will be a retirement pursuit for me and I’m as excited as when I saw my first radish seed sprout in Plant City Fla. 63 years ago.
Are you a native Floridian?  Winter Park is my favorite city in Florida….not least because of all the beautiful landscapes there.  I was born in Lakeland and my 91 year old mom still lives there.  My grandparents had a flower nursery in Plant City and some of my grandfather’s hardscapes are still being enjoyed around the town.

MATT: Pen and notebook is great. This means you already have a lot of thought and a system in place. The computer is just another tool to help with this system. Can you scan or fax me some pages from your notebook so I can see some actual data?
I’m originally from California and moved to the deep south when I was a teenager. My Dad’s sister married an Alabama southern family. We ended up moving across country, but I really enjoyed the freedom and hunting and fishing. I still love to fish, but haven’t hunted in years. I spent my summers with my uncle robbing bees. He was a school teacher and had about 10 bee yards, so we had all summer to get the honey.
I then ended up in Mississippi and finally in Orlando Florida through a Disney College Program. My parents later followed me to Florida and my Mom ran an Estate Sale Business and my Dad worked for the town of Winter Park.
I met my wife in Florida who was a native New Yorker and that’s how I ended up in New York and settled in Connecticut.

DONNA: Sorry I haven’t gotten back to you sooner.  Have been at a sustainable farming conference, working on logo and website, and planting seeds indoors.  Also work has been unusually busy so that gets first priority.
Thanks for hanging in with me.  I’m at work now but will write more later and I do have some additional info from the conference.
Question, can Filemaker take the place of excel for tracking expenses and income?

MATT: I look forward to more information…
Explain what you mean by tracking expenses and income? I need examples. Have you looked at Quickbooks for this?

DONNA: These are two resources from Roger Wilson at UNL that are put together using excel to track costs and income.  I would like to be able to use FileMaker for this information as well as other data we’ve discussed.
Agricultural Economics
Crop Budgets
Guess my question is basically can filemaker work sort of like a spreadsheet as well?
Thanks for the timely response today.  I really appreciate your interest.  By expenses, I’m talking about the costs of the crop, and income of course means from the sales of the crops.  This Mr. Wilson has put it together so you can actually determine what price to set for your produce.

MATT: Great information and yes FileMaker Pro can work similar to a spreadsheet even with a spreadsheet view.
Are you looking for FileMaker to actually determine what price to set for your produce? If so, I see that more in a phase 2 or 3 of the project. We want to get the basics in place and working before tackling those kinds of calculations.

I have enough information now to put together an outline and rough schema of the database modules/areas. I’m sure I’ll have more questions as I put that together. This will be the subject of my next post.

Post to Twitter