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.

[Justin_Barrett]

jord8on:

I can’t seem to figure out how to adjust the formula to get it to work for my fields where I have several images because the amount of raw/expanded text is not consistent. :disappointed:

The formula system I posted in that other thread should do the trick. It’s designed to work with text chunks of varying sizes.

What’s the maximum number of images you’ll have in the attachment field?

[jord8on]

I’ve been working to apply your logic from that formula and expand it but i have some image files with short names and others with long, with makes the formula mess up a bit. I’ve tried to tweak the parameters of the various parts of the formula, based on your explanation but can’t quite seem to get it right.

I don’t think we’ll ever have more than 12 images. Most likely we’ll never have more than 9, if that makes the formula easier.

I wish I was better with formulas but I had a thought that could help with this conversation, I just have no idea how to write a formula like this… How difficult would it be to create a formula that…

• Search for http
• Search for a period with three letters after if (i.e. png, jpg - though this could get tricky if the image was “jpeg” but I wonder if the formula could allow you to set an array of standard parameters like… jpg, png, tiff, pdf, gif, xlsx, jpeg, indd, etc.)
• Grab everything between the http and the last characters of file extension name… and put some spacing in between that and the next URL
• Then repeat and keep searching for the next image, until there are no more.
• Then put all the url permalinks together in CSV format

Is a formula like that too complex to even consider? Perhaps there are flaws as well?

Based on this conversation and the existing formulas, here’s what I’ve done…

I made a view so you could see what I’ve done with the formulas, and it works for som and not for other fields…

Airtable

Grid 15 - Airtable

Explore the "Grid 15" view on Airtable.

“Expanded” field is:

IF(
Product_Images,
((LEN(Product_Images) - LEN(SUBSTITUTE(Product_Images, “,”, “”))) + 1)
& " - "
& SUBSTITUTE(SUBSTITUTE(Product_Images, “(”, REPT(" “, 100)), “)”, REPT(” ", 100))
)

Formula for “Images separated by commas”:

IF(
expanded,
TRIM(MID(expanded, 100, 200))
& IF(LEFT(expanded, 2) > 1, ", " & TRIM(MID(expanded, 400, 200)))
& IF(LEFT(expanded, 2) > 2, ", " & TRIM(MID(expanded, 700, 200)))
& IF(LEFT(expanded, 2) > 3, ", " & TRIM(MID(expanded, 1000, 200)))
& IF(LEFT(expanded, 2) > 4, ", " & TRIM(MID(expanded, 1300, 200)))
& IF(LEFT(expanded, 2) > 5, ", " & TRIM(MID(expanded, 1600, 200)))
& IF(LEFT(expanded, 2) > 6, ", " & TRIM(MID(expanded, 1900, 200)))
& IF(LEFT(expanded, 2) > 7, ", " & TRIM(MID(expanded, 2200, 200)))
& IF(LEFT(expanded, 2) > 8, ", " & TRIM(MID(expanded, 2500, 200)))
& IF(LEFT(expanded, 2) > 9, ", " & TRIM(MID(expanded, 2800, 200)))
& IF(LEFT(expanded, 2) > 10, ", " & TRIM(MID(expanded, 3100, 200)))
& IF(LEFT(expanded, 2) > 11, ", " & TRIM(MID(expanded, 3400, 200)))
& IF(LEFT(expanded, 2) > 12, ", " & TRIM(MID(expanded, 3700, 200)))
)

I just can’t get this to work for me, as you can see in the table link i referenced above :disappointed:

[jord8on]

@Miquel_Capo_Casasnov, Thanks for sharing this great tool!

I tried your script and it was almost flawless!! But unfortunately, it didn’t work on a few of our records. (the only thing I modified on the tool you shared, was the “Product_Images” column where the images live.)

It looks like the script didn’t work for records 82-98 as you can see, here.

Here’s the formula I entered for the column I titled Miquel’s Formula…

IF( find(',', Product_Images) = 0, MID( Product_Images , FIND( '(https://' , Product_Images, if( find(',', Product_Images ) > 0, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 0)), 1 ) ) +1+0 , FIND(')', Product_Images , if( find(',', Product_Images ) > 0, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 0)), 1 ) )-2 - FIND( '(https://' , Product_Images, if( find(',', Product_Images ) > 0, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 0)), 1 ) ) +1+0), MID( Product_Images , FIND( '(https://' , Product_Images, if( find(',', Product_Images ) > 0, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 0)), 1 ) ) +1+0 , FIND(')', Product_Images , if( find(',', Product_Images ) > 0, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 0)), 1 ) )-2 - FIND( '(https://' , Product_Images, if( find(',', Product_Images ) > 0, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 0)), 1 ) ) +1+0) ) & '' & IF(find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 1)) > 0, ', ' & MID( Product_Images , FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 1)) ) +1+0 , FIND(')', Product_Images , find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 1)) )-2 - FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 1)) ) +1+0) , '') & '' & IF(find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 2)) > 0, ', ' & MID( Product_Images , FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 2)) ) +1+0 , FIND(')', Product_Images , find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 2)) )-2 - FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 2)) ) +1+0) , '') & '' & IF(find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 3)) > 0, ', ' & MID( Product_Images , FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 3)) ) +1+0 , FIND(')', Product_Images , find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 3)) )-2 - FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 3)) ) +1+0) , '') & '' & IF(find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 4)) > 0, ', ' & MID( Product_Images , FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 4)) ) +1+0 , FIND(')', Product_Images , find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 4)) )-2 - FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 4)) ) +1+0) , '') & '' & IF(find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 5)) > 0, ', ' & MID( Product_Images , FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 5)) ) +1+0 , FIND(')', Product_Images , find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 5)) )-2 - FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 5)) ) +1+0) , '') & '' & IF(find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 6)) > 0, ', ' & MID( Product_Images , FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 6)) ) +1+0 , FIND(')', Product_Images , find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 6)) )-2 - FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 6)) ) +1+0) , '') & '' & IF(find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 7)) > 0, ', ' & MID( Product_Images , FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 7)) ) +1+0 , FIND(')', Product_Images , find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 7)) )-2 - FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 7)) ) +1+0) , '') & '' & IF(find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 8)) > 0, ', ' & MID( Product_Images , FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 8)) ) +1+0 , FIND(')', Product_Images , find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 8)) )-2 - FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 8)) ) +1+0) , '') & '' & IF(find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 9)) > 0, ', ' & MID( Product_Images , FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 9)) ) +1+0 , FIND(')', Product_Images , find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 9)) )-2 - FIND( '(https://' , Product_Images, find('{{anchor}}', SUBSTITUTE(Product_Images, ',', '{{anchor}}', 9)) ) +1+0) , '')

[Justin_Barrett]

jord8on:

I don’t think we’ll ever have more than 12 images. Most likely we’ll never have more than 9, if that makes the formula easier.

Eh…maybe, but with that many images to parse, even the formula trick I presented could potentially fall apart, as you’ve seen. Frankly, I’d be far more inclined to hand this over to an automation script, which can do the parsing far easier than Airtable’s formula fields. If your base is in a Pro plan workspace, let me know and I’ll put a script together. I could knock this out in just a couple minutes.

[jord8on]

Hey @Justin_Barrett, I think we could keep the script below 8 images, if that would make things easier, then manually add additional images via our CMS if needed. Unfortunately, I’m not on the Pro plan right now. :disappointed:

I was just thinking of the worst case scenario with 12 images. Most products will only have 2-4 images but there are somewhere we are going to be adding extra images for different angles, etc.

Did you see this script from @Miquel_Capo_Casasnov, earlier in the thread?

Miquel_Capo_Casasnov:

I’ve created a script in order to achieve this for multiple images too.
https://salwebs.com/scripts/airtable-url-generator/airtable-url-generator.html

That script worked well but seems to struggle on some of the images and I can’t figure out why. length of the filename was one of my guesses but idk how to determine the issue.

[Justin_Barrett]

jord8on:

Did you see this script from @Miquel_Capo_Casasnov, earlier in the thread?

No, I somehow missed that when it came through in August. It looks interesting, but it’s complex enough that I don’t have time to disassemble it to figure out why it might not be working for your scenario. When formulas start looking that hairy, I start looking for alternate options. :slightly_smiling_face:

In lieu of using an automation, the next best option that I can offer requires a bit more work, but is far more reliable in the end. Long story short, you start with the same {Expanded} formula field as described in the other thread. From there, instead of writing one formula that tries to extract all URLS, you add one formula field to extract each URL you want, with each one referring to the one before it as a reference. From there, you add a final formula field that combines all of the extracted URLs into a single comma-separated string. You end up with a lot of hidden formulas as a result, but it makes the extraction much easier.

I think I wrote up a post on this setup (or something similar) a while ago, though I have no idea how to find it. I don’t have time today to revisit it, but I’ll try to put something together tomorrow.

[Justin_Barrett]

Here’s the rundown on this multi-formula setup.

First off, modify the formula in the {Expanded} field, changing both of the 100s to 500. You’ve got really long URLs, so this just provides extra insurance that each one can be grabbed without any issues.

My first formula field is named “URL1”, and extracts the URL of the first image:

IF(Expanded, TRIM(MID(Expanded, FIND("https", Expanded), 500)))

The next field is named “URL2”, and extracts the URL of the second image, using the position of the first one as reference:

IF(AND(Expanded, LEFT(Expanded, 2) > 1), TRIM(MID(Expanded, FIND("https", Expanded, FIND(URL1, Expanded) + 1), 500)))

“URL3” looks very similar:

IF(AND(Expanded, LEFT(Expanded, 2) > 2), TRIM(MID(Expanded, FIND("https", Expanded, FIND(URL2, Expanded) + 1), 500)))

This pattern is repeated for as many URLs as you want to extract. Each extraction formula uses the one before it as reference, and also looks for a higher number at the front of {Expanded}.

Finally you have a formula field that packs the results together. If you make ten URL extraction formulas, then the final assembly formula would look like this:

IF(
    Expanded, URL1
    & IF(URL2, "," & URL2)
    & IF(URL3, "," & URL3)
    & IF(URL4, "," & URL4)
    & IF(URL5, "," & URL5)
    & IF(URL6, "," & URL6)
    & IF(URL7, "," & URL7)
    & IF(URL8, "," & URL8)
    & IF(URL9, "," & URL9)
    & IF(URL10, "," & URL10)
)

[Miquel_Capo_Cas]

Hi @jord8on, this error is not about the length of the image or url, is because the image name has parenthesis. The formula detects the parenthesis to extract the url.

Remove the parenthesis and should work properly.

[Miquel_Capo_Cas]

@jord8on Here another script. This one finds strings between strings. So you can specify the strings between you want to catch the url and then adjust the offsets.

this solves the problem of the urls with parenthesis inside the name.

This links has url params with the config you need

Salwebs

Diseño web y Marketing Online en Menorca | Salwebs

Diseño web, programación y marketing online en Menorca. Webs hechas a medida fácilmente modificables. Servicios de marketing online para empezar a atraer tráfico a tu nueva web

[jord8on]

Brilliant work @Justin_Barrett! I love how clean this is. And thank you for for taking the time to explain the LOGIC!! Your feedback was so helpful.

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.

I’m going to also try this revised formula from Miquel to see if that does the trick, with less fields.

Thanks again for your advice here, Justin! You’re an airtable wizard! I’ve found answers to lots of questions, from you, in other threads as well!