More Fun With Replace

Demo File:  all-countries-by-region.zip

Introduction

[Note: In this article I use the term “Replace” as shorthand for Replace Field Contents.]

This is a follow-up to a technique we looked at a couple years ago in Replace + GetNthRecord to Fill Down. At the time I had just imported some data from Excel and now that the data was in FileMaker I realized I needed to “fill down” the region. Starting from here…

…I wanted to end up here.

While there were of course various ways one might accomplish this, I was curious whether it could be done via a single Replace command, and without tampering with schema — i.e., without adding any additional tables, table occurrences, fields, relationships, value lists, etc. Just put the cursor in the field, press Cmd= (or Ctrl= on Windows), type in a magic incantation and hit Enter.

It turned out it was indeed possible, and you can see for yourself by running this script.

(For details see the earlier article.)

But then the little wheels started turning in my head, and I was curious whether it might also be possible via a single Replace to “undo” the fill down and put things back the way they’d been at the outset. And, again, part of the challenge would be to do it without touching schema, just a simple Replace on a bare bones table.

Well it turns out that this can be done also, and today we’re going to take a look at a couple ways one might go about it (no doubt there are other ways as well).

Before We Start

A few important things to be aware of when you do a Replace is that it…

• Operates on the current found set
• Respects the current sort order
• Iterates through the found set, one record at a time, starting with record #1 (regardless of which record you initiated the Replace from)

Also bear in mind that our starting point is a fully-loaded set of regions. Our goal is to de-populate, so if necessary run the “Populate” script before executing either of the following.

First Idea: Replace/SQL

Explanation

1. For each record, compare the current Country against the first country entry for that Region
2. If they match, keep the Region
3. Otherwise clear it

Note: ExecuteSQL doesn’t know about or care about the found set or sort order. In this case it doesn’t matter because we aren’t sorted and aren’t in a found set.

Second Idea: Replace/$variable

Explanation

1. Start with an empty $theList variable
2. If the current record’s Region is not in $theList…
   1. add it to $theList
   2. don’t clear the Region field
3. Otherwise it is already in $theList so clear the Region field
4. When we’re done, clear out $theList

Closing Thoughts

Declaring $vars and $$vars via Let is a highly useful trick to have in your toolbox, but bear in mind that, unless you want them to persist (sometimes you do), you take on a housekeeping burden of clearing them out once they’ve served their purpose.