Bespoke Salesforce Formulas

advance_fy.txt

# Generates FY as 2022/23

# CampaignDateTime
IF(
    ISBLANK({!StartDate}) == FALSE, 
    {!StartDate},
    IF(
        ISBLANK({!CreatedAt}) == FALSE, 
        {!CreatedAt},
        {!$Flow.CurrentDateTime}
    )
)

IF(
    AND(DATEVALUE({!CampaignDateTime}) >= DATE(2022,07,01),DATEVALUE({!CampaignDateTime}) <= DATE(2023,06,30)),'2022/23',
    (
        IF((AND(DATEVALUE({!CampaignDateTime}) >= DATE(2023,07,01),DATEVALUE({!CampaignDateTime}) <= DATE(2024,06,30))),'2023/24',
        (
            IF((AND(DATEVALUE({!CampaignDateTime}) >= DATE(2024,07,01),DATEVALUE({!CampaignDateTime}) <= DATE(2025,06,30))),'2024/25',
            (
                IF((AND(DATEVALUE({!CampaignDateTime}) >= DATE(2025,07,01),DATEVALUE({!CampaignDateTime}) <= DATE(2026,06,30))),'2025/26',
                (
                    IF((AND(DATEVALUE({!CampaignDateTime}) >= DATE(2026,07,01),DATEVALUE({!CampaignDateTime}) <= DATE(2027,06,30))),'2026/27',
                    '')
                ))
            ))
        ))
    )
)

DDMMYYY_to_Date.txt

DATE(
    VALUE(RIGHT({!InvoiceDate}, 4)),
    VALUE(
        MID(
            {!InvoiceDate},
            FIND("/", {!InvoiceDate}) + 1,
            FIND("/", {!InvoiceDate}, (FIND("/", {!InvoiceDate}) + 1)) - FIND("/", {!InvoiceDate}) - 1
        )
    ),
    VALUE(LEFT({!InvoiceDate}, FIND("/", {!InvoiceDate}) - 1))
)

end_of_string_by_delimiter.apex

// Gets end of string deliminated by ":"
// "campaign:261812" returns as "261812"
// "campaign-261812" returns as "campaign-261812"

IF(
  FIND(":", {!Key}) == 0,
  {!Key},
  REVERSE(
    LEFT(
      REVERSE({!Key}),
      FIND(":", REVERSE({!Key})) - 1
    )
  )
)

IsContactDonor.txt

({!Action} =="donation" && {!IsActor} == TRUE)

IsContactFundraiser.txt

({!Action} == "metadata") || ({!Action} =="donation" && {!IsActor} == FALSE)

ISO_DateTime_to_Date

// Example - Takes an ISO DateTime (ie. 1984-04-24T12:00:00.000Z) as a String and produce a Date variable.
IF(
    ({!Questions_DateOfBirth} != NULL && FIND("T",{!Questions_DateOfBirth}) > 0),
    DATEVALUE(LEFT({!Questions_DateOfBirth}, FIND("T",{!Questions_DateOfBirth}) - 1)),
    NULL
)

Over_18.apex

// Is over 18.  True = yes / False = no / Null = missing birthdate
IF (
  ({!Record.Birthdate} == NULL), 
  NULL,
  {!Record.Birthdate} <= (ADDMONTHS({!$Flow.CurrentDate}, 0-(12*18)))
)

Proper_Case

# Replaces the first three words with proper casing ... probably should be a built-in function...
# Use: Replace {!Input_Text} with appropriate flow variable.
# Credit: https://developer.salesforce.com/forums/?id=906F0000000927nIAA

IF (
  FIND(" ", {!Input_Text}, 1)=0,
  UPPER(LEFT({!Input_Text}, 1)) & LOWER(MID({!Input_Text},2,LEN({!Input_Text})-1)),
  IF(
    FIND(" ",MID({!Input_Text}, FIND(" ",{!Input_Text}, 1) + 1,LEN({!Input_Text})-FIND(" ",{!Input_Text},1)))=0,
    UPPER(LEFT({!Input_Text}, 1)) & LOWER(MID({!Input_Text}, 2,FIND(" ",{!Input_Text}, 1)-1)) & UPPER(MID({!Input_Text}, FIND(" ",{!Input_Text}, 1) + 1,1)) & LOWER(MID({!Input_Text}, FIND(" ",{!Input_Text}, 1) + 2,LEN({!Input_Text})-1)),
    UPPER(LEFT({!Input_Text}, 1)) & LOWER(MID({!Input_Text}, 2,FIND(" ",{!Input_Text}, 1)-1)) & UPPER(MID({!Input_Text}, FIND(" ",{!Input_Text}, 1) + 1,1)) & LOWER(MID({!Input_Text}, FIND(" ",{!Input_Text}, 1) + 2,FIND(" ",MID({!Input_Text}, FIND(" ",{!Input_Text}, 1) + 1,LEN({!Input_Text} )-FIND(" ",{!Input_Text}, 1)))-1)) & 
    UPPER(MID({!Input_Text}, FIND(" ",MID({!Input_Text}, FIND(" ",{!Input_Text}, 1) + 1,LEN({!Input_Text})-FIND(" ",{!Input_Text}, 1))) + FIND(" ",{!Input_Text}, 1) + 1,1)) & LOWER(MID({!Input_Text}, FIND(" ",MID({!Input_Text}, FIND(" ",{!Input_Text}, 1) + 1,LEN({!Input_Text} )-FIND(" ",{!Input_Text}, 1))) + FIND(" ",{!Input_Text}, 1) + 2,LEN({!Input_Text})-1))
  )
)

simple_fy.txt

// Calculates FY as "FY23", etc (only for 2000s years and when FY starts July)
IF(
    ISBLANK({!StartDate}),
    NULL,
    IF(
        MONTH(DATEVALUE({!StartDate})) > 6,
        "FY" + TEXT( YEAR(DATEVALUE({!StartDate})) - 2000 ),
        "FY" + TEXT( (YEAR(DATEVALUE({!StartDate})) - 2000) - 1 )
    )
)

Subtract_Minutes.txt

// 24/60 divides down to minutes.  Divide by 1 for 1 minute.  Divide by 2 for 2 minutes.
{!ModifiedAt}-(1/24/60)

UK_postcode_validate.txt

// CleanedPostalCode
IF(ISBLANK({!MailingAddress_Postcode}), NULL, UPPER(SUBSTITUTE(SUBSTITUTE({!MailingAddress_Postcode}, ' ', ''), '-', '')))

// -------------------------

// ParsedPostalCode
IF(
  LEN({!CleanedPostalCode}) >= 5 && LEN({!CleanedPostalCode}) <= 8,
  LEFT({!CleanedPostalCode}, LEN({!CleanedPostalCode}) - 3) & " " & RIGHT({!CleanedPostalCode}, 3),
  NULL
)

// -------------------------

// IsUKPostcodeValid
// - RegEx From https://howtodoinjava.com/java/regex/uk-postcode-validation/
IF(ISBLANK({!ParsedPostalCode}), FALSE, REGEX({!ParsedPostalCode}, '^[A-Z]{1,2}[0-9R][0-9A-Z]? [0-9][ABD-HJLNP-UW-Z]{2}$'))

YYDD_to_Date_Variable

// Example: Takes 0526 or 05/26 and converts to 2026-05-01 as a Date Variable.
IF(
    LEN(TRIM({!DateInput})) == 4 || LEN(TRIM({!DateInput})) == 5,
    DATEVALUE("20" + RIGHT(TRIM({!DateInput}), 2) + "-" + LEFT(TRIM({!DateInput}), 2) + "-01"),
    NULL
)