Layout Mapping

Here’s a demo file I built to help with organizing FileMaker Pro consolidation and conversion efforts. This will create a list of layouts based on your specifications, and then save those layouts to PDF, with a table of contents.

Download LayoutMapping.fp7

UPDATE 030912 9:45pm – I found a bug in the scripts that made caused the table of contents to be added again at the end. That has been fixed.

UPDATE 031012 9:30am – there was one other bug where page one was not appending to the table of contents, making the toc piece disappear. That’s fixed as well.

Posted in FileMaker Pro | 3 Comments

Any Further Delays Have Been Postponed

It’s been awhile since I posted here. This is only because of unexpected delays. But wait no more! Hear me post.

A couple of things have happened: 1) the client that I was working with to develop the Dropbox solution had to switch IT companies, and everything is broken right now. So I can’t get screenshots or such to write about the project. Bummer. I was hoping this would’ve been resolved by now, but nope. I’ll pick up on the project as soon as I can.

And 2) in my small hometown of Dublin, Texas, Dr Pepper/Snapple decided that it was in everyone’s best interest to suddenly stop production of the oldest and tastiest version of Dr Pepper in existence: Dublin Dr Pepper. This of course could kill my town. I’m a bit concerned to say the least.

In a textbook case of how not to handle a social media campaign, the public outcry being heard around the world is simply being ignored by DP/S, and they’re hoping it will blow over, paying no attention to the daily #boycottdrpepper thread on Twitter.

So DP/S thinks that the perfect time to undo 120 years of soda history is after the worst drought in Texas history during the worst economy since the depression. As a result, I’ve been busy trying to help come up with plans for now and for the future. Needless to say, I will not be drinking a DP/Snapple/Cadbury/Schweppes product anytime soon, of ever again. And as you might imagine, working through this with the town is taking some of my innovation time.

(If you want to help my town out, you can read my blog post here.)

Speaking of delays, do you still have clients who work with FileMaker versions prior to 7? These systems are getting long in the tooth, and might need some extra thought. For instance, if you happened to be running a Windows 95 box, do you have replacement hardware and a replacement OS ready to deploy? Can you afford to wait on having to find and ship that stuff in an emergency? Do you have an archiving system for lowering the record count?

Better yet  – have you asked the client (or maybe you already know) what the barrier is to upgrading? Is it cost? I think we have crossed the threshold of it costing more to not upgrade than to upgrade. I’m pretty sure that cost analysis can be put down on paper. I’m also confident that most small to medium-sized business owners would like to be alerted to when they’re about to be on the losing side of the curve.

I have a client that’s still running in version 6. I don’t have to help them that often, really. They’re a very small niche insurance business, and everything just works right now. They have backup machines and OSes standing by, per my request. But even still, I’m concerned about them being left behind. Their business processes are changing, but the business model in their software isn’t. And cost is a really big factor for this shop of four or five people. We’re talking all new machines, and all new FM licenses, plus upgrade work on the files (rebuild, if possible to account for the changing business model) – that’s a lot of expense! Even if I comp the development (it really is a simple and small system), that’s still 6 new computers and all the FM licenses.

So my question to you is: what’s the solution? Talk to me about whether or not any further delays to upgrading a client’s FileMaker system should be postponed, and how to take care of the expense problem.

Posted in FileMaker Pro, Small Business | Leave a comment

FileMaker Inderect Networking Using Dropbox Part 2

In my last post, I covered the overview of this project, and the stuff you need to know before you get started.

In this installment, we’re looking at taking a FileMaker Go app, and setting it up to record field edits into XML. There are two things we want to accomplish here:

  1. Only show the latest edit per field
  2. Make the XML look beautiful so that it’s easy to read, edit, and debug

As I said last time, you need to remind yourself often that this is not an audit log, where we record every little change the user makes. The only purpose for this project is to take the current data structure of a Filemaker Go solution and sync it with a central data system. In this case we have a contact management system in the wild, and a system back at the office that incorporates the wilderness contact information into a much larger solution.

As you might expect, we have some name/number/address fields in our contact solution. Here is the current information the Go user has currently:

Fields before editing

Let’s edit some fields and see what happens:

Fields after editing

Here is the XML that was built after exiting the layout:

XML generated after editing

Note the structure of the XML. First, we declare that we’re editing a Bank record, with the tag. Next, we move to the next line and indent to show that we’re adding information that belongs inside this bank record. I always declare the bank serial (or ID, if you use that terminology), to immediately identify which record we’re looking at.

Next, there is a list of fields that have been edited. Note that underneath each field is the Timestamp, also indented to show that it belongs only to the data directly above it. (I’m new to XML, so I’m confident that my structure here is a bit loose, but I think I have the right idea.) We will need this Timestamp in the main system so we can tell whether or not this is the latest edit for this field.

Finally, we have a tag letting us know that we’re to the end of edits for this particular bank.

Let’s investigate how this XML gets built.

Building The XML
For each field that I need to send back to the main system, I have a set of script triggers. The OnEnter script trigger is called “OnTimer Stop”. This script stops the currently running OnTimer script that’s checking for activity. If there is no activity for awhile, the system switches layouts to the log-in screen – a screen locking mechanism. To prevent the OnTimer from getting in the way of editing, I simply turn off the current one by installing an empty one, which is all this script does.

Whenever a field is saved, I record that information into a global variable via the OnSave Bank Fields script. This is one of the scripts we will look at in the next post.

OnExit, I re-install the screen lock OnTimer script.

When a bank is selected, we take a snapshot in XML of what the field values are, and store it in the variable $$CurrentBankValues. Let’s step through the script, and see how that works.

Here’s the script:

Script: Get Current Bank Values

In the section labeled, “Set a list of fields we want to collect information on,” we set a variable to list all of the field names we want to collect information about.

Enter your field names like this

For cross-platform safety, don’t formulate the calculation thusly:

Save yourself some trouble, and don't do it like this. Though the neatness is preferred, the hidden character troubles are not.

That extra invisible returns can sometimes confuse Windows. Don’t ask me why – all I remember is that I had to change from the vertical orientation (my preference) to the continuous version (yuck) because there were problems. And I couldn’t get the ascii character to filter out. If you have a solution, post it in the comments. But the safest practice is the run-on-sentence version of the calculation.

In a moment, we’ll be looping through this list of fields.

Next, we empty the global variable $$CurrentBankValues, because…well…we want to set the values of the current bank, not the bank we selected last time.

Now, we’re down to the start of the fun part: building the XML. Obviously, we need a header explaining what kind of record this is. So we set our recently-cleared-now-ready-for-new-data global variable $$CurrentBankValues to:

”  <Bank>¶    <Serial>” & GetField ( Get ( LayoutTableName ) & “::zzk_Serial” ) & ”    </Serial>”

Creating the header

That’s two spaces in front of the Bank tag, and four spaces in front of the serial tag. Yes, every time you create a new level, add two more spaces. It looks tidy, and it’s easy to remember: multiply the level by two.

(The reason I’m indenting the Bank tag is because I’m going to eventually wrap the whole thing with user and transactional timestamp tags, which will occupy the no-indention level.)

The result looks like this:


Next, we want to loop through all the fields we defined earlier, and set their current values (go figure!):

I use often the Exit Loop If function for defining values in addition to its native function. In this case, incrementing the counter used elsewhere in the script.

The first thing I do is use my Exit Loop If statement to define the loop counter. This way, I don’t have to have a separate Set Variable ( ) step for declaring the counter and incrementing it.

I then define the field that we should be paying attention to. The way that I do that is to take the MiddleValues of the list of BankFields we defined moments ago, select the value that corresponds to the $Count, grabbing only one value.

TrimMV is my custom function for getting rid of the return at the end of the data returned by MiddleValues. That custom function is simply:

Substitute ( Text ; “¶” ; “” )

So, if you don’t want to mess with custom functions (or can’t), you’ll need to write it all out:

Substitute ( MiddleValues ( Text ; $Count ; 1 ) ; “¶” ; “” )

Lastly, if there is no value for Field, we exit the loop, because that will mean we’ve run out of field names to process.

You could use the FieldNames ( ) function to create the list of field names to process for a given table. And this, of course, would update as field names changed. The problem here is that I don’t really want to track all the fields. Just the ones that the user interacts with. Therefore, I’m using a manually-created list.

The final part of building the initial XML values is adding the footer:

“¶ </Bank>”

The resulting XML looks something like this:

<Consultant>Your Sales Consultant</Consultant>
<Fax>(111) 222-3333</Fax>
<Name>Sample Bank</Name>
<Notes>12/2/2011 4:21 PM Brad Stanford – Brad’s Test</Notes>
<LegalAddress>1234 One-Way Ave</LegalAddress>
<Phone>(888) 999-0000</Phone>
<MailingAddress>1234 Your-Way Street</MailingAddress>
<MailingCity>Any Town</MailingCity>
<ServiceType>BOLI & Benefits</ServiceType>

All we’ve done so far is click on a record, and create an XML snapshot of the current information in the fields we’re interested in. Next time, we’ll look at what happens to this XML when we edit a field.

Posted in Uncategorized | 1 Comment

FileMaker Inderect Networking Using Dropbox Part 1

Here’s a scenario for you.

A company approaches you to build a FileMaker Go-based contact management system for their sales force. The solution must be able to sync information back to the main office, and receive information from the main office. As always there are some conditions:

  1. The IT department doesn’t play well with others. Because you are an outside vendor they won’t let you touch the FileMaker Server machine, or the router, and they won’t let you remote in from the outside – not even with Go.
  2. You will have to work on the system via screen-sharing access to an internal machine, via TeamViewer or Apple Remote Desktop. No direct access to FileMaker.
  3. Third-party remote hosting is completely out of the question.
  4. You personally have no knowledge of setting up web servers, and don’t want to learn -OR- you know how to set up web servers, but don’t want to be on the hook for maintaing it for a client like this.

What are the typical options? Make it clear that as the developer, you need access to everything to make this happen? Don’t take the job? Honestly, the best scenario is probably don’t take the job! But, if for whatever reason you find yourself in this position (internal developer maybe?), then there is a possible answer using email and Dropbox.

I can finally say this isn’t a theory anymore, but a working model. I’ve finally finished version 1 of this setup, and it works.

The goal of my next few posts is to walk you through how to set up the same scenario I’ve set up, so you can sync your databases without a direct connection to your main system back at the office or at home.

Before we get started, I’d like to document what my motivations are, as they are quite simple:

  1. I like to innovate. That often means shamelessly taking paths that seem irrelevant to others, or spending extra time to peer-review previously accepted conventions.
  2. I like to provide the best technology solutions for my clients within the parameters they give me.

Yeah, that’s about it.

I’m not trying to create the next development convention, nor am I suggesting that everyone should do what I do. I’m trying to show you some data points from the Twilight Zone that might influence your own development practices. I love the unexpected little surprises of discovery, one of which is finding out what others do with stuff I post. So if you’re looking for the safest practices, the most regulated and tested systems, and the most predictable outcomes, then you’re looking for an airline flight to take you from point A to point B. But if you’re looking for radical concepts that might be useful to you in the future (and the fun discovering and testing them), then join me in my little two-seat experimental aircraft, and the associated joy of flight.

This is not the safe and predictable airline version of FileMaker development.

You are not here:

This is the experimental version of Filemaker development.

You are here:

So with helmet and parachute firmly secured for this test flight, let’s get started. Here are the parts as I think I’ll write them. I’ll update this list as necessary.

  • Concept Overview (in this post, below)
  • Creating the initial snapshot in XML
  • Writing edits to XML in Go
  • Sending and receiving
  • Parsing results in Go
  • Parsing results in the main system
  • Creating a return message to Go

Concept Overview
The concept was born out of desire to move information in and around Filemaker as quickly as possible. Long story short: FileMaker’s current scheme is record-centric. If your remote client asks for field 1 from record 1, FileMaker will send you all the fields and values of record 1, including unstored calcs. Sometimes, this can be super slow, depending on the data.

A better system would send you only the value for field 1. In a complex system, I imagine that you would also want to get any surrounding values, i.e. fields that cause field 1 to calculate, or fields that field 1 affects. Either way, that will not be covered here, but the concepts for doing so will.

While reading, remind yourself constantly: this is an exercise in data movement. Otherwise, it’s super-easy to get sidetracked down the “wouldn’t it be cool if” or “that won’t work because” trails.

Before You Start
Here are some things to consider before getting started.

First, understand that native email is insecure. Not that it’s introverted. But breaking into an unsecured email account isn’t exactly a trophy accomplishment for hackers, because it’s not the most skillful hack one can devise. So be aware that whatever information you transfer with email will not be secure unless you make it so by using an encrypting service of some kind. That will not be covered here. But as a rule: don’t use gmail or yahoo addresses. Use an email address with a company that the public at large has never heard of, and that will allow you to be checking mail every ten seconds.

As for my example in the wild, I discussed these things with the client, and they have assured me that the information being transferred is either public record or (in the case of the notes field) professional information that wouldn’t be compromising in any way. Be sure and have that same conversation with whomever you have to have it with before deployment.

I also spoke to my hosting service, and asked them if there was anything at their end that would kill this system because it pinged them so often. The answer was no, and I’m sure they appreciated the heads-up. I would advise you to do the same.

Second, I don’t have a lot of time to help you with this. I’ll try to answer questions as they come up, but you’ll have to spend some time experimenting to make this work for you in your scenario.

Third, while this method (and similar methods I’ve posted previously) allows you to communicate to remote databases with or without server and via runtime engines (where networking is not supported), understand that there is no money to be saved. You will spend A LOT of time tuning this system. Yes, once it works it works, but don’t expect this to be radically cheaper time wise. You’re re-creating a layer that already exists in FileMaker. Make sure there’s a reason to before you spend time on it.

Last but not least, you might encounter varying speeds in your email reception and delivery. Being on a ten-second checking scheme, your fastest update time is going to be around fifteen to twenty seconds. An email slowdown (like a denial of service attack, or a server with a cold) might kick that up to five minutes or more. YMWV.

Hopefully, in future version of Go (or with someone’s ingenious integration of Dropbox via the web viewer), we can get rid of email altogether. For the moment, though, I will sophomorically declare “First!” in the history of FileMaker/Dropbox development when it comes to data movement using email from Go. If there’s anyone else out there doing this, I’d love to hear from you.

With all of that preliminary stuff out of the way, it’s time to start building something. Next post, please.

Posted in Uncategorized | Leave a comment

What FileMaker Can Learn From Dropbox

So we’re back to the one thing that has already been requested of FMI – Dropbox access from within FileMaker Go. Or at least some type of file transfer service.

Here’s the thing: FMI has put all of its eggs in the Go basket. To make Go sing, we’re having to do some new things. But those new things might actually change the way FileMaker is deployed. If a company knew that they could reliably deploy runtimes on client machines, would there be a reason to pay for full licenses? And if FileMaker considers having the full app in front of its employees part of its marketing effort – because conceivably the employees will have a chance to experiment with Filemaker – then what’s the motivation to accelerate a scenario that will undermine those two things?

I think we have a chance to learn from Dropbox here, oddly enough. Dropbox in its short existence is currently being valued at $4 billion. That’s a company in which 96% of the users pay nothing. FileMaker’s value (for a 25-year old company) doesn’t even show up in Google, or on their company fact sheet. 100% of their customers pay for their products.

I don’t know about you, but I sense an opportunity. Or 4 billion opportunities.

This idea of free FileMaker everywhere is a far better marketing tool and will impact far more people than the ancient licensing model ever will. Here’s what I propose:

  1. $300 for FileMaker client, as usual
  2. $500 for FileMaker Advanced, as usual
  3. $800 for Filemaker Advanced Adavanced, with HTML5 instant web publishing as developed by those of us who want FileMaker to have this capability
  4. $1000 for Filemaker Advanced Adavanced Super Deluxe Special Extra that includes runtime creation, 1 year of dropbox connection, and the tools to make MVC happen with the web, runtimes, mobile, whatever for a single business.

The free part is being able to deploy FileMaker anywhere and everywhere without paying for every last installation. We would pay a premium for the ability to do that.

Having this built in runtime and web access to a central database (notice I left Server out) would be a killer app for small businesses. In this version of freemium, lot’s of FileMaker gets deployed in lots of places that it otherwise would not have been. SaaS would take off. More money would be made by all.

Scenario: I have a vertical market solution that I want to sell as SaaS. Each time I set up a company, it’s going to cost me $1,000. I charge the client a $500 setup fee, and charge $100/month. After 5 months, I’m in the black.

Notice, I’m not poo-pooing the company. I’m offering up a realistic variation of a proven-in-the-wild concept as an alternative to the obviously not-working-so-well system in place right now. I’d like to see FileMaker valued at $4 billion.

I think we’re three executive decisions away from that.

Posted in Uncategorized | 3 Comments

Almost, But Sandboxed

I really thought I had an option to email today. There’s this great little app called PlainText by Jesse Grosjean at Hog Bay Software that allows you to create plain text messages on the iPhone. These messages are ten synced to a PlainText folder in Dropbox. With this setup you can do really cool things like control your Mac from your phone or add tasks to OmniFocus or Things.

In fact, this level of control us precisely the direction I’m heading with my MVC setup. In essence, I want to use documents – or more accurately text instructions –  to tell FileMaker what to do. Ot doens’t matter to me how the text gets from one place to another, just that it does.

So your HTML5 interface sends data to a local runtime solution, and the runtime spits out the resulting HTML file to display the results. Once the user has their results, the runtime sends a document to the central database letting it know of the changes, if any.

See how this can work? We could send find requests, find results, add, edit, and delete.

And think of this: the model can store the HTML that defines the layouts. New layout? no problem: push the new layout to all your clients, wherever they may be. And scripts? well, scripting the UI will be taken care of with Javascript/jquery. So that gets pushed in document form as well. The rest of the scripts for typical functions can be written in a way that allows variables for table & field names, the control driven by the model.

But back to my story.

So I was thinking that since PlainText allows me something that Dropbox does not – locally stored files – I could access the file, substitute my FileMaker data, and have Dropbox sync it to the central database.

I found the path to the file with the help of another neat tool called iExplorer. Right after I built a web viewer test file and had it on my phone trying to ping the PlainText Hello.txt example file, I received an email from Jesse Grosjean, whom I had inquired of earlier in the day. He kindly explained that iPhone apps are sandboxed, which means the apps can’t talk to each other.


I’m sure that’s a good thing for security, but that was a real let-down after coming so close to an elegant non-email solution.

Posted in Dropbox, FileMaker Pro, Vision | 2 Comments


I just finished testing my Pop3It/Dropbox solution, and it works like a champ!

  • Used my FileMaker client to send mail to a remote FileMaker client that was using Pop3It to check mail every 10 seconds*.

Sending mail to remote listener

Database waiting for mail

  • The remote FileMaker client processed the mail, and created a pre-packaged reply in HTML, saving it with a pre-determined file name in the Dropbox public folder. (Because the document has the .html suffix, Dropbox renders it rather than sharing it.)

Mail received, note the list of field edits, with record serial numbers beginning each value. The blank after Fax means the value was removed.

  • Using the pre-defined Dropbox link, my Filemaker client checked a web viewer for a change in data every three seconds (giving the html a chance to reload each time).
  1. The ReceiveMail.fp7 file saves an HTML file to the Public folder in Dropbox. Go reads the return data through a web viewer. (Some of the data has been scrubbed for posting.)

  • When the new data is received (or a timeout reached), the process completes. If there is new data from the server, it parses it out. If not, an alert tells the user nothing new was downloaded.

* I checked with my provider  – Agathon Group – to see if checking mail so often would get me in trouble or shut down. Nope! But your mileage may vary, so be aware.

After that test was complete, I moved on to the iPad. No problems! It works from the iPad just like it does from the desktop. Love it!

All that’s left to do is finish my parsing routine.

I’m super stoked! Nice to have something go to plan for a change.

Maybe when things slow down, I can put together a nice video tutorial or something.

Posted in Uncategorized | 7 Comments