Hoping you can help me. Check spoiler tag for completely detailed explanation of my situation, just in case you can spy a base design issue that could easily be fixed:
I have a table called "Products" and a table called "Definitions". I'm linking each product in my products table to the definitions table. The record in definitions is the classification/type of product, i.e., a wall mounted faucet.
In the Definitions table there are internal instructions that our staff need to check off using rich text format before the product is considered ready. It would be instructions like "Wall mounted faucet is not located on an exterior wall".
I'm using a Lookup in my Products table to retrieve these instructions and attach it to products based on the definition. Unfortunately when I use a lookup to retrieve these internal instructions field in the Products table, it loses all rich text format. Fair enough - as per this help article I get that this isn't supported right now: https://support.airtable.com/docs/rich-text-in-formulas-lookups-and-rollups
THEN to complicate things even further, I'm syncing these products to another base entirely. Within this base more information would be added specific to the project we want to use the product in. Within that base, these instructions would have to be checked off on a per product basis before we approve the use of the product. So since the Lookup that is being synced from Products is of course is not editable, I've used an automation to take what's written in the lookup field and paste it as a unique editable rich text format field within each product. Great! Nearly there. But now my problem becomes restoring the rich text format that I lost in that initial lookup and since the table that has the original rich text format instructions is in another base entirely, I'm stuck looking for an end-point solution to convert the raw markdown to rich text format again.
TL;DR, after using a lookup and then using an automation to copy that lookup info into a new editable field, my rich text format came out like this:
[ ] Wall mounted faucets cannot be located directly on an exterior wall. The wall must be built-out minimum 4-1/2" to accommodate the in-wall plumbing requirements.
[ ] Mount wall mounted faucets on backsplash and increase height of backsplash to a minimum of 10".
How can I automatically reformat the above "raw" markdown back to rich text format? Automation doesn't seem to be up to the specificity of this task, but I could be wrong about that.
Is there something I can do with a formula field to format this text a little better so that the rich text format is restored? Is a script the way forward? Seems likely, but unfortunately I don't have the know-how to do it.
As you have noticed, calculated fields that reference rich text fields strip out the markdown formatting. Some of the formatting is converted to text, such as the checkboxes. But other formatting such as bold and italic are completely gone. You cannot get them back.
You can use both automations and scripting to put formatted text into a rich text field. The issue is you need to have access to the actual markdown text before the markdown was stripped out. Formula fields, lookups, rollups, etc. do not have access to the original markdown. You could use scripting to extract the markdown text, and store the markdown in a long text field with plain text. Then pass that plain text around with rollups/sync. Then at the other end, use an automation to copy the markdown in the long text field into a rich text field.
As you have noticed, calculated fields that reference rich text fields strip out the markdown formatting. Some of the formatting is converted to text, such as the checkboxes. But other formatting such as bold and italic are completely gone. You cannot get them back.
You can use both automations and scripting to put formatted text into a rich text field. The issue is you need to have access to the actual markdown text before the markdown was stripped out. Formula fields, lookups, rollups, etc. do not have access to the original markdown. You could use scripting to extract the markdown text, and store the markdown in a long text field with plain text. Then pass that plain text around with rollups/sync. Then at the other end, use an automation to copy the markdown in the long text field into a rich text field.
Thanks for your reply! Unfortunate that the formatting can't really be restored without having access to the original formatted checkbox list.
I think in my case I am going to make a compromise instead of dramatically having to reorganize the structure of my data just to satiate this one small thing. Just going to add a generic checkbox next to my linked record instructions called "instructions have been reviewed" that will need to be checked off by staff before a product is OK'd. Won't be quite as effective as having to consciously check off each item individually in the list but until they can implement rich text format a little deeper into every aspect of Airtable I guess it'll have to do.
