Re: Scripting (or formula building) assistance request!

Taylor_Johnston · ‎Feb 23, 2021

Hi –

In an effort to save weeks worth of research, I’m hoping to find someone who may know the most efficient way to build out an automation process. I have no experience with computer language or programming and am officially waving my white flag. :rofl:

I essentially need to build a custom numerology app.
I need to be able to press a button in my Airtable and run a program to break down each name (first, middle, last - each in separate columns) into individual characters and assign each letter to a number.
There are more steps to the process as a whole, but this is my primary need for assistance.

I’m fairly certain this can be done with a formula by splitting the text string (name) into individual characters; however, I don’t know how to make this a dynamic process (for the len and/or mid functions) nor do I know how to run a split function without a delimiter.

If anyone has any suggestions, all feedback is appreciated!
As briefly mentioned, there are more steps to this process and I would be more than willing to hire someone to help me build out a few of my needs - I’m a new business owner and the need for automation is rising exponentially. Please PM me if this is something you would be interested in discussing - otherwise, any advice will help tremendously. :slightly_smiling_face:

UPDATE
I was able to get a great start on the script thanks to Bill (code can be found in the comments) – I’m still searching for someone who can help me finish the rest, so I’m going to put the remaining project details below. :slightly_smiling_face:

Bill’s quick script is nearly perfect - I just need for the conditional characters (oBaseClass) to search the entire string for the letter combinations instead of just the first 2 letters. For example: TAylor has one condition, whereas TAbaTHa and ASHworTH would have 2 conditional values assigned.

The remaining steps may seem complicated/extensive at first glance, but it’s actually very simple (just specific details).

Instead of adding up the total for each name, the individual values will be assigned to a specific column (6 columns) in sequential order. After the first 6 letters fill all the columns, you just start at column 1 again and loop through until every letter in the name has been assigned.

for example:
Let’s let our 6 columns be labeled as such:
column 1: PK
column 2: SK
column 3: PT
column 4: ST
column 5: PG
column 6: SG

In this photo you can see the numerology value of each character and the column it would be assigned to:

This is where the values would now be added together. There is a special rule for this step, as well. The final value in each column will consist of a pair of numbers (X-X) instead of a single sum total.
You will add all of the numbers together for an initial total. If that total is more than than 22, you will reduce the number by adding the individual components (ex: total=46 ; 46>26, so reduce: 4+6 = 10 ; 10<22, so :thumbs_up: ) < this will be the first number of the pair. The second number is the furthest reduction of the total to a single digit number (ex. 10 = 1+0 =1 ; FINAL = 10-1

here is a complete example using my name as we did in the earlier screenshots:

I know that’s a lot of info - but I’m more than happy to answer any questions about the process & will gladly compensate for time spent on development of this script if anyone has the capacity to assist! This would be a tremendous help to me, and I appreciate you taking the time to read my request (if you made it this far :rofl: ).

Thank you!!

Raminder_Singh · ‎Feb 23, 2021

Hi @Taylor_Johnston,

Instead of extracting the first, middle and last names from the full name, it would be easier to keep the first, middle and last names each their own fields and then combine them into one full name field using a formula. Once you have each name in a separate field it can definitely be processed using a script in whatever way you want. The community can help if you can share more details.

-Raminder

Taylor_Johnston · ‎Feb 23, 2021

Hi, @Raminder!

Thank you so much for your response. That’s a relief to hear.

Here are more details for my request:
My customers submit orders through Typeform (linked to my Airtable already). First, middle, and last name are all separate questions on the form & are in separate fields in my base.
The breakdown process will need to begin with checking for specific conditions before splitting each name into individual characters. In my numerology method, there are a few letter combinations that will need to act as one character instead of as individual characters.
These are the combinations that need to be checked for (the last 2 rows of the photo refers to any name [first, middle, or last] that ends in “M” or “P”)

Whereas this is the “normal” table:

For example:
My name is Taylor Breanna Johnston. “Ta” is one of the special conditions, so my name broken down will be –

Does this make sense?

Raminder_Singh · ‎Feb 23, 2021

Yes it does make sense. You could start with something like this:

let twoLettersToNumbers = {
    "AH": 5,
    "CH": 8,
    "WH": 16,
    "TA": 22,
}

let table = base.getTable("Table1");
let queryResult = await table.selectRecordsAsync();

for (let record of queryResult.records) {
    let firstName = record.getCellValueAsString("First Name");
    let twoLetters = firstName.substr(0, 2).toUpperCase();
    console.log(`Name is ${firstName}. Two letters is ${twoLetters}`);
    if (twoLetters in twoLettersToNumbers) {
       //Do whatever with your numbers 
       console.log(`Number for ${twoLetters} is ${twoLettersToNumbers[twoLetters]}`);
    }
}

Now this is just a starting point and not complete but I’m in a rush. I might come back to it later if someone else doesn’t beat me to it.

Taylor_Johnston · ‎Feb 23, 2021

Ah!
Amazing - thank you so much for taking the time to begin the script, @Raminder_Singh!
:blush:
This is a great start!!

Bill_French · ‎Feb 23, 2021

Hi @Taylor_Johnston, and welcome to the community!

The beauty of javascript is that every string (i.e., name) is already split because it’s – at its core – an array of letters. As such, extracting any given letter is as simple as:

let thisName = "Taylor";
let thisLetter = thisName[3]
output.text(thisLetter);  // will display "a".

Because of this, a script block is able to provide a fairly elegant and performant way to perform the numerology computations.

Ideally, you need a framework like this that uses a script block to perform the computations quickly and comprehensively.

Furthermore, in addition to the combined numeric score, I suspect you’ll want the underlying computations like this.

Taylor_Johnston · ‎Feb 23, 2021

Oh, this is beautiful!!
This is exactly what I’m looking for – & you made it look so simple!

Do you accept requests to create custom programs like this?
I would love to collaborate to provide the full scope of my process and build out the script. (There are a couple more steps after the initial breakdown to get sub-categories)

& thank you for the warm welcome! :blush:

Bill_French · ‎Feb 23, 2021

Yes, I do. However, I’m pretty slammed. I use simple examples like this to wake up in the morning. Getting my hands dirty on basic stuff is therapy; sort’a like coffee injected directly into the brain. :winking_face:

I’m also a numbers and data geek, so when I saw your plea for help, I couldn’t resist. By taking a unified approach to looking at this as purely data and analytics, Airtable makes it relatively easy to do fun stuff like this.

The code for this is below. Note that there are many ways to approach this solution - I chose the fast and furious approach, but if I had more time, the code would surely be a lot smaller. :winking_face:

I typically charge 99 cents a line, but in this case, it was purely unsolicited feedback so feel free to pay it forward.

Enjoy…

/*

   ***********************************************************
   ScriptBloqs - Numerology
   Copyright (c) 2021 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

output.markdown('# Numerology Computations');

//
// create the computational arrays
//
let oBaseClass = {};
    oBaseClass["ah"] = 5;
    oBaseClass["ch"] = 8;
    oBaseClass["wh"] = 16;
    oBaseClass["tz"] = 18;
    oBaseClass["sh"] = 21;
    oBaseClass["ta"] = 22;
    oBaseClass["th"] = 22;

let oEndClass = {};
    oEndClass["m"] = 12;
    oEndClass["p"] = 12;

let oCoreClass = {};
    oCoreClass["a"] = 1;
    oCoreClass["b"] = 2;
    oCoreClass["c"] = 11;
    oCoreClass["d"] = 4;
    oCoreClass["e"] = 5;
    oCoreClass["f"] = 18;
    oCoreClass["g"] = 3;
    oCoreClass["h"] = 5;
    oCoreClass["i"] = 10;
    oCoreClass["j"] = 10;
    oCoreClass["k"] = 19;
    oCoreClass["l"] = 12;
    oCoreClass["m"] = 13;
    oCoreClass["n"] = 14;
    oCoreClass["o"] = 6;
    oCoreClass["p"] = 17;
    oCoreClass["q"] = 19;
    oCoreClass["r"] = 20;
    oCoreClass["s"] = 15;
    oCoreClass["t"] = 9;
    oCoreClass["u"] = 6;
    oCoreClass["v"] = 6;
    oCoreClass["w"] = 6;
    oCoreClass["x"] = 15;
    oCoreClass["y"] = 16;
    oCoreClass["z"] = 7;

// get the name of the table
let table = base.getTable("Numerology");

// get the records from this table
let records = await table.selectRecordsAsync();

// Update the records
let totalScore = 0;
let computations = "";
for (let record of records.records) {

    if (record.getCellValue("Full Name"))
    {

        totalScore = 0;
        computations = "";

        output.markdown("### " + record.getCellValue("Full Name"));

        // get the names into variables
        let thisFirst  = record.getCellValueAsString("First");
        let thisMiddle = record.getCellValueAsString("Middle");
        let thisLast   = record.getCellValueAsString("Last");

        // display and log the full name
        // output.markdown("### First Name: " + thisFirst);
        computations += record.getCellValue("Full Name").toUpperCase() + "\n\n";

        // log the first name
        computations += thisFirst.toUpperCase() + "\n";

        // test first two characters of the first name
        totalScore = (oBaseClass[thisFirst.toLowerCase().substring(0, 2)]) ? totalScore + oBaseClass[thisFirst.toLowerCase().substring(0, 2)] : totalScore;
        computations += "First Two Characters = " + thisFirst.toLowerCase().substring(0, 2) + " :: " + evaluate(oBaseClass[thisFirst.toLowerCase().substring(0, 2)]) + "\n";

        // compute core class values
        for (var i = 2; i < thisFirst.length; i++)
        {
            totalScore = (oCoreClass[thisFirst.toLowerCase()[i]]) ? totalScore + oCoreClass[thisFirst.toLowerCase()[i]] : totalScore;
            computations += "Character = " + thisFirst.toLowerCase()[i] + " :: " + evaluate(oCoreClass[thisFirst.toLowerCase()[i]]) + "\n";
        }

        // compute core class values for middle name
        computations += "\n" + thisMiddle.toUpperCase() + "\n";
        for (var i = 0; i < thisMiddle.length; i++)
        {
            totalScore = (oCoreClass[thisMiddle.toLowerCase()[i]]) ? totalScore + oCoreClass[thisMiddle.toLowerCase()[i]] : totalScore;
            computations += "Character = " + thisMiddle.toLowerCase()[i] + " :: " + evaluate(oCoreClass[thisMiddle.toLowerCase()[i]]) + "\n";
        }

        // compute core class values for last name
        computations += "\n" + thisLast.toUpperCase() + "\n";
        for (var i = 0; i < thisLast.length; i++)
        {
            totalScore = (oCoreClass[thisLast.toLowerCase()[i]]) ? totalScore + oCoreClass[thisLast.toLowerCase()[i]] : totalScore;
            computations += "Character = " + thisLast.toLowerCase()[i] + " :: " + evaluate(oCoreClass[thisLast.toLowerCase()[i]]) + "\n";
        }

        // compute the last characters of each name
        totalScore = (oEndClass[thisFirst.toLowerCase().slice(-1)]) ? totalScore + oEndClass[thisFirst.toLowerCase().slice(-1)] : totalScore;
        computations += "Last Character = " + thisFirst.toLowerCase().slice(-1) + " :: " + evaluate(oEndClass[thisFirst.toLowerCase().slice(-1)]) + "\n";

        totalScore = (oEndClass[thisMiddle.toLowerCase().slice(-1)]) ? totalScore + oEndClass[thisMiddle.toLowerCase().slice(-1)] : totalScore;
        computations += "Last Character = " + thisMiddle.toLowerCase().slice(-1) + " :: " + evaluate(oEndClass[thisMiddle.toLowerCase().slice(-1)]) + "\n";

        totalScore = (oEndClass[thisLast.toLowerCase().slice(-1)]) ? totalScore + oEndClass[thisLast.toLowerCase().slice(-1)] : totalScore;
        computations += "Last Character = " + thisLast.toLowerCase().slice(-1) + " :: " + evaluate(oEndClass[thisLast.toLowerCase().slice(-1)]) + "\n";

        // display the combined score for this person's name
        output.markdown("Combined score: " + totalScore);

        // update the fields
        await table.updateRecordAsync(record, {
            "Score"        : totalScore,
            "Computations" : computations,
        });

    }
}

//
// if null, return zero
//
function evaluate(str)
{
  return((str === undefined) ? 0 : str);
}

Taylor_Johnston · ‎Feb 23, 2021

It’s nice to know I’m not the only one who thrives on mental stimulation for entertainment! :nerd_face:

You are an angel :pleading_face:
Thank you so so much for sharing a freebie with me - I can certainly understand why your services are in high demand.
This gives me a much better foundation to experiment & learn from (& a heck-of-a head start for other programmers who may be able to assist :rofl: )

Thanks again!