Help

Re: Converting string to array for creating record with multiselect field

3344 0
cancel
Showing results for 
Search instead for 
Did you mean: 
leggomyi
5 - Automation Enthusiast
5 - Automation Enthusiast

One of the inputs to my script is a string which needs to be reformatted such that it will be an acceptable value for a multiselect field when creating a new record.

There are three possibilities for the string:

  • “option 1”
  • “”
  • many options: “option 1, option 2, etc”

I know the format below is what I need to convert it to, but I can’t figure out how.

await table.createRecordAsync({
“Multiple Select Field”: [{name: option1}, {name: option2}, etc]
})

I’m omitting all the things I’ve tried which haven’t worked. I’m going to use the create records action instead of a script action until I can figure this out. Below is the skeleton of the script action I was using with some ??? where I was stuck.

Any help would be greatly appreciated! In the meantime, I’m leveling up my Javascript skills.

let config = input.config();
let recordID = config.recordID;

let table = base.getTable("Table_Name");
let view = table.getView("View_Name");
let query = await view.selectRecordsAsync({
    fields: ["String_Field"]
    });
let record = query.getRecord(recordID);

let multi_select_field;

const getRelevantValues = newSubmission => {
    multi_select_field = record.getCellValueAsString("String_Field");
}

getRelevantValues(record);


// Create Meetings record
table = base.getTable("Meetings");
await table.createRecordAsync({
    "Multiple Select Field": ???
})
7 Replies 7

Welcome to the Airtable community!

You can convert a string to an array using split.

You will then need to convert the resulting array into the write format. You can do that using a loop or a map. It depends on your comfort level with the different syntax and what additional processing you might need to do, such as checking for invalid values.

leggomyi
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi @kuovonne! Thank you for the welcome and direction.

I was able to use split and map to convert the string to an array and then into the write format. Now I’m trying to declate functions to do this which will take the field name as the argument, but I’m falling short. Any other hints would be appreciated. I know this is remedial so thanks for your generosity and patience!

// Example value of Multi_Select_Field: "Literature, Discussion"

// I can get them into the write format like this (successfully creating records)
Multi_Select_Field = Multi_Select_Field.split(/\s*(?:,|$)\s*/)

Multi_Select_Field = Multi_Select_Field.map(obj => {
        let rObj = {};
        rObj["name"] = obj;
        return rObj;
})

// struggling to turn them into functions
const convertStringToArray = fieldName => fieldName = fieldName.split(/\s*(?:,|$)\s*/);

const convertArrayToWriteFormat = fieldName => {
    fieldName.map(obj => {
        let rObj = {};
        rObj["name"] = obj;
        return rObj;
    })
}

convertStringToArray(Multi_Select_Field);
convertArrayToWriteFormat(Multi_Select_Field);

table = base.getTable("Other_Table");
await table.createRecordAsync({
    "Multi_Select_Field": Multi_Select_Field
});

Congratulations! Learning to code can be very rewarding.

Creating functions is a fundamental aspect of writing code. There are many different ways of creating functions in JavaScript. The most common are …

  • functions created with the function keywork
  • arrow functions created and immediately assigned to a variable
  • completely anonymous functions

It looks like you are using the last two methods, but not the first one. Many JavaScript courses will cover this information. If you would like personal coaching on script writing and these three different methods of creating functions, you can book an appointment with me.

Welcome to the community! You’ve got a lot of good advice here already, so I’ll try to keep this short and only list a few more things that came to mind while reading your code snippets:

Do not mutate data if you can help it; unless that’s your explicit goal.

Always be cloning stuff instead. All modern browsers and popular runtime environments like Node use JS engines with excellent garbage collection, so why not take full advantage of that instead of hunting mind-numbing type errors every other day?

The way you’re using that Multi_Select_Field variable, for example, is how type errors happen. No matter how experienced you are; if you keep dragging the same object through a score of functions, you’re basically setting yourself up for failure. Especially within the limited, heavily sanitized environment of the Scripting block, far away from the safety provided by AI-infused linters and whatnot.

You don’t have to worry about the ‘why’ just yet but the short story is that JavaScript’s single-threadedness and prototypal duck typing are basically a recipe for an infinite source of gotchas befuddling both newcomers and veterans alike.

Making it all the more important to develop good habits early on:

  • write single-purpose functions that either query, sort, or mutate data, but never combine those activities
  • comment your code because you’ll forget why things work the way they do
  • cherry-pick whatever parts of object-oriented, functional, procedural, and logical programming you like for your particular use case as you go along
    • mastering any given one will take longer but every paradigm has its strengths and JavaScript’s biggest selling point is its flexibility; with time, you’ll begin to understand how even the quirkiest aspects of the language have their uses
  • this includes getting comfortable with every way of writing functions and learning to recognize when one might be preferred over another, as Kuovonne already suggested
  • variable naming might be the single most deceptively difficult skill to pick up in any language; there’s a rather thin line between names that are informative, and those that prevent you from seeing the forest for the trees; you seem to be leaning toward a more verbose approach right now; that’s preferable when you’re just starting out, but try to spend more time on deciding how to name your functions and objects and you might be surprised how big of a difference that will make in terms of maintaining and optimizing code
    • here’s a more direct piece of advice - use camel case, pascal case, and snake case, in that order of frequency (ie. snake case is conventionally reserved for constant values like input.config data in the Scripting block because it’s the most annoying to write)
    • more importantly, be consistent even if you end up sticking with your current preferences, same goes for semicoln usage
  • don’t forget to have fun, coding is like the ultimate skill for scaffolding crazy entertaining hobby projects in an afternoon; if you get stuck, especially in the beginning, sometimes it’s better to start from scratch than waste time debugging a dumpster fire

EDIT:
Oh, and regarding code, I’m not sure why are you querying the field as a string, it just makes serialization more annoying and convoluted, as is the case here:

const convertStringToArray = fieldName => fieldName = fieldName.split(/\s*(?:,|$)\s*/);

Regardless of whether the field you’re querying has colored selections or not, it would be easier to just query it with a getCellValue method an filter out the things you don’t need instead of pulling a string representation of an object and then ducktaping that data back into an array.

Right now, this function probably isn’t even doing its job, in spite of all the effort, because some of the special characters haven’t been properly escaped. Even if that wasn’t the case, I’m like 65.12% sure that ‘?:’ invalidates your entire capture group, while the double \s* patterns most likely aren’t doing much, either.

If all of your multi-select options consist of a single word, you could salvage the getCellValueAsString approach by doing something like:

const toArray = string => string.split(/\b/).filter(w => w.length)

The split pattern slices the string at every word break, while the filter takes care of empty-string tokens that this approach generates, leaving you with an array of individual words.

Alternatively, replace the /\b/ with an /,/ (or just ‘,’) if you’re dealing with multi-word selections. And if your selections have commas in them, then it’s probably time to start rethinking your base design before throwing elaborate regex patterns at the problem lol.

I’ll try to keep this short and-

Fission mailed. 🥸

Thanks for both of your replies. I am going to take the advice to heart.

@Dominik_Bosnjak
The background for this is users fill out a form built on Typeform. We use Zapier to add each submission to a “Submissions” table in Airtable. Answers to a multi-select question in Typeform are treated as strings: “option 1, option 2”. For each new record on the “Submissions” table, I am trying to create a new record on another table (“Table2”, let’s say). The script needs to take that string—“option 1, option 2”—and convert it to the write format of the corresponding multi-select field on “Table2”.

“option_1, option 2, option 3” → {name: option_1}, {name: option_2}, {name: option_3}

// fieldName is the field on the Submissions table populated with the string from the mult-select question in Typeform
const convertStringToArray = fieldName => {
    fieldAsArray = fieldName.split(/\s*(?:,|$)\s*/);
}

// takes the output of convertStringToArray to convert it to the write format of a multi-select field
const convertArrayToWriteFormat = arrayInput => {
    arrayInput.map(obj => {
        let rObj = {};
        rObj["name"] = obj;
        return rObj;
    })
}

let fieldAsArray;
// typeformMultiSelectField = "Literature, Discussion"
convertStringToArray(typeformMultiSelectField); // returns ["Literature", "Discussion"]
let format = convertArrayToWriteFormat(fieldAsArray); // returns undefined instead of [{name: "Discussion"}, {name: "Literature"}]

// creates record but does not pass "Literature" and "Discussion" as multiselect values int the Format field
table = base.getTable("Meetings");
await table.createRecordAsync({
    "Format": format
});

It can be difficult to develop these habits on your own because these are practices are a matter of style. The purpose of these habits is to make it easier for humans (including our current and future selves) to develop, debug, and maintain our code. The computer running the code doesn’t care about these things, just as it doesn’t care if it spits out the result you want or not.

Back when I was in grad school, I thought variable naming would make an interesting research project. I still do.

That reminds me of this classic:

image

@leggomyi, your convertArrayToWriteFormat function returns undefined because of the needless curly braces. The return would have been implied otherwise.

But you could have simply ended that section by writing arrayInput.map(obj => obj=({name:obj})).

So long as your code is still readable and acts the same, you should always strive for brevity because a smaller code base means fewer bugs.

Case in point - a probably better solution would have been to use Array.prototype.reduce to skip most of that final snippet of code you’ve shared.

Other than that, I’m not following the train of thought that led to the confusion referenced by that final comment; you called the createARecordAsync function and provided it with a single key-value pair. That’s always going to result in a single cell getting filled - at best.

Other than that, you probably aren’t aware that if you named your format variable Format, or had an all-lowercase field name, you could have just called the createARecord function like this:

base.getTable('Meetings').createRecordAsync({Format})

The parameter destructuring happens automatically whenever an object property has a namesake in scope, as is almost the case here. And creating that sole record is such a trivial microtask for Airtable and what I assume is the underlying Node infrastructure that you probably don’t need to await that call. Especially since there’s nothing to await - this being your final act in this script and whatnot.

This reminds me of another good habit you ought to at least try to pursue consciously = testing early and often. Even if you’re on fire and spend a whole day coding to the point of writing 700 perfect lines of code, you’re still going to waste twice as much time finding that one typo that tripped up the interepreter than what it would have taken you to finish if you were less eager and more paranoid.

‘Defense is the best offense’ isn’t something that often rings true but defensive coding is how you keep your sanity and continue improving - assume you’re always making mistakes, never stop testing your code, and no matter how much you mess up, you’ll tend to catch your own missteps early, before the frustration sets in and prevents you from learning something from them.