Extracting single image/attachment URL

[W_Vann_Hall]

Admittedly, an edge case, but in working with the Airpress WordPress-to-Airtable API and plugin, I ran into problems when attempting to display only the first image from an attachment field. After hours spent stepping my way through the plugin, I finally discovered I’d been debugging the wrong module entirely. Discouraged, I took a break, fed the cat, got another cup of coffee – and then wondered if I might not be able to goose Airtable into giving me the data directly.

Five minutes later, I had it.

The example assumes an Attachment field named ‘images’, but should work with attachments of any type.

To extract the URL

MID(images,FIND("(",images)+1,(FIND(")",images)-1)-(FIND("(",images)))

To extract the file name

LEFT(images,FIND(" ",images)-1)

This works because Airtable will cast an Attachment field to a comma-delimited string of values when it is acted upon by a text function. Each attachment value consists of two parts, separated by a space: the original file name (sans path) and the Airtable URL, enclosed in parentheses – as so:

FileName1 (URL1),FileName2 (URL2),[...],FileNameX] (URL[x])

Again, probably not of interest for most Airtable users – but if I’d thought about it earlier, I could have saved myself a day’s effort.

[jord8on]

This looks great @Miquel_Capo_Casasnov!! Curious if additional suffix filenames could be added… e.g. could you have the “End Field” allow .jpg OR .png OR .gif OR .pdf OR .webp etc.

We have a variety of image/file types that we are putting in this field and would like to define an array of possible suffixes.

[Miquel_Capo_Cas]

You can simply remove parenthesis from image files. And then use the first script or use the second removing “.jpg” from the end string “.jpg)” to leave it like this “)” so it will take the parenthesis as end string and any format will be working.

[Justin_Barrett]

jord8on:

The only downside I can see with this formula here is that I would need an extra field for each additional image. Most records in our db have only 1-3 images, while there are a dozen or more with several additional images. So this would force us to create a lot of different fields, with empty cells.

Yup, that’s the tradeoff that I mentioned for that particular solution. Unless you need to see all of those fields, though, you could just hide them.

The solution from @Miquel_Capo_Casasnov is definitely better if you want a single formula, but its logic puts constraints on how the files are named as he pointed out.

As you’re discovering, virtually any solution to this problem has its pros and cons, including a scripting solution. The big pros with that option are that you wouldn’t need any formula fields, you’re free to edit the final result if needed, and filename formatting wouldn’t matter. The con is that it requires a Pro-plan workspace, which isn’t an option for some.

[Tuur]

Which is why I came up with a version that doesn’t care about parentheses or commas. :winking_face:

Extracting single image/attachment URL Show and tell

For future reference, here’s a version in case filenames contain parentheses or commas for example. It uses the last couple of characters (usually the extension) from the filename (and URL encodes them in case there are non-safe characters in the name). It also starts searching from the beginning of the URL. It’s one ugly @#%&$ but pretty reliable… MID({Image},FIND("(https",{Image})+1,(FIND(ENCODE_URL_COMPONENT(RIGHT(LEFT({Image},FIND('(https://', {Image})-2),4))&')',{Image})+LEN(ENCODE_URL_C…

[Rebecca_Rasmuss]

Thank you for sharing!

[Hannah_Wiginton]

We have this as an easy no-code function in our On2Air Actions app

Extract URL from Attachment

On2Air | Accomplish Anything with Airtable – 8 May 21

On2Air: Actions | On2Air

On2Air: Actions is an Airtable automation and workflow tool to integrate and connect your Airtable data with any other software tools used in your business. Create powerful business processes with almost 20 additional action options beyond the...