Creating new rows out of a multi-select cell

[Jeremy_Brunet]

Hello Airtable community!

I have a table A where my column “Items” is type multi-select.
Inside each cell of “Items”, I store several items.
Every row in table A has a unique ID in the primary field.

How can I create new rows in a table B out of each item of my table A’s “Items” column?

I’ve dug a forum post called “Formula to separate concatenated items?” and especially @Julian_Kirkness first answer about the Zapier Javascript Code action but I didn’t find out how to automate this.

Thanks for your help!

[Jeremy_Brunet]

Hi @Julian_Kirkness

Thanks for your quick answer!

My database is quite simple: I record the contracts I’ve dealt, each contract has a unique ID and contains several tasks to do for my customer (alongside many other informations such as the customer name, its company details, etc).

Example:
Contract ID #001 contains 3 services : “pick-up mail”, “write blog posts” and “create Adwords campaign”.
Contract ID #002 contains 2 services: “PR management” and “write blog posts”.

Today, I’m pushing these services in my table A with Zapier.
Every service I sell is stored in my CRM (Copper) and when I create a new opportunity, it reports it in Airtable (see Airtable as an handy dashboard here). The unique contract ID created in Airtable is Copper’s opportunity ID and the services comes from a required multi-select field inside each opportunity.

I’m not sure how I can split my multi-select cells by creating new tables.
When I lookup to the contract ID #001, Airtable copies & pastes in plain text “pick-up mail, write blog posts, create Adwords campaign”. I just make a string out of three options.

I continue digging the options available in Airtable but I think if there’s a workaround, Zapier might play a leading role.

[Julian_Kirkness]

Ah…

You may be able to use Zapier to take the services from Copper and create a new attached record for each one in a Tasks table (so Contract 1 would have 3 records linked with it). To split up the 3 options in Zapier would probably require some javascript - there are some similar examples on my blog:

Database Resources

Zapier | Learn more about Cloud Databases

Ideas and reviews of Cloud Database platforms

Or, you could try using Integromat which has better facilities for looping through arrays (and other logic).

[Jeremy_Brunet]

Alright, let me dig your post and Integromat, sounds promising, and I’ll post updates asap.

Thanks again for your help! :winking_face:

[Jeremy_Brunet]

I had a look on your links, thanks for your input @Julian_Kirkness

Integromat looks promising but I need more time to understand how it works, I’m way more familiar with Zapier. I’ve tried the iterator tool, I’ve managed to create 2 rows (but not how to store one service on each row :man_facepalming: ), clearly something missing in Zapier…

I’ve tried to code some Javascript snippets to loop through my array of services (the step prior to the code is a formatter to return line-items) but I theorically output an array but when running, the zap can’t push the first item of the array in a first line, the second in the next line and so on.

Have you done a zap like that? I think that’s all I need to fix my problem.

[Julian_Kirkness]

Here is a code snippet from one of my blogposts which turns a comma separated string into an array of objects:

//this code is required to turn the string containing comma separated data into
//an array of objects. Because the output is an array of objects the following
//steps will run for each record found.

if (inputData.csString == null) {
var listArray = ;
} else {
var listArray = inputData.csString.split(",");
}
var output = ;
var arrayNos = listArray.length;
var i=0;
do {
var thisItem = new String(listArray[i]);
var thisItemObj = {};
thisItemObj.record = thisItem;
output.push({thisItemObj});
i++;
}
while (i < arrayNos);

As is says in the comments, outputting an array of objects in this way causes the following Zap steps to run for each item in the array - so you simply have a step which creates a record and it will run for each array item.

Note - this site has interpreted opining and closing square brackets as a !!!

[Jeremy_Brunet]

Thanks @Julian_Kirkness for the snippet!

I’ve just tried it, the first item has been pushed in a new row in Airtable, not the next one.
I keep working on it, this is clearly heading in the right direction, if you think about any mistake I could have made in the process, I’m all ears.

My zap:

1. Copper: Update Opportunity Stage
2. Filter (continue if opportunity stage is “customer”)
3. Run Javascript code
4. Airtable: Create record

[Julian_Kirkness]

mmm…

Sounds like your code isn’t returning an array of objects - you could copy it into here - along with an example of the data it’s getting and a screenshot of the Code step showing the data input part.

These days I always use Integromat for this type of think (no code needed) - it’s a while since I wrote those JS / Zapier examples!

One thing you could use as a tool is to write some console.log() statements into your code and open the browser console and see what they contain. You can output different values from steps in the process.

[Jeremy_Brunet]

Yes I do use console.log to track what’s going on, here they are:

Top of the code to check the input (2 items here)

inputData.csString = Item1,Item2

First Loop

thisItem = Item1
thisItemObj = [object Object]
output = [object Object]

Second Loop

thisItem = Item2
thisItemObj = [object Object]
output = [object Object],[object Object]

What Zapier outputs

thisItemObj:
record: Item1

I’m trying to build a zap similar to yours on your blog with this workflow:

1. Airtable: New Record
2. Run Javascript
3. Airtable: Find Record

And finish with 4. Airtable: Create Record

I’m not closed to the idea of using Integromat if it’s easier, it’s just I don’t get it yet! :grinning_face_with_sweat:

[Jeremy_Brunet]

Here’s the output of the Run Javascript step (I can only post one image/post, I can screenshot more steps if needed):

“Envoyer bulletin…” is my “item1”, “item2” is missing here.

I’ve formatted in pretty much everything before the Code step - string, line-item, separate fields, you name it - it didn’t help me out so far.