Formula Fields and Cross-Object Formula Fields

Introduction

Alright! I’m pretty happy about this video, as we go over 5 common and useful formula fields that you might find yourself using, including formulas which make cross-object references (from Opportunity to Account, and from Case to Contact in my examples).

Formula fields are extremely useful functionality which give an administrator or developer a way to surface information in novel ways. For me, I find the possibility of building dynamic urls using HYPERLINK() particularly intriguing — I show an example in the video of how that might be used with a VOIP dialer.

Here are the code of the examples, indented for readability (a practice I always recommend, particularly with longer formulas):

Partner Commission

IF(Partner_Sale__c, Amount * 0.20, 0)

Hyperlink

HYPERLINK(
    "http://somevoipserver.com/dial?phone=" & Contact.Phone, 
    "CALL: " & Contact.Phone
)

Case Age Rating

IF(
    TODAY() - DATEVALUE(CreatedDate) < 7, "New",
    IF (
        TODAY() - DATEVALUE(CreatedDate) < 14, "Aging",
        "Old"
    )
)

Case Age Rating with IMAGE()

IMAGE(
    IF(
    TODAY() - DATEVALUE(CreatedDate) < 7, "/img/samples/flag_green.gif",
    IF (
        TODAY() - DATEVALUE(CreatedDate) < 14, "/img/samples/flag_yellow.gif",
        "/img/samples/flag_red.gif"
    )
),
"Age Flag",
40, 40
)

Opportunity Rating based on Account Fields

IF (
    AND (
        Account.AnnualRevenue > 10000000,
        CONTAINS (CASE (Account.BillingCountry, "United States", "US", "America", "US", "USA", "US", "NA"), "US")
    ),
    IF(
        ISPICKVAL(Account.Type, "Manufacturing Partner"), "Hot",
        IF(
            OR (
                ISPICKVAL (Account.Type, "Channel Partner/Reseller"),
                ISPICKVAL(Account.Type, "Installation Partner")
            ),
        "Warm", "Cold"
        )
    ),
"Cold"
)

That’s all! Hope this was helpful.

Add comment

Recent Posts

Categories