Help

Re: "Create records from multiple select field choices" script

2605 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Hillary
4 - Data Explorer
4 - Data Explorer

Hello! I am a first-time poster using the following template script which is part of the Airtable +Add an app section (script below).

I am using Airtable for hiring. Within my Applicants table, I have people (field name: Applicant Name) who have applied for multiple positions (field name: Active Positions Applied For) of which some are under consideration (field name: Active Positions Under Consideration). I am looking to automate rejection and advancing to next stage emails but am running into difficulty with the following scenarios due to multiple selection:

  • Candidate applies for 2+ positions, of which 0 are under consideration – want to send separate rejection emails to positions applied for (current automation sending as one combined email only)
  • Candidate applies for 2+ positions, of which 1 is under consideration – want to send rejection email to position not under consideration only
  • Candidate applies for 2+ positions, of which 1 is under consideration and then later no longer under consideration – want to send separate rejection email to both without duplication / re-sending original
  1. Would creating a child table (table name: Test) off the parent (table name: Applicants) data using the script template resolve this issue as I can build automations based off single rows in the child table? Eg. Person applies to 3 positions and each position is handled separately with separate reject / advance email

  2. Getting an error on Line 36 when I run script below: TypeError: Cannot read property ‘length’ of undefined at main on line 36

  3. Not sure that I have chosen my 5 options correctly. Essentially, I want all data in Applicants table to be shown in the Test table but with each position applied for and subsequent position/s under consideration being its own row eg. applied for and under consideration Position A = 1 row

  4. Is there an alternative way to turn a multiple select into separate rows all linked to the same person?

  5. Is there an IF statement or formula for only sending separate rejection emails to Active Positions Applied For that DO NOT MATCH Active Positions Under Consideration?

  6. Any recommended resources to address my goals are appreciated!

Thank you for your help! Script below.

Airtable - Create records from multiple select field options

// Script settings
let s = input.config({
title: ‘ :part_alternation_mark: Create records from multiple select field options’,
description: ‘Creates 1 record in another table for each option in a multiple select field in the source table, and links the new records back to the source record.’,
items: [
// Source table select
input.config.table(‘tableSource’, {
label: ‘ :fallen_leaf: Table with existing records’
}),
// Source table: Multiple select field with deliverables
input.config.field(‘delivField’, {
parentTable: ‘tableSource’,
label: ‘ :fallen_leaf: Multiple select field with names of records to create’,
}),
// Destination table select
input.config.table(‘tableDest’, {
label: ‘ :seedling: Table to create new records in’
}),
// Destination table: Name or title field
input.config.field(‘destinationField’, {
parentTable: ‘tableDest’,
label: ‘ :seedling: Deliverable name or title field in destination table’,
}),
// Destination table: Linked record field (back to the Source table record)
input.config.field(‘projField’, {
parentTable: ‘tableDest’,
label: ‘ :seedling: Linked record field (links to table with existing records)’,
}),
]
});
// You can use a button field that points to this script to run it for specific records,
// or you can run the script directly, in which case the following prompts the user to pick a record
let r = await input.recordAsync(‘Pick a record’, s.tableSource);

// Gets the desired # of records to create, and deliverable names, based on the fields chosen in the script settings
let recToCreate = s.delivField.options.choices.length;
let deliverables = r.getCellValue(s.delivField.name);

// Variables to store the applicable deliverable names and length (total records to create)
let delivNames = ;
let length = 0;

if (deliverables) {
// Creates record names from ‘deliverables’ multiple select field, if any
for (let item of deliverables) {
delivNames.push({
‘Name’: item.name
})
}
length = delivNames.length
// Preview records to create, prompt user to confirm creation
output.markdown(’### Create ’ + length + ’ records for ’ + r.name + '?’);
output.table(delivNames);
await input.buttonsAsync(’’, [{ label: ‘Create records’, value: ‘Create records’, variant: ‘primary’ }]);
// Create records
let dToCreate = ;
for (let i = 0; i < length; i++) {
let name = delivNames[i].Name
dToCreate.push({
fields: {
[s.destinationField.id]: name,
[s.projField.id]: [{ id: r.id }]
}
})
};
// Batches the creation
while (dToCreate.length > 0) {
await s.tableDest.createRecordsAsync(dToCreate.slice(0, 50));
dToCreate = dToCreate.slice(50);
}
// Output confirmation for the user
output.markdown( ${length} records created );
} else {
output.markdown(**No deliverables chosen for this record. Make selections in the ${s.delivField.name} field first.**)
};

16 Replies 16
Alex_Wolfe1
Airtable Employee
Airtable Employee

Hi Hillary! Welcome to the community :slightly_smiling_face: I’m with our support team, and wrote that script, so I’m happy to help.

I replied to your email that came into us at support, so please feel free to reply there! Otherwise, let me know if you have any questions.

Hey Alex,

I just wrote into support, but when copying the sample base the script appears to be the generic Hello World script. Do you have the source of this script I could use? When attempting to implement the script above, I am running into some formatting issues…

Thanks!

Hey Brandon, sorry about that! While I look into why that happened with the Universe base, it looks like Erin shared that the script is also available in our Marketplace. Hope that’s helped for now, and I’ll work on checking into the Universe base. Thanks for letting us know!

Brandon_Jones
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks Alex! You and Erin were super fast on this. Works like a charm, we’ll see how things go with me enhancing this to fit our needs :slightly_smiling_face:

Hi Alex, I’m also trying to run this script. I’m not coder, so please bear with me. This is for a user research base. I’m trying to copy every record in a multi-select field all at once (there are 264 records) from one table to another table. This secondary table will become the master table for this data, will be synced to all bases, and used as a linked table (linked record field).

Hi Britt :wave: Happy to help if I can — would you mind clarifying what you’re running into or where you’re looking to adapt the script? That’d help me point you in the right direction or give any recommendations.

Feel free to DM me as well and we can discuss 1:1 outside of the thread if you’d like~

Hi Alex,

I’m not sure what part I need to modify.

Original message:

“I’m trying to copy every record in a multi-select field all at once (there are 264 records) from one table to another table. This secondary table will become the master table for this data, will be synced to all bases, and used as a linked table (linked record field).”

My use case:

I have a multi select field in one table with 264 options to chose from and I want the script to take all of those options at one time rather than one by one and make them records in a secondary table.

I will then sync the secondary table with all the other bases so i can have a master list of the multi select options for all my bases. I’d be happy to screenshare or whatever is needed.

Hope this helps,

Britt

Ah okay got it, thanks for clarifying that.

At the moment, the marketplace script I wrote wasn’t built to do this, so it would take some custom code to get that working, but if you have someone on your team with some coding knowledge they could certainly build that with the scripting app’s capabilities — or better yet, I’d suggest creating a post in our community page dedicated for Scripting to ask the community experts for help: Scripting app - Airtable Community Forum

Hi,
I’m not sure whether I understand your task well, it seems like simple way to do it, without script.
Create new field in your table, ‘linked to another record’, point to secondary table (suppose it’s empty)
Copy-paste whole multiselect column into it

I have not found a way to copy from the Multi select field dropdown. That was the first solution that I tried.

Maybe I’m still misunderstand. Ok, I will try to explain with demo. Imagine you have table Users with some events (muliselect field LinkEv)

image

Create new table Events (remove default 3 records & 3 fields),
In table Users, create link field to it and copy-paste

image

image
image

result will be every single multiselect option in primary field of Events table.

image

===========

Note: if you need just a list of options, you don’t need second table and you can do the task in 15 seconds
Just duplicate, then turn to text, then again to multiselect, but don’t press Save, copy-paste

image

image

I hope it will help you.

Thanks Alexey_Gusev ! I will try this solution.

When I just started, I had some troubles without understanding of basic thing - how copy-paste works for linked field.
When you copy-paste something into linked field, Airtable iterates all possible values to link (ignoring “Limit record selection to a view”). and choose first to match
If it found nothing, it will create new record and link to it.
comma separated text considered as several values. symbol ‘&’ also means something, it should be taken into account when working with such data

I appreciate your help so far. :pray:

I am trying to make a list of all the options from the Findings column. (see attached)

I tried your suggested method:

  1. duplicating the column

    • But when I changed the field type to Text (I had to choose long text because my choices are sentences) it did not list them out

See your version vs. what it showed me. Can you help me troubleshoot?

image.png

My Version.png

Ariella_Faitels
4 - Data Explorer
4 - Data Explorer

Hi! Running into an issue with this script and not sure what i’m doing wrong…
Screen Shot 2022-03-16 at 6.06.25 PM

But when I changed the field type to Text it did not list them out

Correct, it will just change it to text, when you press Save.
Then try to change this text field and choose multiselect and you will get the list.
It’s the “side feature” of converting text to select.
Note that you can also check duplicates in such way, because values in this list sorted by frequency in decreasing order.