FileMaker EDI with Excel: Getting data in and out of Excel using FileMaker Pro

There are several ways to get your FileMaker data into a format that can be used by excel. First, the Export Records script step – also available under File->Export Records – allows you to create a new file containing the found set in a format that Excel can open. Then there is the Save as Excel script step, also available under File->Save/Send Records As, which is similar to exporting but provides controls that are more like printing than exporting in that it allows you to save the found set or just the current record. Both of these options allow you to create a new file that can be opened in Excel.

But what if the file you want to put the data into already exists? You might have a file already prepared with a lot of time invested in creating formatting, formulas, charting etc. You might just want to add a few fields of data to a part of that file. None of the options mentioned above will help you. It seems, at first glance, that you would have to do it manually.

You may also want to get data out of and excel file and place it into FileMaker Pro. You could import the data but that will give you all the data in a given sheet. What if you only need a few fields or maybe just a single row of data?

We could use ODBC but I decided not to cover it here due to the wealth of existing information on the subject. If you want more information on using FileMaker with ODBC please check out the following links:

There are two additional tools we can use accomplish the goal of sending data to and getting data from a preexisting, excel document.

 

AppleScript

On the Macintosh we can use AppleScript to send data from a FileMaker field to a specific cell or group of cells and also get data from a cell or group of cells and return that data to FileMaker.

DDE on Windows

DDE gives us a capable set of similar commands to accomplish the same task as we can on the Mac. The syntax is quite different but the results are the same.

Script Debugger in FileMaker Pro 11 Advanced : Part I

Script Debugger is a great feature of FileMaker Pro 11 Advanced. This video tours the interface and demonstrates how you can use this tool to see the effect of script triggers.

Thanks to Alan Kirtlink  for producing this video.

The Log That Time Forgot

Ladies and gentlemen: the story you are about to read is true. Only the names have been changed to protect the innocent. I had a conversation yesterday with a customer who we’ll call Joe. A few months ago, we saved Joe’s bacon when a failing server brought his business to its knees. Joe has developed a FileMaker application to manage almost all of the data and information that are pertinent to his business. He developed this application twelve years ago in FileMaker Pro 3. Over time, he’s upgraded, extended, fed and watered his FileMaker application – keeping it healthy. Joe’s a pretty savvy guy. He’s never asked for professional advice with his FileMaker application. He’s also never sought expert counsel regarding his IT infrastructure which consists of a couple Apple Xserves and six to 10 workstations. It’s nothing extravagant, just enough to keep his business moving forward. Perfect…Until I received a frantic call from Joe. He explained that his Xserve had crashed. Evidently, a drive was full and Joe couldn’t figure out why. He also could not run his business. He was frozen with no way to generate revenue. Crippled. Dead in the water. You get the picture. Fortunately, we respond to phone calls like this regularly. Oliver Block spent the better part of that day working with Joe. Through a methodical and well practiced approach to troubleshooting, Oliver located the unlikely culprit. A log file. You know, one of the files on your server that ‘normal people’ never look at. The ones you’ve opened for a second just to see what’s there and found an unimaginably mundane list of repetitive repeating repetition. This particular log involved the faxing service. Someone probably enabled this this service accidentally, not really really knowing what it did and thinking, “what can it hurt?” The log file showed that no fax had ever been sent. In fact, it showed that no fax line could be detected. It had been attempted to find a fax line millions of times. Each time, a bit of text landed in the log file saying “I tried. It’s not there.” Four or five years later this file had grown to 35 GB. Joe was amazed. He didn’t even know the fax log existed. He didn’t know the fax was being attempted. He wasn’t watching ALL of the log files. The fact is, even the most cautious, over protective IT guy shouldn’t watch ALL of the log files on server. Knowing which logs to monitor and how to identify a problem in the ones you’re not paying attention to, that’s the trick. A 35 GB file alone won’t crash most servers. However, combined with an ever expanding FileMaker database housing hundreds, if not thousands, of images there is a clash of the titans. There is a happy ending to this story. Oliver was able to completely recover Joe’s FileMaker application. Joe is considering re-architecting the system to streamline image handling using 360Works SuperContainer plugin.

The moral(s) of the story:

It’s tough to know what you don’t know. You’ll pay for mistakes so why not pay to make sure there aren’t any? Technology failure is typically more costly than technology maintenance. What forgotten piece of technology will fail your business? Your servers were not made by Ron Popeil. They are not ‘Set it and forget it!’ technology. Who’s tending your critical infrastructure? We can help you answer all of these questions. Just Give me a call or shoot me an email to Jason Thomas.

Start Small

Have you ever started a huge project and not known where to begin? We all have. It’s not a comfortable position. What can compound the discomfort is the “help” you’ll be offered by so called “experts.” You’ve got some really heavy choices to make. Do you take on this project alone? Do these “experts” know what they’re talking about? Should we do this at all? Their sales guy said {insert line of B.S. here} but, do you trust this guy? There’s got to be a better way, right? There certainly is. Skeleton Key believes it’s prudent to test, test, test. From a technical perspective that’s obvious. However, you’ll also hear it from our business development team. We routinely recommend a ‘start small’ approach to doing business with us. It allows you to test our abilities; test our style; test our effectiveness. Think about it. How do you answer these questions during a buying cycle?

  • Why take on the largest project in my company’s history while depending on an unknown quantity?
  • What kind of people do they employ?
  • Is the technical staff as smooth as the sales staff? (In our case, smoother ~ but uglier 😉
  • It’s basic but, do they do what they say they’ll do?
  • How do they communicate?
  • Can I understand them or do they speak ‘techie’?
  • Bottom line, do I trust them?

You don’t really. It’s always a leap of faith. So take a shorter leap. Start small. For SK the process looks like this: 1. Together we identify a relatively small, finite project. 2. We’ll agree to a fixed price up-front so everyone knows the risks and rewards before we start. 3. As the project is completed you’ll have the option of working with SK on larger initiatives in the future. We’ll admit, Skeleton Key is not a perfect fit for every project or every company. We also know working with vendors in a continual sales cycle as they vie for your business doesn’t provide enough insight. We believe partnerships are born from experience. Please contact us to get the ball rolling. We’ll start small 😉