Tag: Separation Model

ExecuteSQL, Level: Intermediate, SQL, Version: FM 14 or later

FM 14: Separation Model Data Mining

Kevin Frank

Note: Interface file #1 requires FM 14 or later; interface file #2 works with FM 12 or later.

What do you get when you combine the Separation Model + FM 14 placeholder text + ExecuteSQL + a million-record table + a variable array + Get(CalculationRepetitionNumber) + the Mod and Ceiling functions + a couple custom functions, with blazingly fast (local, LAN and WAN) performance thrown in for good measure?

This article w/ accompanying demo (fm-14-separated-data-mining.zip), that’s what.

7-7-2015 12-33-33 PM

The Challenge

Provide a data-mining interface to query a million-record table (cc_transactions) containing 20 years’ worth of credit card transactions. The client wants to be able to pick a date via a calendar widget, and see transaction info for that date, summarized by card type, transaction type and region.

7-6-2015 8-54-58 AM

This is a separated solution, with cc_transactions living in a file called z_data.fmp12, and a specific requirement for this project is to not make any schema changes to the data file. The data-mining will take place in a separate interface file… or in this case, two interface files, since we’re going to look at two methods.  Continue reading “FM 14: Separation Model Data Mining” →

Level: Intermediate, Version: FM 14 or later

FM 14: Separation Aggregation Aggravation revisited

Kevin Frank

Four years ago I posted the original version of this article, exploring a technique that worked fine in FM 11, but that broke and required extensive work arounds in FM 12 and 13. What you’re about to read is a revision of the original article, showcasing a FM 14 method made possible by a new feature: placeholder text.

Introduction

FileMaker developers take it for granted that calculated sub-total and total fields will update automatically when portal rows are created, edited or deleted. This becomes problematic when one a) uses the separation model, and b) either intentionally or unintentionally holds the parent and related child records open (uncommitted) while editing, but still expects to see these aggregates update in real time.

7-3-2013 2-57-02 PMToday’s demo file is FM 14 Aggregates and Separation, and for demonstration purposes it has interface elements in both the Data file and the Interface file. Normally of course, in a separated solution, you wouldn’t have interface elements in the Data file (since that would defeat the purpose of separation). Here we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three calc fields below the portal are tracking the changes in real time. Continue reading “FM 14: Separation Aggregation Aggravation revisited” →

Level: Advanced, Version: FM 12 or later

Radical Separation, part 4

Kevin Frank

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

FYI: In March at the Portland PauseOnError un-conference, Matt Navarre and I had a freewheeling Separation Model discussion, a podcast of which has just been posted as episode 85 at FileMaker Talk.

Welcome to the fourth and final installment in our series on Radical Separation. Today’s article assumes familiarity with part 1, part 2 & part 3, and continues in the direction we were headed at the end of part 3. Specifically, we will finish up our exploration of “virtual calculations” by examing an intriguing approach suggested by Barry Isakson to a) reduce the field count, b) solve the “define more fields than you’ll ever need” problem, and c) accommodate summary fields, and I invite you to follow along in today’s demo file, Virtual Calculations, Part 4, if you are so inclined.

6-9-2013 4-10-10 PM

Continue reading “Radical Separation, part 4” →

Level: Advanced, Version: FM 12 or later

Radical Separation, part 3

Kevin Frank

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

Today we’re going to delve further into the concept of virtual calculations, picking up where we left off last time, and with the assumption that readers are familiar with the material in part 1 and part 2 . We’ll look at some ways to make this technique less brittle (prone to breakage if objects are renamed), and also less opaque to DDR analysis tools such as BaseElements and Inspector. We’ll also see if the technique can be applied to auto-enter calc fields, and finally, we’ll explore some ways to make the technique easier to implement.

5-10-2013 10-41-51 AM

Demo Files:

Continue reading “Radical Separation, part 3” →

Level: Advanced, Version: FM 12 or later

Radical Separation, part 2

Kevin Frank

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

In part 1 of this series, we defined radical separation as a separation model scenario in which the developer no longer has access to a data file once a solution had been deployed. Updates to the solution are delivered in the standard separation model manner: by swapping in a new interface file.

We explored the concept of “virtual calculations”, where certain (unstored) calculated fields in a data file derive their definitions from syntax stored as data in a special table in the interface file. The advantage of this being that calculation logic can be redefined programatically by the simple expedient of replacing the interface file.

5-2-2013 9-09 PM

In the six weeks that have gone by since I posted part 1, I have made a couple improvements to the technique, one of which which we’ll examine in today’s demo file: Virtual Calcs, Part 2

Continue reading “Radical Separation, part 2” →

ExecuteSQL, Level: Advanced, SQL, Version: FM 12 or later

Radical Separation, part 1

Kevin Frank

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

Earlier this month I had the honor and the privilege to do a presentation on the topic of Radical Separation at the PauseOnError un-conference in Portland, Oregon, which included a demo file resembling this one: virtual-calcs-part-1-v2

3-20-2013 2-54-43 PM

Before the conference I posted a pseudo-F.A.Q. which included the following…

  • Q. What’s your experience with the Separation Model?
    A. I’ve used it heavily over the last seven years, for a variety of vertical market applications, custom projects and, recently, on a vertical market FMGo app.
  • Continue reading “Radical Separation, part 1” →
Level: Intermediate, Version: FM 9 or later

Separation Aggregation Aggravation

Kevin Frank

7 June 2015: This technique has been substantially revised to work with FileMaker 14; see FM 14: Separation Aggregation Aggravation revisited.

FileMaker developers take it for granted that calculated sub-total and total fields will update automatically when portal rows are created, edited or deleted. This becomes problematic when one a) uses the separation model, and b) either intentionally or unintentionally holds the parent and related child records open (uncommitted) while editing, but still expects to see these aggregates update in real time.

7-3-2013 2-57-02 PMToday’s demo file is called aggregates-and-separation, and for demonstration purposes it has interface elements in both the Data file and the Interface file. Normally of course, in a separated solution, you wouldn’t have interface elements in the Data file (since that would defeat the purpose of separation). At the left, we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three aggregate calc fields below the portal are tracking the changes in real time. Continue reading “Separation Aggregation Aggravation” →