Dynamic Text/Macros In Multi-Line/Rich Text/Mark-Down Fields

Feature Requests
1

I know you can do SUBSTITUTE() but I’m trying to create an email template system that we can quickly do ##CampaignName## or {CampaignName} or similar and have it grab that field value out of the current record when displaying the field. I want to be able to do this for any field name though without having to create a chained SUBSTITUTE formula.

This could be implemented as a formula like a MACROS(FieldToReplace) and have it auto replace anything between { } or # # with the field value. Or is this already possible with a regex and I just don’t know the proper syntax to dynamically replace any/all fields

Example:

Hi #FirstName#,

Here is your #CampaignName#, the cost is #Cost#

I know you can do this in an email workflow/automation, but I want to store multiple templates in a table and be able to select which one to use when sending an email and be able to preview it before sending.

2

Firstly, for both of the suggestions I’m going to present, please be wary of creating more and more fields in the root table for more variable substitutions. If this is truly dynamic, link your table to a second table that stores all of your key: value pairs of substitutions.

For replacing them, you have two options:

Run a script in Airtable.
Easily the most robust option.
Re-run it every time the content changes. Unlimited dynamic variables, pretty much instant updates..

–

Big formula rollup shenanigans
The other option is to be silly.
Rollup each record’s key: value pairs into a big string:

[key1:value][key2:value]

Now write a big formula that dynamically subtracts each of them in your content:

SUBSTITUTE(
SUBSTITUTE(
{Content}
, {FormulaToGetKey1fromList}, {FormulaToGetValue1fromList})
, {FormulaToGetKey2fromList}, {FormulaToGetValue2fromList})

etc.

You’re hardcoding the total number of substitutions here. The only upside is instant updates.

Choose wisely!

3

image
image2046×582 59.9 KB

4 
let { variableRecords, recordID, sectionNumber } = input.config();

let contractSectionsTable = base.getTable("Contract sections");
let record = await contractSectionsTable.selectRecordAsync(recordID);

// Pull the raw content as a string with formatting preserved
let rawContent = record.getCellValue("Section content");

let variablesTable = base.getTable("Contract variables");
let query = await variablesTable.selectRecordsAsync({
  fields: ["[ Key ]", "Value in contract"]
});

// Filter by record IDs you passed in
let variables = query.records.filter(record =>
  variableRecords.includes(record.id)
);

console.log("Raw content:");
console.log(rawContent);

// Start with the raw content
let finalText = rawContent;

// 1. Replace variable keys with their values
for (let variable of variables) {
  let key = variable.getCellValue("[ Key ]");
  let value = variable.getCellValue("Value in contract");

  if (key && value) {
    // Escape special regex characters in the key
    let escapedKey = key.replace(/[-\/\\^$*+?.()|[\]{}]/g, '\\$&');
    let pattern = new RegExp(escapedKey, 'g');

    console.log(`Replacing: ${escapedKey} -> ${value}`);
    finalText = finalText.replace(pattern, value);
  }
}

// 2. Replace all [X] with sectionNumber
if (sectionNumber) {
  finalText = finalText.replace(/\[X\]/g, sectionNumber);
  console.log(`Replaced [X] with section number: ${sectionNumber}`);
}

console.log("Final text:");
console.log(finalText);

// Update the record with the final version
await contractSectionsTable.updateRecordAsync(recordID, {
  "Section content final": finalText
});