Help

Re: Creating new rows out of a multi-select cell

1598 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jeremy_Brunet
6 - Interface Innovator
6 - Interface Innovator

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!

11 Replies 11
Jeremy_Brunet
6 - Interface Innovator
6 - Interface Innovator

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.

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

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
6 - Interface Innovator
6 - Interface Innovator

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.

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
6 - Interface Innovator
6 - Interface Innovator

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

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
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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

Zapier Step 3.png

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

Jeremy_Brunet
6 - Interface Innovator
6 - Interface Innovator

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:

No worries - glad it’s working!