Category: General

General, Level: Any

Resolved: My Very Own Twitter Impersonator

Kevin Frank

UPDATE, 7 JAN 2013: THANK YOU FRIENDS AND COLLEAGUES EVERYWHERE. THIS PROBLEM HAS BEEN RESOLVED THANKS TO YOU. I AM NOW @FILEMAKERHACKS ON TWITTER. I CANNOT BEGIN TO EXPRESS MY GRATITUDE AND APPRECIATION.

Dear Friends, Colleagues and Fellow FileMaker Enthusiasts,

You know the old saying that imitation is the sincerest form of flattery? Well, I’m here to tell you that having experienced it first hand, I’m not feeling even remotely flattered. Pissed off? Yes. Violated? Yes. But not flattered, and not amused. Here’s the short version:

Me:

1-6-2013 3-36-38 PM

The Impostor:

1-6-2013 3-38-08 PM

Here’s a longer version: Last year a colleague wrote to ask why I wasn’t using my logo on my Twitter postings. Me: “I don’t do Twitter.” Him: “Sure you do, I’m one of your followers!”

Continue reading “Resolved: My Very Own Twitter Impersonator” →

Beverly Voth, General, Level: Intermediate, Macintosh, Version: FM 8 or later, Windows

Fixed Width for EDI and Other Reporting

beverlyvoth

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 “Fixed Width for EDI and Other Reporting” →

General

Combinations and Pascal’s Triangle, part 2

Kevin Frank

Yesterday we looked at counting all possible combinations of four modifier keys: Alt, Control, Shift and Window (abbreviated A,C,S,W). Today, we’re going to look at a related problem: how many different ways can we combine two of those four keys?

Of course, when your total “set size” is only four, it’s not that hard to identify them (AC, AS, AW, CS, CW, & SW) and then count them (6). But why go to all that trouble when a modified version of Pascal’s Triangle can not only do the work for you, but answer the question for any set size, and any number of choices within that set.

Let’s start with the original and slice off the sections in red:

03-corrected

This gives us a simplified triangle, which we can use to answer the question I posed above: Continue reading “Combinations and Pascal’s Triangle, part 2” →

General, Level: Intermediate, SQL, Version: FM 8 or later

FileMaker’s Internal SQL Engine, part 2

Kevin Frank

When my kids were little, I sometimes found myself echoing that familiar parental refrain: “Just because you can do something, doesn’t mean you should.” And I think a related question can be asked with regard to FileMaker’s internal SQL engine: Given FileMaker’s almost infinite flexibility, why bother with SQL?

Three reasons come immediately to mind: 1) efficiency; 2) power; 3) SQL is the most widely used database language, and worth becoming familiar with. Number 3 is self-explanatory, and I’ll address #2 below, but what do I mean by #1? How is SQL more efficient? In a nutshell, it allows you manipulate data via text commands without having to “establish context” (by going to a particular layout or record), and also without having to add table occurrences or relationships to your Relationships Graph.

Let’s say I have a scheduling solution that ships with demo data. I want to ensure that all dates in the system are current, so that when the user looks at the calendar, they will see appointments for the current month, and dates for invoices, payments, purchases, etc., are contemporary as well. Now there’s no reason this can’t be handled the “traditional way”, but what a load of drudgery.

Go to layout
Show all records
Replace field contents of date field
(repeat as necessary for multiple date fields in a given table)
Go to another layout
Repeat ad nauseam…

You could easily end up producing a mountain of script steps to accomplish what could have instead been done with a single Set Variable script step:

…which contains multiple SQL calls.

Note: I am using the doSQL plug-in in these examples, but the code inside the parentheses would be the same, regardless of which SQL plug-in I chose to use.

A similar situation arises if we want to delete all test data prior to shipping a new version of our product. Sure, we could navigate to umpteen layouts, repeatedly issuing Delete All Records commands, but doesn’t this seem more elegant?

Incidentally, for a couple of the tables, rather than deleting all the records, I delete only the ones that meet the condition imposed by the WHERE clause. And of course the WHERE clause could be much more complex than the one I’m using here.

The two examples we’ve looked at demonstrate the economy of using internal SQL to accomplish tasks that would not pose particular challenges for the average FileMaker developer. But so far we haven’t waved a magic SQL wand to solve a difficult problem. I said in the second paragraph that “power” is one of the reasons to use internal SQL, so let’s look at a problem that is difficult to solve using traditional FileMaker methods. Not impossible, just difficult.

Consider a database with two tables, Donors and Donations, related in the usual manner. We would like to see who is donating during a given date range, how many times and how much.

Rather than using a standard summary report, we want to show the equivalent information inside a portal. So, we need an interface that will allow the user to enter a date range, and then see each unique donor, and the count and total dollar amout of donations made by the donor during the date range, like so:

As I say, it’s possible to do this using “pure” FileMaker, in fact I built this demo when FileMaker 7 was first released in 2004, but it took a fair amount of trial and error to get it working properly.

With internal SQL, on the other hand, this can be knocked out in a few easy steps.

1. Build a multiline key of donor ids for the specified date range.

2. Relate this key to the primary key in the Donors table, and base the portal on that relationship.

3. Define a calculated field in Donors to count the related donations for the specified date range.

4. Define another calculated field in Donors, to sum the related donation amounts for the specified date range.

That’s all there is to it, and if you don’t want to take the time to build it from scratch, you can download this demo: sql-summary-report-in-portal (requires either doSQL, or the SQL plug-in of your choice, but if you don’t use doSQL then obviously you’ll need to modify the plug-in calls accordingly).

Finally, the DateToSQL custom function in the above code samples, will be discussed in my next posting (and the Q custom function was explained here).

General, Level: Beginner, Version: FM 8 or later

The Wondrous Bullet Character

Kevin Frank

Do you know how to create a bullet (•) character via the keyboard? It’s easy on the Mac: just press Option 8. It’s a bit more complicated in the Windows world, but once your fingers get the hang of it, you’ll be able to do it without much thought at all.

  1. Hold down the Alt key
  2. Using the number pad, type 0149
  3. Release the Alt key

Step 2 must be performed on the number pad; it won’t work if you use the numbers on the main portion of the keyboard. And if this seems unduly complicated, the upside is that you can create any character this way as long as you know its corresponding ASCII value.

Continue reading “The Wondrous Bullet Character” →

General, Level: Intermediate, Version: FM 8 or later

The Last Day of the Month, part 3

Kevin Frank

Grizzled FileMaker veterans are fond of saying things like, “You ought to know at least three different ways to accomplish any given task.” With that in mind, I hereby submit a third method for calculating the last day of a given month.

Let ( [
theDate = Get ( CurrentDate ) ;
monthNum = Month ( theDate ) ;
yearNum = Year ( theDate ) ;
febLastDay = 28 +
Case (
Mod ( yearNum ; 400 ) = 0 ; 1 ;
Mod ( yearNum ; 100 ) = 0 ; 0 ;
Mod ( yearNum ; 4 ) = 0 ; 1 ;
0
) ;
dayNum = Choose ( monthNum ; "" ; 31 ; febLastDay ; 31 ;
30 ; 31 ; 30 ; 31 ; 31 ; 30 ; 31 ; 30 ; 31
)
] ;

Date ( monthNum ; dayNum ; yearNum )

) // end let

If you read yesterday’s post, you may have noted a resemblance between this calculation and its predecessor, at least as far as the “Let” portion goes. The main difference is the addition of a dayNum variable populated via the Choose() function.

In case you’re not comfortable with Choose(), its format is

Choose (
test ; result if test = 0 { ; result if test = 1 ; result if test = 2... }
)

…where “test” is any non-negative whole number, and the results in braces are optional. At first this function may seem confusing but it turns out to be a very compact replacement for the Case() function, under a strictly defined set of circumstances.

Say, for example, in a table called “test”, you have a field called “score”, which can contain any integer between 0 and 9, and you want convert that value to its corresponding name (“zero,” “one,” “two”, etc.). You could certainly accomplish this with Case() and the statement might look like this:

Case (
test::score = 0 ; "zero" ;
test::score = 1 ; "one" ;
test::score = 2 ; "two" ;
test::score = 3 ; "three" ;
test::score = 4 ; "four" ;
test::score = 5 ; "five" ;
test::score = 6 ; "six" ;
test::score = 7 ; "seven" ;
test::score = 8 ; "eight" ;
test::score = 9 ; "nine"
)

The exact same result can be obtained far more economically thus:

Choose ( test::score ;
"zero" ; "one" ; "two" ; "three" ; "four" ;
"five" ; "six" ; "seven" ; "eight" ; "nine"
)

Essentially, Choose uses test::score as a pointer to the correct “result”, via what’s known as a zero-based index, so a test::score value of 0 corresponds to the first result, a test::score of 1 corresponds to the second result, etc.

In the case of our Last Day of the Month problem, there is no month number of 0, only 1 through 12, so our first result is "" to accommodate the non-existent zero result.

General, Level: Beginner, Version: FM 8 or later

The Last Day of the Month, part 2

Kevin Frank

Yesterday we explored a method to calculate the last day of the month, and by way of introduction I said,

You might be tempted to use a Case() statement, and test each month individually, but then you’d have to engage in some calculation gymnastics to accommodate Feb 29th in leap years.

This time around let’s look at that method, and those alleged “gymnastics”. They turn out to not be terribly convoluted; in fact, the most time-consuming part of this could be simply defining the rules for what constitutes a leap year.

But first let’s pretend there’s no such thing as a leap year or a leap day. In that case we could write our calculation as follows:

   Let ( [
      theDate = Get ( CurrentDate ) ;
      monthName = MonthName ( theDate ) ;
      yearNum = Year ( theDate )
   ] ;
   Case (
      monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
      monthName = "February" ; Date ( 2 ; 28 ; yearNum ) ;
      <etc.>

Okay, that was a pleasant interlude, but now back to reality. Superficially, it appears that all years evenly divisible by 4 are leap years, but it’s a bit more complicated than that. A year is a leap year when:

  1. it is evenly divisible by 400
    or
  2. it is evenly divisible by 4 but not evenly divisible by 100

It’s important to know all the rules, because we don’t want our database breaking on February 29th in the year 2400, do we?

So how do we determine “even divisibility”? We do so by using the Mod() function, which returns the remainder when you divide one number by another. If the result of the Mod() operation is zero, then we know the first number is evenly divisible by the second.

For example, Mod ( 2000 ; 400 ) = 0, which passes test #1 above, and tells us that the year 2000 was a leap year. The year 1900 cannot pass either of the above tests, which tells us it was not a leap year. Bearing in mind that FileMaker uses calculation “short-circuiting” (stops calculating as soon as one of its logical tests evaluate as true), here’s a calc that returns a 1 for leap years and otherwise returns a zero:

   Let ( yearNum = Year ( Get ( CurrentDate ) ) ;
      Case (
         Mod ( yearNum ; 400 ) = 0 ; 1 ;
         Mod ( yearNum ; 100 ) = 0 ; 0 ;
         Mod ( yearNum ; 4 ) = 0 ; 1 ;
         0
      )
   )   //   end let

Okay, now that we’ve gotten to the heart of the matter, we can put it all together and write our calculation.

   Let ( [
      theDate = Get ( CurrentDate ) ;
      monthName = MonthName ( theDate ) ;
      yearNum = Year ( theDate ) ;
      possibleLeapDay =
         Case (
            Mod ( yearNum ; 400 ) = 0 ; 1 ;
            Mod ( yearNum ; 100 ) = 0 ; 0 ;
            Mod ( yearNum ; 4 ) = 0 ; 1 ;
            0
         )
   ] ;
   Case (
      monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
      monthName = "February" ; Date ( 2 ; 28 + possibleLeapDay ; yearNum ) ;
      monthName = "March" ; Date ( 3 ; 31 ; yearNum ) ;
      monthName = "April" ; Date ( 4 ; 30 ; yearNum ) ;
      monthName = "May" ; Date ( 5 ; 31 ; yearNum ) ;
      monthName = "June" ; Date ( 6 ; 30 ; yearNum ) ;
      monthName = "July" ; Date ( 7 ; 31 ; yearNum ) ;
      monthName = "August" ; Date ( 8 ; 31 ; yearNum ) ;
      monthName = "September" ; Date ( 9 ; 30 ; yearNum ) ;
      monthName = "October" ; Date ( 10 ; 31 ; yearNum ) ;
      monthName = "November" ; Date ( 11 ; 30 ; yearNum ) ;
      monthName = "December" ; Date ( 12 ; 31 ; yearNum )
   )  
   )   //   end let

One of the nice things about yesterday’s elegant little calculation was that we didn’t have to think about leap year at all. We let the FileMaker calculation engine worry about that for us. But there’s something very different I like about today’s calculation… it’s supremely readable. It’s not elegant, and it’s not succinct, but it gets the job done. And that’s one of my favorite things about FileMaker: there is rarely only one way to solve a problem.