Archive | Level: Intermediate RSS feed for this section

Outer Joins in FileMaker 12, part 3

Posted on April 17, 2013 by

I’ve said before, and no doubt will say again, that one of my favorite things about this blog is how much I learn from your feedback and the demo files you send me.

Recently I received a file from Otmar Kramnis of the Hochschule Luzern demonstrating the fastest SQL-based method I have yet seen to solve the challenge we looked at in part 1 and in part 2, and with a few minor modifications, this is the demo we’re going to focus on today:  Outer Join Demo 7

As you may recall, the aim is to show a week’s worth of daily sales totals for all employees whether they had any sales or not.

4-17-2013 8-39-48 AM

Or, to restate the problem in more generic terms: we need to show all values from table A, whether or not there are any matching values in table B. This is known as an “outer join”, or more precisely a “left outer join”, since we want to see all values in the “left” table (Employees), whether or not they have corresponding matches in the “right” table (Sales).

Continue Reading →

Copying and Pasting Layout Schema

Posted on February 23, 2013 by

Has this ever happened to you? You have a number of identically named fields in two different tables…

2-23-2013 5-48-43 PM

…but when you copy one of those fields from a layout based on the first table…

2-23-2013 5-42-27 PM

… and paste it onto a layout based on the second one…

2-23-2013 5-45-14 PM

…the result is not what you might have wished for.

Continue Reading →

Fun with Factorials

Posted on February 15, 2013 by

g01A while back my youngest son, who is an avid Go player, asked me, “Is it true there are more possible Go games than there are atoms in the universe?”

go2“Absolutely,” I replied, “Let’s fire up FileMaker Pro and prove it.” (I wasn’t about to let a rare teachable moment slip by.) “If memory serves, the number of atoms in the universe is estimated be roughly 10^80. Since a Go board is 19 by 19, in theory there are 361 possible first moves (19^2), followed by 360 possible second moves for each of those 361 first moves, followed by 359 possible third moves for each of the 360 possible second moves, and so on. Therefore the total number of moves can be calculated as 361 x 360 x 359… x 1, or, more simply, 361! (i.e., 361 factorial).” Continue Reading →

Graph Management + Conditional Icons

Posted on January 28, 2013 by

Recently I received a dual-technique FileMaker 12 demo from Eden Morris. Here’s what he had to say about technique #1:

In the Relationships Graph I show the use of colored labels to indicate where record creation, cascade deletes, and sorted relationships are enabled. Looking at the graph it easily shows that I can create customers from a company table but that they don’t cascade delete, you can create invoices and invoice lines from a project and the invoices and their lines cascade delete, and that the invoice lines are sorted.

1-27-2013 11-50-00 PM

It’s an elegant way to display this info (but for some reason, it makes me crave jelly beans).

Continue Reading →

Privilege Set Reporting

Posted on January 12, 2013 by

Editor’s note: Today I’m pleased to present a guest article by Michael Rocharde, author of FileMaker & Me, an interactive multimedia book focusing on FileMaker Interface Design.

FileMaker has always been an incredible tool for generating reports and for many years, I used to have a report screen with banks of buttons to generate all of the individual reports. In the last few years however, I’ve added a global field, to the footer of each module data entry screen, with a pop-up menu, listing all of the different report options for the module in question. To that global field, I’ve added an onObjectModify script trigger which sets the name of the report as the parameter that generates the report they want. Simple, very effective and it has the added advantage of giving the user all the reports that are relevant to where they are at the time.

Recently I had a project where some of the reports that were required were restricted to the different privilege sets and I was looking for a quick (and easy) way to accomplish this. I started thinking about whether I could use a related value list to solve the problem and came up with, what I think is, an elegant and very simple solution.

The first thing I did was create a Reports table with just two fields, Group (for the privilege set) and Report (for the name of the report itself).

Reports_table

Continue Reading →

Thinking About Value Lists, part 2

Posted on November 7, 2012 by

In part 1 we explored a particular type of value list: the field-based two-column variety, based on all values, and set to show values from the second column only…

…and today’s article assumes familiarity with that material. This time around we’re going to look at some challenges and issues that can arise when using filtered (a.k.a. “conditional”) field-based value lists, and propose some solutions to those challenges.

I don’t claim that any of the following is particularly original or brilliant, but I do a fair amount of team programming, and these issues come up over and over again.

Continue Reading →

PDF version of FM 12 ExecuteSQL Reference

Posted on November 2, 2012 by

Beverly Voth has produced a PDF version of The Missing FM 12 ExecuteSQL Reference with some additional material not available in her original posting from October 19th.

And the SQL4_fmdev2.fmp12 demo file has been updated with new queries.

Thank you Beverly for this major contribution to the FileMaker community.

Outer Joins in FileMaker 12, part 2

Posted on October 12, 2012 by

Last week in part 1 we looked at four “outer join” reporting approaches. Two of them involved ExecuteSQL, and I ended that section with the plea: Of course it’s possible that you, dear reader, know some FileMaker SQL voodoo to speed things up, and would be willing to share? Well Dr. Osamu Noda of Japan was kind enough to not only respond, but has provided a pair of demos (Outer Join Demo 5 and Outer Join Demo 6) which are significantly faster and which I am sharing with his permission.

Both of the demos are based on my original files from last week, and as you may recall, the aim was to show a week’s worth of sales for all employees whether they had any sales or not.

Continue Reading →

Outer Joins in FileMaker 12, part 1

Posted on October 2, 2012 by

Recently I had an on-screen reporting challenge, and decided to try several different approaches to see which would be fastest. The challenge: Starting with two tables, one containing 20 Employees, and one containing 2,000 Sales records for the current year…

…display daily sales totals per employee in a seven-day grid, like so:

Continue Reading →

Aggregates (Summary Fields) in Filtered Portals

Posted on September 13, 2012 by

The question on one or more of the FileMaker forums was asked and answered. Q: How do you get the Count() of the related records in a filtered portal? A: (paraphrased) duplicate the filtered portal and make it one row in height. Place the related summary field, “Count of…”, in the single row filtered portal. Voila! your count is now filter-specific. (The instructions for creating the filter for a portal and summary field are below.)

This recent topic led me to consider what other aggregate fields could be used with the filtered portal. And what about that Go To Related Record script step? Does it only show the related FILTERED records or all related records? The demo (bvoth_aggregates_in_portals.fmp12) and article have been created to answer these questions.

I started using filtered portals in cross-tab reports shortly after they were introduced. These are generally ways to show something very specific in each portal with sorts and filters and usually one row only. I had not explored the use of aggregates in these cross-tab reports until now.

Kudos to those before me that may have discovered these answers and tricks, too.

Continue Reading →