Help

Forms: Multiple inputs in a single field box

Topic Labels: Automations Data
Solved
Jump to Solution
1591 2
cancel
Showing results for 
Search instead for 
Did you mean: 
robinsonj
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi all--

We have a form that staff use to pitch a new idea or project for our organization. We want to connect their name and email (as submitted in the form) to a preexisting linked record with their details.  Sometimes, staff will fill in multiple names and emails at once, separated by commas. This would look something like this:

[PROJECT LIST TABLE]

[Project Name Field]: Special Event
[Name Field] : John Doe, Jane Doe
[Email Field]: johndoe@org.com, janedoe@org.com 
[Project Details Field]: A special event that we will be working together to create for the organization.

I would like to have an automation or script pull apart the comma-separated values, search for each one in the linked Staff table, and if the name or email exists in the linked table, add that name/email's linked record to a separate [Project Staff] field in the Project List table. Theoretically, it's simple! But it's been about a decade since I've done any scripting, and if lack of practice makes one "rusty," I'm probably closer to "disintegrating." Below was my stab at coding a script, but I'm getting an error about the array not being iterable, and I'm certain it's got other issues as well. 

Spoiler
//get Input from new record
let inputconfig = input.config();

//get Name & Email Submitted field data
let nameString = inputconfig.Name;
let emailString = inputconfig.Email;

//split name data by the word "and", "(", or ","
nameString = nameString.replace(')','');
let nameArray = nameString.split(' and ').join(',').split('(').join(',').split(',');


//split email data by the word "and", ";", or "," and clean up emails
let emailArray = emailString.split(' and ').join(',').trim().split(',');


//contact the Staff table and get access to full name & email address fields
let staffTable = base.getTable("Staff");
let queryArray = await staffTable.selectRecordsAsync({
    fields: ["Email Address", "Full Name"]
});
//create I iterative variable
let i=0;

//connect Staff Name from Staff Table to Pitching Staff field
for (const name in nameArray){
    for (const record of queryArray[i]){
        if (record.getCellValueAsString("Full Name").toLowerCase() == nameArray.toString().toLowerCase()){
            output.set("Pitching Staff",  record.id )
            i++
        }
        else if (record.getCellValueAsString("Email Address").toLowerCase() == emailArray.toString().toLowerCase()){
            
            output.set("Pitching Staff",  record.id )
            i++
        }   
    };
    
};

Nobody else on my team has experience coding or scripting; it was not part of the job requirements. As such, I'm happy to try and learn how to fix my code OR just have a solution given. However, I'm looking for a solution that does not require an external/non-Airtable extension.

Thank you for your time.

1 Solution

Accepted Solutions
robinsonj
5 - Automation Enthusiast
5 - Automation Enthusiast

I appear to have finally figured out my issue. 
After running the script, I used the update record automation and put the data output by this script into the linked field.
Working code below.

Spoiler
//get Input from new record
let inputconfig = input.config();

//get Name & Email Submitted field data
let nameString = inputconfig.Name;
let emailString = inputconfig.Email;

//split name data by the word "and", "(", or ","
nameString = nameString.replace(')','');
let nameArray = nameString.split(' and ').join(', ').split(' (').join(', ').split(', ');

//split email data by the word "and", ";", or "," and clean up emails
let emailArray = emailString.split(' and ').join(', ').split(', ');

//Combine two arrays into one array for easier searching
let inputArray = [nameArray, emailArray];

//Add a value to prevent undefined errors for sparse array
inputArray.forEach( (inputrec, index) =>   {
    if ((inputArray[index][0] === undefined) && (inputArray[index][1] != undefined))
        inputArray[index][0] = "0";
    else if ((inputArray[index][1] === undefined) && (inputArray[index][0] != undefined))
        inputArray[index][1] = "0";
});

//contact the Staff table and get access to full name & email address fields
let staffTable = base.getTable("Staff");
let staffArray = await staffTable.selectRecordsAsync({
    fields: ["Full Name", "Email Address"]
});

//create an array to hold the pitching staff ids
var pitchingstaffids = [];

//Iterate through input
inputArray.forEach( (inputrec, index) => {
    //Iterate through Staff Table
    staffArray.records.forEach( (record) => {
        //Compare Names & Emails of input to Staff Table data
        if (
            (record.getCellValueAsString("Full Name").toLowerCase() == inputArray[index][0].toString().toLowerCase()) || 
            (record.getCellValueAsString("Email Address").toLowerCase() == inputArray[index][1].toString().toLowerCase())
            )
            //add record ID to pitchingstaffids
            {pitchingstaffids.push(record.id);}
    });
});
//put record ids from array in the Pitching Staff field if pitchingstaffids is not empty
if (pitchingstaffids.length != 0)
{output.set("Pitching Staff", pitchingstaffids);}  

Happy to accept any other ideas or constructive criticism to help make my code more efficient and effective.

See Solution in Thread

2 Replies 2
robinsonj
5 - Automation Enthusiast
5 - Automation Enthusiast

First issue solved--Error of "queryArray not iterable"

    for (const record of queryArray[i]){

should be

    for (let record of queryArray.records){

 and I can remove the iterator i++. I feel stupid, but it's progress. I still have to figure out how to set multiple record id outputs at once as output.set function overwrites the data instead of adding it. This should be much easier.

robinsonj
5 - Automation Enthusiast
5 - Automation Enthusiast

I appear to have finally figured out my issue. 
After running the script, I used the update record automation and put the data output by this script into the linked field.
Working code below.

Spoiler
//get Input from new record
let inputconfig = input.config();

//get Name & Email Submitted field data
let nameString = inputconfig.Name;
let emailString = inputconfig.Email;

//split name data by the word "and", "(", or ","
nameString = nameString.replace(')','');
let nameArray = nameString.split(' and ').join(', ').split(' (').join(', ').split(', ');

//split email data by the word "and", ";", or "," and clean up emails
let emailArray = emailString.split(' and ').join(', ').split(', ');

//Combine two arrays into one array for easier searching
let inputArray = [nameArray, emailArray];

//Add a value to prevent undefined errors for sparse array
inputArray.forEach( (inputrec, index) =>   {
    if ((inputArray[index][0] === undefined) && (inputArray[index][1] != undefined))
        inputArray[index][0] = "0";
    else if ((inputArray[index][1] === undefined) && (inputArray[index][0] != undefined))
        inputArray[index][1] = "0";
});

//contact the Staff table and get access to full name & email address fields
let staffTable = base.getTable("Staff");
let staffArray = await staffTable.selectRecordsAsync({
    fields: ["Full Name", "Email Address"]
});

//create an array to hold the pitching staff ids
var pitchingstaffids = [];

//Iterate through input
inputArray.forEach( (inputrec, index) => {
    //Iterate through Staff Table
    staffArray.records.forEach( (record) => {
        //Compare Names & Emails of input to Staff Table data
        if (
            (record.getCellValueAsString("Full Name").toLowerCase() == inputArray[index][0].toString().toLowerCase()) || 
            (record.getCellValueAsString("Email Address").toLowerCase() == inputArray[index][1].toString().toLowerCase())
            )
            //add record ID to pitchingstaffids
            {pitchingstaffids.push(record.id);}
    });
});
//put record ids from array in the Pitching Staff field if pitchingstaffids is not empty
if (pitchingstaffids.length != 0)
{output.set("Pitching Staff", pitchingstaffids);}  

Happy to accept any other ideas or constructive criticism to help make my code more efficient and effective.