- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 17, 2023 07:03 AM - edited ‎Oct 17, 2023 07:16 AM
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.
//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.
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 18, 2023 12:21 PM
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.
//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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 18, 2023 08:13 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 18, 2023 12:21 PM
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.
//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.
