Skip to main content
Solved

Forms: Multiple inputs in a single field box

robinsonj
Forum|alt.badge.img+6

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. 

Show content

//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.

Best answer by robinsonj

robinsonj wrote:

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.


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.

Show content

//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.

View original
Did this topic help you find an answer to your question?

2 replies

robinsonj
Forum|alt.badge.img+6
  • robinsonj
  • Author
  • Inspiring
  • 5 replies
  • October 18, 2023

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
Forum|alt.badge.img+6
  • robinsonj
  • Author
  • Inspiring
  • 5 replies
  • Answer
  • October 18, 2023
robinsonj wrote:

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.


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.

Show content

//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.

S
1 person likes this

Reply


Most helpful members this week

Terms & Conditions