Formula to Separate Concatenated Items?

[Arlo_Haskell]

Does anyone know a way to separate items from a concatenated string?

I have a table that I’ve used to make a schedule of events for a conference. Each presenter makes multiple appearances. Using Zapier and WebMerge I plan to produce a print-ready manifest for each presenter that will show their personalized schedule, along with hotel, flight, and contact information from other tables.

Problem is, when I look up the start time of their presentations I get ALL the start times in a concatenated string like this:

1/13/2017 10:35am, 1/14/2017 9:40am, 1/14/2017 12:20pm

I’d like a formula that basically says “find and display first item from string”, “find and display second item from string” etc… Then I could create fields for “Start Time 1,” “Start Time 2”, match them up with corresponding End Time fields, and get it over to WebMerge and in print.

Many many thanks to anyone who can help point me in the right direction here. :eyeglasses:

[Julian_Kirkness]

There is a technique for doing this which involves using Javascript Zapier step to convert the string into first an array and, then, an array of objects and returning this from the step.

The clever thing about this is that the Zapier steps following the Javascript Step (returning an array of objects) runs for EACH of the objects in the array.

You may be better looking up a field which holds the IDs of the individual presentation records so that you can then use these to search for the records in a Find step and get the other data from the records that you need for the WebMerge.

There is one other thing you need to watch out for which is that Zapier will only loop through 25 objects returned from a Code Step in this way - but there is a workaround if this is an issue by splitting the original array up before turning it into an array of objects - sounds complicated but it’s not that bad.

I discovered this technique doing some work for a client and could either post some sample code if you have a little Javascript or get in touch directly if you would like some help.

Julian

[Arlo_Haskell]

Thanks so much @Julian_Kirkness. I don’t have any Javascript experience, so this might be too much of a challenge to take on in the timeframe I have, but I’m going to study it a bit more and will message you.

Also, FYI, Matt from Airtable suggested this can be done manually using string functions such as FIND to determine the index of a separator and MID to extract a substring before or after a particular separator.

[Julian_Kirkness]

Hi Arlo

You could definitely split up the values inside Airtable - but I assume you need all the values (an unknown number of them presumably) in Zapier for merging?

The one aspect I haven’t looked at is how this would be sent to WebMerge using my technique - in my use of this so far I have been creating multiple Airtable records (in another table) when certain criteria are met in the triggering table. I haven’t used WebMerge ‘in anger’ yet although I have played a little.

I will contact you directly.

[Julian_Kirkness]

Hi Arlo

I have done a little bit of research into this and I think I have a suggestion. This involves creating a JSON string using formula fields in Airtable containing key/value pairs to populate a table in WebMerge. Essentially, you would create a JSON calculated field in each presentation record and then use lookup / rollup and formula fields to format a JSON string in the presenters record with all the information from their bookings. This can be sent to WebMerge by Zapier along with other information as required. Not that simple but no code is required to do this.

Here is a simple table in WebMerge which shows an example:

and here is the test part of the Zap which shows the format of the JSON which is sent to populate it:

This example relates to producing a ‘statement’ detailing time spent on a project.

There is some documentation on this on the WebMerge site. I believe that sending JSON like this is the only way to get WebMerge to produce a table of results (which I assume you would want rather than a separate document for each presentation?).

[Arlo_Haskell]

Just a follow-up here. I was able to achieve my objective by taking a different approach. Instead of exporting each individual field (start time, end time, presenter, presentation title) to WebMerge, I used a formula to concatenate these fields in a single string, with punctuation to make it “friendly”; then I used a rollup to specify a separator between record-strings (" | " in my case). Then finally I sent it to WebMerge and used “explode” code on the specified separator (" | ") to display each string on its own line; also applied text formatting (bold for headline, italic for note, etc).

It took a lot of testing to get it right, but now it works like a dream. Using Zapier as the intermediary, I’ve set it up so just by typing “manifest” in an Airtable field, I can get a formatted document from WebMerge on our letterhead that contains all the info we need for our presenters: full contact details, hotel accommodations, flight itinerary, and on-stage assignments. This gets saved in a Dropbox folder that my whole team has access to, so everyone can get all this info on a single formatted sheet at a moment’s notice.

When I have more time, I’ll try and post on this in “show and tell.” Definitely my biggest Airtable success story.

BTW, below is the code I used in Webmerge (where “manifests_sched” is the field I’m getting from Airtable that contains the string with " | " separators)

Put this in the Field Map:
{json_encode(explode(";", $manifests_sched))}

And this in your document:
{foreach from=$manifests_sched item=_row}{$_m = explode("|", $_row)}
{$_m.0}
{$_m.1}
{$_m.2}
{/foreach}

[Albert_Chen]

Hi Julian,

Thanks for posting this. This is exactly what I need to do, but I can’t seem to manipulate a field to get it to output data in JSON array format. Can you let me know how you did it please?

[Julian_Kirkness]

Hi Albert

Essentially, I just used Concatenate() to build the JSON String:

Because this particular case then involved using a rollup to get all of these values for a particular client / project I then used Substitute to format the multiple values correctly:

I hope this helps!

Julian

[Albert_Chen]

This is amazing! Thank you for a quick response.

[Annie_Wang]

Hi! I’m trying to something similar. I have a string of URLs that I would like to separate so they are their own column in Airtable.

Ie
Www.test1com, www.test2com, www.test3com

To become three separate columns in Airtable?

Can anyone help? @Julian_Kirkness @Arlo_Haskell