Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Script/Formula Solution for Extracting Contiguous Text Counts (Location Dependent)

Topic Labels: Automations
282 3
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi All,

I’m looking for a simple/elegant solution for a formula/script that will produce what I’m looking for.

I have a text field (it will have a maximum of 9 ALPHA (uppercase only) characters in it. I’m looking for a solution that will perform the following functions.
a. If a character is contiguous to itself if will count the total contiguous characters and place the number followed by the character. For example if the text field was PPPPP I would get a result of 5P.
b. If a character has no contiguous matches then the result would just be the characters as shown. For example QRSTUV would give a result of QRSTUV.
c. Combining these two rules together if we were to have RRQRRRQS the result would be 2RQ3RQS. I can easily find a solution if I wanted the result of an input sting of RRQRRRQS to be 5R2Q1S but the sequence has to remain as was originally shown as each value is assigned to a corresponding column (column 1 through 9 which is associated with a quantity level) so this wont work.

Here is some actual data and the desired results.

Count String Desired Result
1 RSSSSTTZZ R4S2T2Z
2 RRSSSST 2R4ST
3 QRSSSST QR4ST
4 PRRRRST P4RST
5 RRSSSTT 2R3S2T
6 RSSSSTT R4S2T
7 PQRRRST PQ3RST
8 PRRRRST P4RST
9 RRRRRST 5RST
10 RQRRRST RQ3RST
11 PRRRRST P4RST
12 RRSSSTT 2R3S2T
13 RRRRRST 5RST
14 RRRRRST 5RST
15 QRRRRST Q4RST
16 QRRRRST Q4RST
17 QPQRRST QPQ2RST
18 RRSSSST 2R4ST

Any thoughts or suggestions on how to proceed are much appreciated!

Tom

3 Replies 3

After a quick google search I found this simple javascript function which will get you half way there:

Modified slightly, here’s the basic code:

var str = "RSSSSTTZZ"

function letterCount(str){
    var s= str.match(/([a-zA-Z])\1*/g)||[];
    return s.map(function(itm){
        return [itm.length, itm.charAt(0)];
    });
}

const arr = letterCount(str)

console.log(arr.toString().replace(/1/g, '').replace(/,/g,''))

To make the above actually usable, you’d replace the first line with pulling in data from a particular record, and replace the last line with a function to actually update the record. There are several examples of both throughout these forums, but I can help guide how to do it if you need further assistance.

Thank you so much for your quick response, Kamille_Parks!

So I was able to get your script to work perfectly with the output showing up in the console log.

Now I’ve set up an automation when the field “FinalDiscountCodeString” is modified in the “Product” table, the script will run and put the result in the “FinalDiscountCodeScriptRun” field also in the “Product” table. Here’s what I was able to come up with but I’m keep getting errors:

let table = base.getTable(“Product”);

let str = input.config();

function letterCount(str){

var s= str.match(/([a-zA-Z])\1*/g)||[];

return s.map(function(itm){

    return [itm.length,itm.charAt(0)];

});

}

const arr = letterCount(str)

table.updateRecordAsync.FinalDiscountCodeScriptRun(arr.toString().replace(/,/g,’’).replace(/1/g,’’))

Being a novice at scripting I know that this can be done but I seem to be beating a dead horse on new ideas for what to try to get the code to go through…any pointers, are greatly appreciated.

Thanks again!

To do this via an Automation you will need two Input Variables: one for str (the {FinalDiscountCodeString} field value from the trigger record), and one for recordID (the trigger record’s id)

So to pull in these variables properly, change:

let str = input.config();

to

let {recordID, str} = input.config();

And change:

table.updateRecordAsync.FinalDiscountCodeScriptRun(arr.toString().replace(/,/g,’’).replace(/1/g,’’))

to:

await table.updateRecordAsync(recordID, {
   "FinalDiscountCodeScriptRun": arr.toString().replace(/,/g,’’).replace(/1/g,’’)
}