Level: Advanced, Version: FM 13 or later

CustomList

Earlier this year, in Virtual List Reporting, I used a custom function written by Agnès Barouh called CustomList to generate a couple basic lists, and then remarked…

2016-09-19_000003

Well today I’m going to attempt to make good on that assertion. In case you aren’t already familiar with CustomList, it allows you to iteratively generate and/or parse lists using the full power of the FileMaker calculation engine. CustomList can process up to 500,000 rows — it is non-recursive and very fast — and today we’re going to use it to solve a variety of problems, both real world and theoretical. [Actually, the row limit can be adjusted, and it varies by platform — for full details see the CF definition.]

Today’s article features four demos…

…as well as a number of examples that don’t require a demo to illustrate their point. Continue reading “CustomList”

Level: Advanced, Version: FM 13 or later, Virtual List

Virtual List Reporting, part 2

Welcome back for the second installment in our exploration of Virtual List Reporting (a.k.a. VLR). Demo file: VLR-part-2.zip

2016-06-21_045615

To avoid repetition, this article will assume the reader is familiar with concepts and techniques introduced in part 1 (some of what follows may appear to be gibberish if the reader is not)… but to briefly recap, here are some benefits of VLR:

  • Flexible framework accommodates complex reporting challenges
  • Fast performance (we use the Multifind technique in this demo)
  • No need to tamper with schema in your data tables or on the relationships graph
  • Unlike traditional FM reports, you can easily combine data from unrelated tables (we saw this in report 6 in part 1)
  • Under certain circumstances, VLRs can be much faster to develop than traditional FM reports (as per discussion of report 3 in part 1)
    Continue reading “Virtual List Reporting, part 2”
Level: Advanced, Version: FM 13 or later, Virtual List

Virtual List Reporting, part 1

Self-plagiarism alert: to avoid repeatedly referring the reader back to earlier virtual list articles, portions of text from those earlier articles are incorporated here.

Welcome to the first installment of a multi-part series on producing reports using the virtual list technique, or more properly, collection of techniques. Demo file: VLR-part-1.zip

demo

Invented and popularized by Bruce Robertson, virtual lists are incredibly flexible, and have made a number of appearances here in the past, including… Continue reading “Virtual List Reporting, part 1”

ExecuteSQL, Level: Intermediate, Version: FM 13 or later

Fast Summaries Revisited

Update 30 May 2018: see also Fast Summaries Re-revisited.

If you do complex data analysis, then from time to time you probably need to group, summarize, and parse data into variables and/or fields. There are various ways to accomplish this, including the Fast Summary technique, which regular readers of this blog may be familiar with, as it has made a number of appearances here over the years.

Well today we’re going to look at a couple alternatives to Fast Summaries, with the help of some demo files, which you can use to do your own benchmarking.

2016-03-29_223920

Or, if you’d rather not do your own benchmarking, scroll down to see results from my tests. Bottom line: each of these three techniques can be fastest, depending on circumstances.

(Also, if you’ve been putting off exploring Perform Script On Server, a.k.a. PSOS, host the demo files on FileMaker Server 13 or later. The reporting routine optionally uses PSOS, so you can dive in painlessly and see what you’ve been missing.) Continue reading “Fast Summaries Revisited”

ExecuteSQL, Level: Any, SQL, Version: FM 13 or later

Tips ’n’ Tricks, part 1

Over the last few years I’ve been collecting miscellaneous tips and tricks with the thought of eventually aggregating them into a blog posting, and it appears that day has finally come. Ideally there will be something new and useful here for developers of all levels, from the neophyte to the most grizzled of FileMaker veterans.

We start off with a couple FM 14-specific tips.

Tip #1: Tool tips in layout mode

In layout mode in FileMaker 14 you can hover your mouse over any badge and an informative tool tip will display.

2015-11-30_13-32-06

Tip #2: Shift-Return in the Script Workspace

Did you know you can add new line above the current line in the Script Workspace by pressing Shift-Return? It works with the standard Return key, as well as the numeric Enter key.

2015-11-30_14-08-17
Before… line 1 is active, and the user is about to press Shift-Return
After... a new top line has been added
After… a new top line has been added, and existing entries have moved down to accommodate

Continue reading “Tips ’n’ Tricks, part 1”

Level: Intermediate, Version: FM 13 or later

Multi-State Dynamic Graphic Buttons

I had a client request recently to toggle an image button to a second image on mouse over. Under normal circumstances, this would not be difficult to accomplish since you can assign one image to the Normal state, and a different image to the Hover state, like so.

2015-10-16_9-32-26

However, if you go this route, you can’t use container fields as the source for the images, but instead must make static file selections, like so:

2015-10-25_15-08-03

Since this button would be appearing on dozens of layouts throughout a 20+ file solution, the client wanted to be able to store the two images in container fields, so an authorized user could easily swap in new container images (in browse mode, not layout mode) and have the changes ripple through their entire solution.

2015-10-25_15-23-05

Demo Files:

Continue reading “Multi-State Dynamic Graphic Buttons”

ExecuteSQL, Level: Intermediate, SQL, Summary List, Version: FM 13 or later

Found Set Awareness Revisited

Recently a colleague contacted me with some questions about making ExecuteSQL found-set aware, and I realized that while I had addressed aspects of the problem in various blog postings over a multiyear period (for example, here and here), I had not assembled my thoughts into one cohesive, up-to-date article.

Well, that shortcoming has now been addressed, and today we’re going to look at three demo files, all of which rely on the summary list field type (introduced in FM 13).

9-20-2015 8-52-01 AM Continue reading “Found Set Awareness Revisited”

Beverly Voth, Level: Intermediate, Version: FM 13 or later

FileMaker UI Tricks: Dual-Purpose Layout

Editor’s note: Today it’s my honor and privilege to present an article by guest author Beverly Voth, whose contributions to the FileMaker community are numerous and much appreciated. It’s great to have her back here on FileMaker Hacks.

FileMaker 13 & 14 have some features that can be leveraged to allow one layout to toggle between Form View & Table View and look differently! We will be using the Starter Solution, “Contacts”. Feel free to download the demo file (Contacts14.fmp12.zip) or you may start with a fresh copy of the database file or another Starter Solution and follow the steps below.

NEW to Starter Solutions? Select “New From Starter Solution” under the File menu. Click the “Contacts” icon and click on the “Choose” button. A dialog will come up with the “Save As” field filled in with the name of the database file you chose. Navigate to where you wish to save the file and click the “Save” button. This will open the file to the default layout and be ready to change.

The default layout for Contacts is “Contact Details”, is in Form View and shows two “tab-like” buttons called: Contact List & Contact Details. These are buttons that Switch you to different layouts. Notice how the placement is the same and there is a smooth transition between the two layouts. You may also notice that the view is “locked” to just allow View as List or View as Form (respectively). You cannot select other views on these two layouts.

CONTACTS_details_tab

CONTACTS_list_tab

Continue reading “FileMaker UI Tricks: Dual-Purpose Layout”

Level: Intermediate, Version: FM 13 or later

Global Multiline Key As Relational Predicate

This is a quick follow up to my previous article on using a Summary List field as a relational predicate, and transferring a found set from table A to the corresponding found set in table B across that relationship via the Go To Related Record (current record) script step.

To avoid needless repetition, I will assume the reader has read that article. As discussed in the comments section (thank you Mardi Kennedy and Jerry Salem), there is a tried and true method for going to a found set of related records that has worked since FileMaker Pro 3 was released in December 1995:

  1. Define a global text field
  2. Link this field relationally to the primary key field of another table
  3. Populate the global text field with a list of keys
  4. Go to related records (a.k.a. GTRR)

2-17-2015 9-30-13 AM Continue reading “Global Multiline Key As Relational Predicate”

Level: Intermediate, Version: FM 13 or later

Summary List As Relational Predicate, part 2

Disclaimer: This article features an undocumented technique. As with all material on this site, use at your own risk and test thoroughly.

8 Feb 2015: Demo updated to include a “refresh window / flush cached join results” step before GTRRing across a summary list relationship

Last week we looked at a technique to allow a summary field to be used as a relational predicate, directly, i.e., without the need to use a “helper field” as a predicate. If you’re not comfortable employing an undocumented hack in your solutions, I encourage you to read on anyway, because what you’re about to see may still be worth doing, whether you use last week’s method, or the safer (but a bit slower) “helper field” approach as delineated here on FileMaker Hacks last year in Summary List Fields in FM 13, part 1.

We’ve established that a summary list field can be used as a relational predicate (either directly or indirectly); the aim of today’s article and demo file, FM-13-Summary-List-As-Predicate, is to offer a compelling reason to do so. Continue reading “Summary List As Relational Predicate, part 2”