What is the "Clear the Swamp" Technique?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
What is the "Clear the Swamp" Technique?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
What a wonderful question! In a nutshell, the Clear the Swamp technique is a handy-dandy technique to clean up a field in your database which has become messed up in some way due to inconsistent data entry or lack of adequate input validation using a set of regular expressions.

Let me demonstrate with some Cupcake Ipsum filler text obtained from this site:

Cupcake Ipsum ~ Sugar Coated Lorem Ipsum Generator
http://www.cupcakeipsum.com/

Here is a report of 100 records of delicious Cupcake Ipsum

https://haversineconsulting.quickbase.com/db/bmb3gqcsb?a=q&qid=6


For demonstration purposes, let's say our Cupcake Ipsum should properly be (1) listed in title case (ie first letter of every word capitalized), (2) have all vowels removed (who needs them!) and (3) have a newline substituted for the period at the end of sentences.

The Clear the Swamp technique is basically a one time usage script that is executed through the console.


Here is the script that makes all three of the above corrections to our Cupcake Ipsum and places the result in a new field.

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=531


And here is the result of executing the script through the console:

https://haversineconsulting.quickbase.com/db/bmb3gqcsb?a=q&qid=1


The script is very general and is controlled by a set of parameters that define the table, apptoken, query and fid:

var dbid = "bmb3gqcsb";
var apptoken = "pkcstad5vrharc4664uucpt258d";
var qid = "1";
var fidSource = "6";
var fidSourceTag = "text1";
var fidTarget = "7";
var fidTargetTag = "text2";
var batchSize = 20;

along with an array of regular expressions and replacement rules:

var rules = [{
  name: "To Title Case",
  pattern: /(?:^|\s)\w/g,
  replace: function(match) {
    return match.toUpperCase();
  }
}, {
  name: "Delete Vowels",
  pattern: /[aeiou]/gi,
  replace: function(match) {
    return "";
  }
}, {
  name: "Replace Periods With Newlines",
  pattern: /[.] ?/g,
  replace: function(match) {
    return "\n";
  }
}];

The first rule named "To Title Case" will convert all the text in the source field to title case.

The second rule named "Delete Vowels" will delete all vowels in the source field.

The third rule named "Replace Periods With Newlines" will replace all periods with newlines.

So if you look at the first record the [Text1] field is:

Cupcake ipsum dolor sit amet gummi bears pie chupa chups. Biscuit lollipop sweet roll cake brownie tiramisu bear claw sweet roll jelly-o. Brownie cake pastry ice cream macaroon soufflé jelly-o dragée jelly beans. Bonbon marzipan candy canes.

After running the script the first record will have a [Text2] field as follows:
Cpck psm Dlr St mt Gmm Brs P Chp Chps Bsct Lllpp Swt Rll Ck Brwn Trms Br Clw Swt Rll Jlly- Brwn Ck Pstry c Crm Mcrn Sfflé Jlly- Drgé Jlly Bns Bnbn Mrzpn Cndy Cns
Well that's the Clear the Swamp technique. By supplying your own parameters and rules you can clear your own swamp.
Great.