Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum

A Thousand Ways to Manipulate Field Contents

Recommended Posts


I want to manipulate the content of one field in a found set of records. (I want to add leading zeros to some text dates in the format #.#.#### so they'll be ##.##.#### .)

So far I've been adding calculation fields to manipulate the field content in one way or another, and then replacing field contents in the target field of the found set.


But I'm starting to wonder about the effectiveness of this method, especially since the calculation is performed on 40 000 records, and I really only need to change a few hundred. The practical side of this is that I make sure it does what I want it to before I apply the results to the field in question. Certainly, there are may ways to peel & core an apple. A few other methods come to mind:


• write a script to go through the found set

• use the find/replace function – much much more limited, but at least you have more control at the moment it's carried out

• do it by hand!


What would you do?

Share this post

Link to post
Share on other sites

With 40,000 records, I would use a looping script.


Second choice would be Replace Field Contents. I would never use Find/Replace but then I never do anyway. (Just isn't a tool I ever had much use for *).


Why second choice? Because a looping script can have an optional pause inserted in it, in case you need to stop the operation midway through:



..If [PatternCount (Left (YourTable::TextDatefield; 2); "/")>0]

.... Set Field [YourTable::TextDateField; "0" & YourTable::TextDateField]

..End If

..If [Get (ActiveModifierkeys)=2]

.... Pause Script (indefinitely)

..End If

.. Go to Record [Next; Exit After Last]

End If


You can also stick up Data Viewer and put Get(RecordNumber) on it and hence get the equivalent of a progress bar.


With Replace Field Contents you can't pause, you can only cancel; and I think at any time that FileMaker acknowledges a cancel request during a Replace Field Contents, it discards changes not yet flushed to disk, meaning that a large chunk of processed records get reverted. Plus you don't have an obvious "this is where I got to" indicator; all you can do is scroll down to find the last one that actually appears to have a 0 appended to its front and restart from there.


Be that as it may, Replace Field Contents does have its own progress bar and is an effective way to go at it if you don't anticipate any need to pause the operation. If you had 40,000,000 recs instead of 40,000 I'd be a lot more emphatic about using a script.



No way in hell I'd do it by hand. Not for 40 records, let alone 40,000.



* Find / Replace made its debut in FileMaker 6. It was quickly dubbed it "Search and Destroy" because it defaults to the very dangerous operation of ALL fields in ALL records of the current found set. It won't do anything on a single field that Replace Field Contents won't do, and the latter is far more versatile than Find / Replace for anything except a "do this for ALL fields" operation. Oh, and I'll admit part of my disdain for the function is that, back when they rolled it out, the only way to take it away from end users was to take away Cut Copy and Paste (aka Editing functions) from their access privileges, because Fm Inc chose to treat it as an "editing function". So my attitude towards Find / Replace is tainted by an initial "yeesh WTF were they thinking?" reaction.

Edited by AHunter3

Share this post

Link to post
Share on other sites

  • Create New...

Important Information

Terms of Use