Creating new rows out of a multi-select cell


#1

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!


#2

This post was flagged by the community and is temporarily hidden.


#3

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.


#4

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:

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


#5

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

Thanks again for your help! :wink:


#6

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.


#7

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 !!!


#8

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

#9

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.


#10

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! :sweat_smile:


#11

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.


#12

IT WORKS!

Everything was working perfectly, it’s just that I didn’t know it until I turned on my zap…
Zapier’s testing environment doesn’t simulate loops :man_facepalming:

Sorry for this lengthy and annoying thread @Julian_Kirkness, your help was precious! :pray:


#13

No worries - glad it’s working!