Archive | Level: Intermediate RSS feed for this section

FileMaker Blogs

Posted on September 1, 2012 by

Recently a question came up on one of the lists asking about intermediate-to-advanced FileMaker blogs. Here are some I like to read (no doubt I’ve missed some good ones; I apologize for any inadvertent omissions).

Cimbura – http://cimbura.com/tech/blog

DB Services – http://www.dbservices.com/articles

FileMaker ’n’ the Web: Lessons Learned – http://blog.jsfmp.com

FileMaker Weetbicks – http://www.teamdf.com/weetbicks

FMDiff – http://fmdiff.com/fm

FM Forums Community Blog List – http://fmforums.com/forum/blogs

GeistInteractive – http://www.geistinteractive.com/blog

HomeBase Software – http://hbase.net

MightyData – http://www.mightydata.com/blog

New York FileMaker Pro Developers’ Group – http://nyfmp.org/blog

RobFM – http://www.sumware.net/robfm3

SavvyData – http://www.savvydata.com/blog

SeedCode – http://seedcodenext.wordpress.com

Six Fried Rice – http://sixfriedrice.com/wp/blog

Soliant Consulting – http://www.soliantconsulting.com/blog

Continue Reading →

Fixed Width for EDI and Other Reporting

Posted on August 29, 2012 by

Editor’s note: Today it’s my pleasure to present a guest article written by Beverly Voth. Like many other developers, I have enjoyed and benefitted from her ongoing contributions to the FileMaker community.

I do a lot of text manipulation for EDI (Electronic data interchange – http://en.wikipedia.org/wiki/Electronic_data_interchange) and plain text exports with fixed-width field data. Some varieties of EDI use XML, but this article is about plain text. EDI may or may not use the fixed-width format. Fixed-width reports may or may not use delimiters and various “padding” characters.

I created two FileMaker custom functions to help me calculate fixed-width and EDI text for export, and if you wish, you can follow along in today’s demo file, Fixed Width EDI.

Continue Reading →

Magic Value Lists

Posted on July 25, 2012 by

Folks, we have a superb demo today (MagicValueList), which comes courtesy of Andries Heylen of BH&A…

But first a bit of background. Prior to July 18, 2012, if anyone had told me you could base a value list on an unstored field, my response would have been something along the lines of…

  • What app are you using? (Because it sure as heck ain’t FileMaker.)
  • Why are you wasting my time with this nonsense?
  • Is today April Fool’s Day?
  • What are you smoking?

But then John Ahn showed this amazing Conditional Value List demo during the DevCon “Unconference” session devoted to ExecuteSQL (see previous posting), and to my way of thinking, the most intriguing part of session was only incidentally concerned with SQL, because John seemingly had achieved the impossible — a value list based on an unstored field.

Continue Reading →

Wim Decorte’s FM 12 Trigger Reference

Posted on July 15, 2012 by

The other day I had a question about some nuance involving script triggers. The details of that particular question aren’t important, but I recalled that when script triggers were first introduced, Wim Decorte had produced a detailed, color-coded reference.

I wondered if he had updated it for FM 12, and it turns out that he has, and with his permission I am sharing it here. There are actually two files in today’s download (Wim Decorte FM 12 Trigger Reference): the four page PDF reference, and a FM 12 file demonstrating the firing order of the various triggers.

There is a wealth of information to be gleaned from these two files, and I encourage you to download them and check them out. And of course if you find any mistakes or have any trigger-related observations to share, I hope you’ll post a comment here.

Note: even if you are sticking with FM 10 or FM 11 for the time being, if you use script triggers, the PDF in today’s download contains much that you will find relevant (hence my categorization of this post as “Version: FM 10 or later”).

A Simple Backup Script, revisited

Posted on June 25, 2012 by

In October 2011 I posted a simple backup script that has made my development life easier, but of course that version was for .fp7 files only (the release of FM 12 being six months in the future), and when I began converting files to FileMaker 12, I realized that the backup script was not intelligently rolling with the changes. So recently I modified the script to accommodate both .fp7 and .fmp12 files, and also to be more flexible with regards to separators within the backup file name, e.g.,

Rather than repeatedly referring the reader back to the earlier article, I have “deprecated” the prior one and reproduced it here with appropriate changes, and have also updated the demo file (see below). Continue Reading →

Dwindling Value Lists, part 2

Posted on June 10, 2012 by

One thing I love about this blog is how much I learn from your comments. Having said at the end of part 1 that I couldn’t think of any serious use for a DVL on a check box set, Michael Rocharde kindly provided this example file (Conditionals), which I am sharing with his permission.

This is one of many example files and techniques available in his new interactive e-book, FileMaker and Me, available for iPad via the iTunes store.

Dwindling Value Lists, part 1

Posted on June 10, 2012 by

A few months ago I mentioned “dwindling value lists” in passing, and said I would do a proper article on them at some point in the future. Well, then FM 12 was released and I went on an ExecuteSQL binge, so I’m just now getting around to honoring my promise.

Dwindling Value Lists (DVLs) are value lists that shrink up, by removing individual list items as they are selected. They can come in very handy when you need to schedule resources and want to prevent double booking. This example comes from one of today’s demo files, Dwindling Value Lists, and shows how a DVL might be used to schedule employees for a work shift.

Essentially, a DVL is a filtered value list that updates in real time (or something very close to real time), and this is done by building a multiline key of selected values, and then filtering the VL to only show remaining eligible values. Continue Reading →

FM 12 ExecuteSQL: Robust Coding, part 2

Posted on May 21, 2012 by

In part 1, I listed six scenarios that could potentially cause ExecuteSQL code to break:

…and managed to get through the first five before running out of steam. This time, we’re going to examine #6 on the list, and then look at a few miscellaneous odds ’n’ ends, and today’s demo file is ExecuteSQL Sandbox, v2, if you’d like to follow along.

Changing a Field Type

Why would changing a field’s type cause a problem? Well, first off, as I mentioned last month, if you are using that particular field as a predicate in a JOIN, the query will break, and your reward will be the the dreaded “?”.

I don’t have a fix for this, just some advice: Don’t change a field’s type if you’ve used it as a JOIN predicate in a working SQL statement. (The only reason I’ve needed to change a field type recently was to fix a broken JOIN where the field types were mismatched.)

Continue Reading →

FM 12 ExecuteSQL: Robust Coding, part 1

Posted on May 13, 2012 by

[Note: Some of the material in this article, and in today's demo file ExecuteSQL Sandbox, previously appeared in my March 2011 article, Custom Functions for Internal SQL.]

The other day a colleague remarked, “You know, it’s going to be interesting to see if you SQL guys are still so excited about ExecuteSQL when something gets renamed and your code breaks.” He had a good point: code is “brittle” if it works initially, but then subsequently breaks as a result of a seemingly innocuous action.

Here are some things that can cause ExecuteSQL code to break:

  1. Renaming a table occurrence (TO)
  2. Renaming a field
  3. Using a “reserved word” as a field or TO name
  4. Having a problematic character in a field or TO name
  5. Executing the code in a foreign country (!)
  6. Changing a field’s type (e.g., from text to number or vice versa)

…and today we’re going to look at some defensive measures we can employ to prevent problems when these things occur.

Using the “orders” table in the demo file…

Continue Reading →

FM 12 ExecuteSQL: Dynamic Parameters, pt 2

Posted on May 8, 2012 by

This is a quick follow-up to part 1, with a couple more observations about dynamic parameters.

Embedded Apostrophes

Here’s one I can’t believe I forgot to mention the other day:  A major ExecuteSQL headache that dynamic parameters can alleviate is the dreaded “embedded apostrophe” problem. In case you aren’t familiar with it, if your text string contains an embedded apostrophe, in standard SQL you must escape it by prepending another apostrophe, for example compare these two “standard” (non-dynamic) queries:

As you might expect, you don’t have to worry about this if you instead use a dynamic parameter… just quote the search term the way you would any FileMaker text string (i.e., in double quotes) and go about your business.

Continue Reading →