Things I Don't Do Well

I've been working with FileMaker Pro for about 20 years now, and this has let me pile up a pretty good bag of tricks. I take full advantage of things I've already learned and systems I've already developed when new clients show up. I can hit the ground running by just grabbing work I've already done that's kind of like what they want, copying it, and modifying it to their specifications.

Similarly, if existing clients want their database system to do something new, chances are fairly decent that I've already done something like it before and can adapt either my knowledge or some actual chunks of pre-existing databases to their needs.

However, FileMaker Pro is a pretty powerful piece of software, and it has capabilities that I'm merely aware of and haven't actually used. I'm listing some of them here as a humble disclaimer that some things would take me longer than others to accomplish, simply because I've never done them before and would have to learn from the manuals, tutorials, help files, and on-line developer community, so it wouldn't be fast, and the first iteration wouldn't be pretty.

Nonetheless, I'm always looking for nifty, fun things to do with FileMaker, and I'm eager to expand my skill set, so if any of these things ring chimes as to how they might possibly help you, let me know.

Subject Index

Bar-Code Reading
Custom Functions
File Sharing over a LAN or WAN
FileMaker Go
Microsoft Access
QuickBooks and Constant Contact
Runtime Versions
Web Publishing

Bar-Code Reading. I know that FMP can generate bar-code labels using a special (free or cheap) bar-code font, and it can read in bar codes using a (moderately priced) bar-code scanner. I've never done it. I've never seen it done. But it sounds like something that might make inventory control a lot easier.

UPDATE: As of 2013 December, FileMaker Go 13 for iPhone and iPad supports bar-code scanning natively.

Back to Index

Bentō. FileMaker Inc. also makes Bentō, a dumbed-down end-user database, designed to be a cheap alternative to FMP, mainly for home use. Its strength is that it's tightly integrated with standard programs that come with the Mac OS — Contacts (formerly Address Book), Calendar (formerly iCal), and Mail — which is why it's Mac-only; no Windows version. And it comes with a bunch of standard templates that many people can just use straight out of the box without modification. Customizability, power, and file sharing got shorted, tho.

It's possible to import Bentō data into a real FMP database, but I'd rather you just started out with FileMaker Pro in the 1st place. I don't work directly with Bentō. I'll figure out how to import data from it if you've run out of patience banging into all the walls of its limitations.

UPDATE: As of 2013 August, FileMaker Inc. has given up on Bentō and will no longer market it. Its support for Bentō will undoubtedly end sometime in the next year or 2.

Back to Index

Custom Functions. I've done a few of these, which I try to build into all my databases:
  • "Datify" turns a date that's stored or calculated within FileMaker Pro into a text string using my preferred version of the ISO 8601 standard, namely "YYYY Mon D" (for example "2014 Jul 4").
  • "Dollarize" turns a numeric quantity into a text string showing dollars and cents. Examples:
    • "12" becomes "$12".
    • "12.3" becomes "$12.30".
    • "12.34" becomes "$12.34".
    • "12.345" becomes "$12.35".

I'm game for trying more, if you've got particular calculations, whether involving numbers or text, that seem to come up a lot and would like to make them faster or easier.

Back to Index

File Sharing over a LAN or WAN. A "LAN" is a "local-area network", and I work with those all the time. But the meaning of "local" is kind of vague. Most of my work has involved a single office at a single location, but I've had some limited experience with people who are connected to a WAN (wider-area network) being able to get at a database system from miles away. It's kind of an obtuse, cranky thing to set up, but it seems to work OK once we figure out the specifics of IP addresses, etc. I rely heavily on local network gurus whenever I've done this, because I am definitely not a networking guy.

Back to Index

FileMaker Go. FileMaker Go is an app that you can use with iOS devices (iPad, iPhone, and iPod Touch) to interact with databases created using FileMaker Pro on a computer. While you can fiddle with data using FileMaker Go, you cannot use it to create the database itself or modify its design, which is one reason why they can offer FM Go as a free download.

I've done a grand total of one customization for FileMaker Go, and it was fairly straightforward to get the database loaded onto the iPad (once you know how, tho it was a bear to figure out in the first place). I learned a lot doing this one customization, mainly that an iPad is definitely not the same as a Mac, and I have to do lots of things differently, to the point of basically just building a whole separate set of iOS-specific layouts into the file and making sure that the database detects what kind of device it's running on when it first fires up, so it never takes you to any of the layouts for the other kind of hardware.

It was fun, tho, and I'd welcome the chance to do more.

I should say up front that the one file I did convert was the index to a music library that only gets updated 3-4 times a year, so the approach we used was to treat the FileMaker Go version as read-only. The user just walks around within the library room, eyeballing what's on hand, perhaps tagging items on the iPad screen, but not doing any data entry. Typing on a virtual keyboard turns out to be a major PITA with a handheld device (especially if one hand is in fact holding the device), and it's best left to actual computers.

It's also possible for FileMaker Go to be dynamically linked to the live database on a server and to modify that home database directly using a wi-fi connection. This is a bit more daunting, but now that it's possible, I'm pretty sure that sooner or later somebody's gonna want it done.

In between the read-only and live-connection options is a 3rd possibility, the "traveling salesman" approach. Using this technique, the agent in the field updates the data solely on the iPad (no live connection to the home office) but then syncs the handheld file up with the master computer file upon returning to the office. FileMaker Pro makes it possible to do this, but, since I've never done it myself, I'm not going to swear that they've made it easy. OTOH, it does avoid the problem of having your connection dropped halfway thru trying to update your file.

Back to Index

Graphing. You can use FileMaker to generate graphs and charts from your data. I've never done it, but it looks pretty simple.

Back to Index

Linux. FileMaker Pro is 99% cross-platform compatible between Macs and Windows PCs. (See the "Cross-Platform Issues" tab to learn about the 1% that's not.) Would you like it to also work under Linux or some other flavor of Unix? So would I. But it doesn't, so we're both SOL on that score. No indication out of FileMaker Inc. (notoriously tight-lipped about future directions) whether it ever will, either.

Back to Index

Microsoft Access. Microsoft Access is the world's most widely used end-user database manager. That's because Microsoft essentially gives it away free as a component of Microsoft Office. You can buy Word, Excel, and PowerPoint independently of Office, but they don't even pretend that there's a separate market for Access, because it totally sucks. Nonetheless, whenever a naive owner of Microsoft Office runs across something for which he or she needs a database manager, they figure "Hmm, we've got Access right here, why not use it rather than paying for a whole separate piece of software?" By the time they've finally figured out why (and why a lot of savvier users were willing to do exactly that), they've already invested so much time, money, energy, and trouble into their mistake that they're reluctant to abandon it — even tho that's exactly what they should do before they throw more good money after bad.

A lot of database developers were hopeful when Microsoft hoovered up Fox Software back in 1992 that they'd replace Access in their Office suite with Visual FoxBase, but no; they basically just put the fairly promising FoxBase on a shelf and let it molder, while continuing to run with that piece of crap that plagues computerdom to this very day.

I mention this because occasionally somebody will ask me if I'm willing to work on an Access database, to which I respond that I'd hold my nose for only as long as it took to extract the data from that miasmatic slum to import into a FileMaker Pro system, for which you should be eternally grateful, and I apologize for your having to clean up any puke I might generate in the process.

Blessedly, Access doesn't run on a Mac. They must have known better than to even try.

Back to Index

ODBC and JDBC. "ODBC" stands for "Open Database Connectivity". ODBC and JDBC are application programming interfaces (APIs), protocols whereby databases from different vendors can talk to each other. ODBC is an API for applications written in the C language, and JDBC is a similar API for the Java language. These APIs give client applications a common language for interacting with a variety of data sources and database services, including FileMaker Pro and FileMaker Server Advanced. All applications that support ODBC and JDBC recognize a basic subset of SQL (Structured Query Language) statements. Working with SQL, you can use other applications (such as spreadsheets, word processors, and reporting tools) to view, analyze, and modify data.

From what I understand, FileMaker Pro is kind of half-compliant with ODBC standards, in that it's fully capable of reading data from ODBC-compliant sources but is not itself very good at making its own data available to other database engines, tho it keeps getting better.

I haven't worked with ODBC at all. All of the databases I've worked on are self-contained. But there's a manual.

Back to Index

Plug-Ins. There are some features and functions that FileMaker Inc. never got around to building into FileMaker Pro, often because they were just too specialized for FMI to want to spend its scarce resources on. But, as is often the case, the people who need a particular functionality are willing to pay good money to get it, so a little asteroid belt of 3rd-party developers has sprung up around the shining star that is FileMaker Pro, and they've supplied a flock of plug-ins that will work with FMP to accomplish certain tasks. I have zero experience with these, but I know they're out there if I ever run up against a wall of something FMP can't do by itself.

Back to Index

QuickBooks and Constant Contact. Have you ever found yourself typing the exact same information into your accounting or contact-managemenet software that you've already got stored in your database and thinking "Man, I've already done this once. Why should I have to do it again? Why don't these 2 pieces of software talk to each other?" Yup, me too. But they don't. And it doesn't look like they're going to any time soon, either.

But, if you're of an entrepreneurial bent, you're thinking "Aha! Business opportunity!". And so it has come to pass. Productive Computing Inc. has issued FMP plug-ins called "FM Books Connector" and "EMail Marketing Connector". These are commercial products, which means you pay extra for them, but they can save you that amount of money in time and headaches over the long haul. I've never worked with them, but they look intriguing, and I'd welcome the opportunity to do so.

Back to Index

Runtime Versions. I've done a couple of these, and they're way spiff. The general idea is that I can design the living daylights out of a system using FileMaker Pro, then capture or encapsulate it in a runtime version that can be used by somebody who doesn't have the FileMaker Pro program installed (or paid for) at all.

For example, I ginned up a database that would let my sister allocate her Denver Nuggets season tickets every year among the 2-3 other people she shares them with. She uses the runtime version to enter the dates of the home games and the names of the opponents, then connects each game to the person who's claimed the ticket for it. It prints out reports and e-mails them to the other people in her ticket-sharing group — all without her ever having to own a copy of FileMaker Pro herself.

There are 2 main downsides to this:
  1. The runtime program only works on the platform you were running on when you captured it (a practice FMI refers to as "binding"). In my sister's case, no prob, since both of us use Macs. If I needed to do this for a Windows environment, however, I'd need access to a copy of FileMaker Pro Advanced running under Windows.
  2. While you can change the data in the runtime version (otherwise it wouldn't very well be a database, would it?), you can't change the file structure, layouts, reports, procedures, etc.

Back to Index

Web Publishing. This is directly from the FMP web-publishing manual:

There are three ways to publish your data using FileMaker Pro.
  1. Instant Web Publishing: With Instant Web Publishing, you can quickly and easily publish your database on the web. You don’t need to modify your database files or install additional software—anyone with compatible web browser software and access to the internet or an intranet can connect to your database to view, edit, sort, or search records, if you give them access privileges. You can use additional software to perform additional tasks, for example, to configure a firewall to secure your network.
  2. Static publishing: If your data rarely changes, or if you don’t want users to have a live connection to your database, you can use static publishing. With static publishing, you export FileMaker Pro data to create a web page that you can further customize with HTML. The web page doesn’t change when information in your database changes, and users don’t connect to your database. (With Instant Web Publishing, data is updated in a web browser window each time the browser sends a request to FileMaker Pro.)
  3. Custom Web Publishing: For more control over the appearance and functionality of your published database, use the Custom Web Publishing technologies available in the FileMaker Server software. Using XML and PHP, you can:
    • Integrate your database with another website
    • Determine how users interact with data
    • Control how data displays in web browsers

I've had some limited experience with IWP and none at all with static or custom web publishing. I do not know PHP. I know only the minimal HTML needed to maintain this blog with massive infrastructure support from Blogger. The one thing I do know is that you absolutely must have a fixed IP (internet protocol) address on the machine that's running your database in order for the internet to be able to reliably find it, and often your ISP (internet service provider) simply assigns an IP address to your computer dynamically, the next one up in its stable of available addresses at the time you log in. Working with an ISP to guarantee a fixed IP address is a business matter that I try to stay away from.

UPDATE: As of 2013 December, FileMaker Pro 13 has replaced IWP with a completely rewritten, easier to use, and more powerful internet interface called WebDirect. I also know essentially nothing about it, either, but I expect I'll be learning.

Back to Index

No comments:

Post a Comment