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?
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?
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:
@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?
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.
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: