Formula to create code

[wilfred_hilbers]

In my database of garden plants I use the field WETENSCHAPPELIJKE NAAM, it has two or three strings, the genus, the species and/or the variety of the plant. the variety always between apostrophe’s.

I like to create a formula-field which automatically creates a CODE, the first character of each string in the field WETENSCHAPPELIJKE NAAM, without the apostroph.

I tried but without the result, can somebody help?

[ScottWorld]

Welcome to the community, @wilfred_hilbers!

I think your best bet is to keep manually writing the code like you’ve been doing.

Unfortunately, as far as I know, there is no way to automatically write that code with either a single Airtable formula or multiple Airtable formulas.

Airtable’s formula functions just aren’t complex enough to understand the concept of different words, and Airtable’s functions are not even complex enough to give you the position number of a 2nd or 3rd or 4th space in a field. Airtable formulas also do not offer any recursion abilities. In general, Airtable’s functions are relatively basic with a very limited amount of power.

Maybe someone else will be able to figure this out for you using a series of multiple different formula fields, but my guess is that you would probably need to turn to JavaScript to do this.

So it would likely be easiest for you to just keep typing in the code manually.

[Bill_French]

wilfred_hilbers:

I like to create a formula-field which automatically creates a CODE, t he first character of each string in the field WETENSCHAPPELIJKE NAAM, without the apostroph.

Given this sample data…

Is this kind’a what you’re attempting to do?

Resulting in this -

This is also possible in a formula field but it would be very complex like this one.

The trouble with using a formula to do this is that it will require some array handling and Airtable lacks Split(), so it’s far easier to accomplish this in Javascript using a Script Block. But this has downsides as well as you must actually run the script to update the fields and you have to know some javascript to modify this to meet more specific use-cases.

The Script…

/*

   ***********************************************************
   Airdrop - Code Builder
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

// display the title
output.markdown('# Create Codes');

// get the table name
output.markdown("### Select the Table");
let sourceTable     = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;

// get the source field name
output.markdown("### Select the Source Field");
let sourceField = await input.fieldAsync("Pick the name of the field to be ENCODED:", sourceTable.id);
let sourceFieldName = sourceField.name;

// get the target field name
output.markdown("### Select the Target Field");
let targetField = await input.fieldAsync("Pick the name field to be UPDATED with the code:", sourceTable.id);
let targetFieldName = targetField.name;

// get the data set for this gallery
let result = await sourceTable.selectRecordsAsync();
let sourceRecords = result.records;

// get the record count
let recordCount = sourceRecords.length;

// iterate across all the records
for (var r = 0; r < sourceRecords.length; r++)
{
  
    // get the name of this item
    var thisName       = sourceRecords[r].getCellValue("NAME");
    var thisNameTokens = thisName.replace(/'/ig, "");
    
    // compute the code
    var thisCode = thisNameTokens.toString().split(" ")[0].substring(0, 1) + thisNameTokens.toString().split(" ")[1].substring(0, 1)
    thisCode = thisCode.toLowerCase();

    output.markdown(thisCode + " :: " + thisName);

    await sourceTable.updateRecordAsync(sourceRecords[r].id, {
                [targetFieldName] : thisCode
            })

}

[wilfred_hilbers]

Thanks, I will try. It’s enough to run the script once, after that I will type on new entries. Don’t know yet how to run a script, but I’ll try to find out.

[Bill_French]

Yep - install the Script Block feature and paste the code in a new instance and run - should work.

[wilfred_hilbers]

Thank you! It worked, had to change the field name from name tot Wetenschappelijke naam, but then I got my plantcodes.

It now creates the code from the first two fields, so two characters. Is it difficult to create it with the first three fields, to get a three-characters code. The code will be more unique then.

I’m very happy until now! Thanks again.

[Bill_French · Accepted solution]

wilfred_hilbers:

Is it difficult to create it with the first three fields, to get a three-characters code. The code will be more unique then.

Yes - this is possible. If there is a plant with only 2 words in the name, we need to decide what to do. In the following change I assume the missing character in the code would be “*”, but it could be nothing (i.e., “”).

Replace this line:

var thisCode = thisNameTokens.toString().split(" ")[0].substring(0, 1) + thisNameTokens.toString().split(" ")[1].substring(0, 1)

… with this one:

var thisCode = thisNameTokens.toString().split(" ")[0].substring(0, 1) + thisNameTokens.toString().split(" ")[1].substring(0, 1) + ((thisNameTokens.toString().split(" ")[2] == undefined) ? "*" : thisNameTokens.toString().split(" ")[2].substring(0, 1));

Resulting in this output -

[wilfred_hilbers]

Wauw! You are good!!! Thank you.