Experiences of a FileMaker Pro Database Developer

Import Fails on FileMaker Server

Yep, it got me last night. I wrote a script to import data into a reporting system (post coming soon on optimizing reporting). It worked fine on FileMaker Pro client, but failed when I implemented the script to run server-side. I thought I would add the routine to the nightly updates so any changed or new entries would automatically get updated into the Report System overnight. It’s all part of optimizing reporting so the user doesn’t have to wait for large updates to run when generating a report, only changed or new data.

So, why did it fail? FileMaker Server can’t import from one table to another when the files are hosted. Bummer and I forgot this. I’ll be re-writing the script to follow an export and import routine. Steven Blackwell and Wim Decorte wrote a great post on Server Side Import and Exports that I’ll be sure to follow. It helps clarify what can and can’t be accomplished. For the knowledge base article on the subject, check out ID 7035.

 

Post to Twitter

Know Who Your Users Are

Learn from the end users

I can’t stress how important it is to know who your users are and what they do. When developing custom apps, regardless what the underlying programming is, it’s so important to know how the user is going to use what you build. It’s even more important to clearly understand what the user does and how they do their job BEFORE you start planning your project.

I’m starting my last 2 module re-writes of a long term project and I really enjoy the information gathering and initial interface design aspect of programming. Gathering information allows me to get down into the trenches and observe and interact with the end users. How are they doing their job? Let me try? I get to ask a bunch of questions and let them complain to me about their day-to-day tasks. In a way it’s like I’m a therapist. I’m here to make their job easier and to help eliminate those annoyances.

At the same time, I get to think about how they might use a new feature or do their job a little different, more efficient. This is when I begin exploring the interface design and how the user will interact with the input tools. Is it clean, clear and easy to use? Can they figure out how to use it with little or no documentation?

With this comes change. I don’t know about you, but I don’t even like change. Just keep this in mind when you go to implement the new solution. Learn how to manage the resistance to change. When you are in the trenches, let the users know that change is coming. If they are part of this change, there is less resistance. Above all, make sure to train. If you’ve done your job right, once the user is shown how to use the new solution, they will see the improvements. Sometimes it takes them time to get used to the newness and repeated training sessions may also be necessary.

Post to Twitter

Preparing to Plant – Part 2

Organic Gardening

Organic Gardens by Gordon (my father's strawberries)

After the initial gathering, the first thing I do is jot down all the pieces to what I think will be in the system. For a project like organic farming, and learning what Donna wants to track, these are the pieces that I came up with, in no specific order. Some of these will be the modules or primary sections of the database.

  1. Contacts – this is where every person and company is maintained. Anyone that has anything to do with the farm will be entered here, from customers to employees to vendors, etc. All the pertinent information needed to contact the person or company as well as describing who they and what they do and offer is recorded here.
  2. Costing – this is where all costs are tracked, everything from seeds to labor. There will be a hard goods section as well as a labor/hourly section.
  3. Fields – this will identify the field and will track preparation, crops, rotation, weed/pest control, fertilization, harvest and yield. This section will be linked to costing and sales in order to determine pricing, costs and profits for each field. This section will also be linked to Plants/Crops in order to pull in information about the plants being grown in specific fields. The plant information will be used to determining growing season, fertilizer, weed/pest control, etc.
  4. Plants/Crops – this is were all the information for each of your plants is stored. Here you will define the plant, where to purchase, how to grow, how to maintain, etc. This will also be tied into costing and sales to determine the cost and profitability of each type of plant/crop. To be used in sales, the market and use options will be stored here as well.
  5. Sales – this is where all sales activity is tracked, everything from who to sell to, how to sell and where to sell. This section will keep track of your orders, invoicing and delivery.
  6. Budgeting – this is where all the planning, forecasting and pricing takes place. We will develop something similar to the Excel examples you provided here.

Those are the main sections as I see them at this time. I know I’ve been delayed in getting this off the ground, but hope this information is helpful. Donna, let me know if I am missing any of the main sections.

Next, I’ll dive deeper into each section and define some the items and fields that I think are important.

This is part 2 of a multipart series, see Getting Down and Dirty – Part 1 for the previous post.

Post to Twitter

Creating QR Codes with FileMaker

FMP QR Code

QR Code

This QR Code generator developed in FileMaker Pro is free and unlocked so it can be used in your own solution. The QR Code is still popular today, especially when used with smart phones to quickly launch a website for more information. The website could be anything from more information about the product you are looking at, the daily lunch specials at the restaurant you just passed or more information on a house for sale that you saw driving by.

Using Google’s Chart API for QR Codes, Custom Functions and the ScriptMaster plug-in by 360Works, I created the FileMaker Pro QR Code Creator. There are 2 options for displaying and creating the QR Code:

  1. Create QR Codes in Web Viewer
  2. Store a QR Code as a PNG file in a container field

Each option uses a different custom function. I was hoping to generate the QR Codes with a single CF, but the Web Viewer’s automatic encoding makes it basically impossible.

UPDATE 4/30/2011: I just updated the file so the plug-in function is now loaded within the custom function instead of having to call a separate script. All you need to do to make this work in your own solution is to install the ScriptMaker plug-in and to import my “QR_Code_Container” Custom Function from my example file below.

UPDATE 5/16/2011: Thanks to Daniel, he discovered that it’s required that my sample file is open before you import the Custom Function. If my sample file is not open when you import, the Custom Function will be commented out and missing the ScriptMaster GetURLAsContainerpart2 function.

UPDATE 1/21/2013: Google seems to be changing the API for QR Codes. I updated the link above, but it looks like QR Code generation may no longer be supported after 2015. The file still works as expected, but I am not sure how Google’s future changes may impact the function of the file. I will try to dig into this and if anyone else has further information, please let me know.

UPDATE 3/7/2013: Thanks to Bill, he helped me discover that I needed to clarify that not only is it required to open my sample file, but you MUST create a QR Code in my sample file first, before you import the Custom Functions. Creating a QR Code, or just changing one of the fields that is used to generate the code, causes the Custom Functions to load. If they do not evoke the Custom Function first, you will get  ( $url & $dim & $data & $encoding & $error & $margin ), instead of GetURLAsContainerpart2( $url & $dim & $data & $encoding & $error & $margin ).

UPDATE 3/16/2015: You can also try the QR Code Generator using the KAYWA API if you are having trouble with the Google API or want to see another solution. Check it out here.

You can download the fully opened, unlocked example file here:

QR_Code_v1.1

If you have any comments, questions, suggestions or problems with the file, please post a comment. Although I have done the best I can to create a file that works, is bug free and generates a code that is expected, use the file at your own risk and there is no implied warranty protecting data loss or the accuracy of the code generated.

Post to Twitter

Data Interchange

Recently I’ve been working on exports from FileMaker that are being used to import into other systems. FileMaker is great at accepting and interpreting many formats, but not so true the other way around. I find it interesting how inflexible those other systems are.

When exporting data in CSV format, FileMaker puts quotes around the data in every field, including number fields. Excel seems to only quote data when there is a comma in the data. I was told by another developer of another database system that the CSV format I’m supplying is not in “standard CSV format.” But, there is NO industry standard CSV format and it’s acceptable to put quotes around the data of every field. In order to make the client happy and conform to their inflexible CSV import, I have to open and save the file in Excel before posting to their FTP site. An extra step that’s totally unnecessary.

I had similar experiences with tab delimited files exported from FileMaker. A client requested the files as pipe delimited, but tab would also be acceptable although not preferred. Most of the work I do is on a Mac, but I make sure its compatible across both platforms. I usually export in a Unicode format. However, I was told there were no carriage returns, so I exported as ASCII (dos) format. I was again told there are no carriage returns in the document and everything is in one long line. They also wanted the file saved with a txt extension instead of a tab extension stating tab was specific for Tableau. This is just not true. Although .tab may be an extension for Tableau, it’s not the only application associated with the extension.

The actual problem with the file is the issue with Carriage Returns (CR) and Line Feeds (LF). Older Macs use a Carriage Return and newer Macs use a Line Feed, like their Unix counterparts, including Linux. Windows requires both Carriage Return and Line Feed (CRLF). If you transfer the files via FTP in ASCII mode the conversion between platforms is supposed to be taken care automatically. This does not always work from my experience. I’m looking for a solution now and will post it once I have it. In the meantime, I’m exporting on a PC.

Although FileMaker is flexible with file formats it can import, many systems are not. Keep this in mind when exporting data for use in other systems.

Post to Twitter

Server-Side Script Abort Fails to Disconnect

A new feature in FileMaker Server 11 is the ability to set a time limit on a script. When the time limit is reached, the script is supposed to abort and quit running. This feature works great most of the time, but sometimes it doesn’t. For one client of mine, I’m experiencing real problems with server-side scripts aborting based on my time-out settings, but not disconnecting. I’m running FMS v11.02 on Windows 2003 Server.
I set the abort in order that the scripts do not take too long to run. Based on the number of entries, the scripts will take longer. Mondays are particularly heavy based on entering data for 3 days (Friday, Saturday, Sunday data). When the scripts do not disconnect, the next time the scripts runs (every 15 and 30 minutes), this causes multiple instances of the scripts showing as though they are running. I believe they are still running in some fashion based on my processor load.
I try to disconnect them and I’m able to with some instances, but not others. The only way I’ve been able to definitely disconnect them is to stop the server, restart the machine and start the server again. I also know that I will need to run a backup first because the databases will not close properly nor will FMS stop properly. This is based on experience and similar situations in the past.
I cannot pinpoint when this will happen, but it has happened numerous times and usually after the Server has been running consistently for about 2 weeks or more. When it happens, users complain about slow performance and multiple processors are showing heavy use.
By the way, my procedures involve ALWAYS running a backup before closing databases and stopping FMS. I’ve been burned too many times.

Post to Twitter

Reporting

Today I’m working on some report glitches. I still haven’t tracked down the specific problem and I may need to wait when I’m onsite next week. I’m re-running the reports as I’m logged in remotely.

I’m currently working in a live development situation and bugs get worked out as users begin using new features. These new batch of reports are quite complicated and take a while to run since information is gathered from many sources. In the report module, records are supposed to get deleted and then updated and new records are imported. This assures that we are working with the latest and greatest information.

This week, a new user ran the reports, so I’m suspecting it might be a user privilege setting and this user doesn’t have access to delete the records. I think this user just might have the wrong privilege set assigned in Active Directory which means I have to adjust the privileges to allow deleting in the table or assign her to a new group. I can’t stress the importance of testing, test and then test again — then deploy!

More on the caveats of developing in a live hosted environment in future posts.

Post to Twitter

FileMaker Beginnings

I began using FileMaker Pro in the early 1990’s. Version 2 was just coming out and it was going to be cross-platform, just what I needed. I was the studio manager for an in-house art department and I needed to bridge the gap between the art department’s Macs and the product department’s PCs. There had to be a better way to manage work orders and copy. FileMaker Pro fit the bill. No more crazy file conversions and I could finally get those hand written spreadsheets into a database. The Mac artists could open FileMaker, find their product and then just copy and paste the product copy from FileMaker directly to their Quark or Illustrator catalog and packaging projects. Back then that was a big deal.

Having a design background, I fell in love with FileMaker Pro’s design capabilities. I used Microsoft Access when it first came out, about a year or so earlier, and got a taste of what I could do with databases. But, it’s design tools made it difficult to create a decent user interface. Using FileMaker Pro, I was able to take my design skills and create screens and reports that not only worked well, but looked good too. Now I could design and get organized.

From that point on, I continued to create and develop projects in FileMaker. In the beginning I was developing purely for my own needs and that later grew to other areas of the company. On the side, I started taking on small jobs including my mother’s estate sale business. Things grew and in 2000, I started The Business Helper, Inc. with my wife Nancy. Today, Nancy is a realtor with Coldwell Banker here in Danbury and I’m still developing in FileMaker Pro, just a bit older and hopefully wiser.

Many FileMaker Pro developers started their careers much like I have with no experience or formal training in database development or theory. We had to learn along the way. We are the “Bob the boat builders” of software development, just no longer building boats. I started this blog to write and share my experiences as a software database developer. These are my stories.

Post to Twitter