Jun 16, 2021 12:32 PM
New to airTable and I love it. In setting up a database, I am running into a problem. Links from a long text string have to be replaced with new links. Is there a formula that an do this or does this have to be done manually?
Here’s the text:
<h4>UNIT OVERVIEW</h4><p><a href="https://apostolicfaithchurch.box.com/s/q77dw02m7v5mu11fg301ixmtbni56ebb">Men Who Trusted God</a><br></p><h4>MEMORY VERSE IN ACTION</h4><p>WHOSO — Point to others.</p><p>PUTTETH HIS TRUST — At eye-level, hold fists on top of each other as though holding to an imaginary rope.</p><p>IN THE LORD — Make an L with thumb and first finger of the right hand. Touch thumb to left shoulder, bring across in front of body touching right waist.</p><p>SHALL BE SAFE — wipe sweat from forehead as though just having a close call with danger.<br></p><h4>MEMORY VERSE VISUALIZED</h4><p>Though our memory verse for this week is found in Proverbs, it ties in closely with our story text about Noah and the ark. The visualization for this verse is the ark. (See pattern at the end of this lesson.) Cut out each piece and assemble in the correct order as your students learn the verse.</p><h4>MEMORY VERSE SONG</h4><ul><li><a href="https://apostolicfaithchurch.box.com/s/9gynfedpkg86inoiztrd7qjzop981ef9">Trust In The Lord</a></li></ul><h4>COORDINATING SONGS</h4><ul><li><strong>"Brother Noah" </strong>— tune of "Old McDonald"</li></ul><h4>MEMORY VERSE PATTERN</h4><ul><li><a href="https://apostolicfaithchurch.box.com/s/vc5pj6c1jp77hcj9bbqkxj40thpvyko0">Proverbs 29:25</a><br></li></ul><h4>PATTERNS AND ACTIVITIES</h4><ul><li><a href="https://apostolicfaithchurch.box.com/s/5pemga35jz6sk7ls4g17kwqu9pprt9ci">Unit 24 Patterns</a></li></ul><h4>SUPPORT MATERIAL</h4>
And these are the new links:
MEMORY VERSE SONG
PATTERNS AND ACTIVITIES
MEMORY VERSE PATTERNS
Can this be done with a formula? Do the PDFs need to be brought into AirTable?
Thank you in advance.
Jun 16, 2021 01:23 PM
I am not certain this could be done using one or more formulas. Is this something that must be done for every or several records in a table? Is there any kind of consistency to how many links are in the text field, and what those links need to be replaced with?
Jun 16, 2021 02:23 PM
@Kamille_Parks Yes, each table has approximately 200+ records. Each record will have the same amount of links that have to be changed. And those url will always be after those headings.
Jun 16, 2021 03:56 PM
It helps that there’s some level of consistency, but in my first few attempts at a formula-based solution I wasn’t able to reliably extract out the urls then replace them with the ones you need.
Jun 16, 2021 04:08 PM
Jun 17, 2021 05:43 AM
Welcome to the community, @rmm!
You can very easily do this with Integromat, which is a low-code/no-code automation & integration platform that has full Airtable support.
Integromat has a text parsing module that lets you extract an unlimited number of links from HTML code, and it will return them all to you as separate elements to do whatever you’d like to do with them:
Jun 17, 2021 12:13 PM
@ScottWorld Thanks Scott. I tried setting it up without success. A true no-coder here. Any tips on how to set it up?
Jun 17, 2021 12:34 PM
Welcome to the Airtable community!
Do you have a way of automatically knowing what the new link should be from the old link? Do you have a table that maps the old links to the new links?
Is this an ongoing conversion process or a one-time process?
How do you feel about having a script to perform the conversion? Do you have budget to hire someone to write a script?
Jun 17, 2021 12:50 PM
@kuovonne Thank you! We could create columns for each in that same table and bring them into the appropriate row, though that in itself is quite a bit of work. Currently, those files are saved in Oracle and would require going into each record and copying its link.
This is a one-time process. I would be interested in a script. What would be the setup needed for it to work?
Jun 17, 2021 07:42 PM
A script can search for a long text field for urls and replace them with other urls. However, the script needs to know what the new urls should be. For example, you can provide a list of urls for the script to replace in matching order. Or you can provide a table of matching urls and the script will lookup the new url from the table based on the original url. It really depends on the current structure of your data.
Jun 18, 2021 05:22 AM
There are an unlimited number of ways to set this up in Integromat, depending on your needs & how your database is setup.
Unfortunately, this is not something that I would be able to help you out with for free, because it is time intensive. If you have a budget for this project and you’d like to hire an expert Airtable consultant & Integromat Partner to help you create this, please feel free to contact me through my website at ScottWorld.com: