Skip to content

Miscellaneous Formula Use Cases

Determining field values with formulas is a common mechanism on the Force.com platform and hence, in JustOn. See a formula as an equation that makes use of other, context-specific data and operations to automatically calculate a new value of some type. For details, see An Introduction to Formulas and Calculate Field Values With Formulas in the Salesforce Help.

This page summarizes miscellaneous formulas that can be applied in various use cases in JustOn. These formulas are not shipped with JustOn. They have been developed in customer projects and have proven their effectiveness in productive environments.

Display the name of the month that precedes the invoice date

Assume you need to print the name of the month that precedes the invoice date on the invoice. To cover this use case, you create two custom formula fields on the invoice:

(1) A field (API name, for example, LastMonthNumber__c) of the type Formula (Number) that returns the previous month based on ONB2__Date__c

IF(MONTH( ONB2__Date__c ) = 1, 12,
IF(MONTH( ONB2__Date__c ) <= 12, (MONTH(ONB2__Date__c) - 1),
null))

(2) A field of the type Formula (Text) that returns the (English) name of the month based on LastMonthNumber__c

IF( TEXT(LastMonthNumber__c) = "1", "January" & " " & TEXT(YEAR(ONB2__Date__c)) ,
  IF( TEXT(LastMonthNumber__c) = "2", "February" & " " & TEXT(YEAR(ONB2__Date__c)) ,
    IF( TEXT(LastMonthNumber__c) = "3", "March" & " " & TEXT(YEAR(ONB2__Date__c)) ,
      IF( TEXT(LastMonthNumber__c) = "4", "April" & " " & TEXT(YEAR(ONB2__Date__c)) ,
        IF( TEXT(LastMonthNumber__c) = "5", "May" & " " & TEXT(YEAR(ONB2__Date__c)) ,
          IF( TEXT(LastMonthNumber__c) = "6", "June" & " " & TEXT(YEAR(ONB2__Date__c)) ,
            IF( TEXT(LastMonthNumber__c) = "7", "July" & " " & TEXT(YEAR(ONB2__Date__c)) ,
              IF( TEXT(LastMonthNumber__c) = "8", "August" & " " & TEXT(YEAR(ONB2__Date__c)) ,
                IF( TEXT(LastMonthNumber__c) = "9", "September" & " " & TEXT(YEAR(ONB2__Date__c)) ,
                  IF( TEXT(LastMonthNumber__c) = "10", "October" & " " & TEXT(YEAR(ONB2__Date__c)) ,
                    IF( TEXT(LastMonthNumber__c) = "11", "November" & " " & TEXT(YEAR(ONB2__Date__c)) ,
                      IF( TEXT(LastMonthNumber__c) = "12", "December" & " " & TEXT(YEAR(ONB2__Date__c)-1) ,

null)))))))))))))
Determine the invoice region for controlling tax rules

Assume the following example: There are four tax situations, for which you need individual tax rules that yield a specific tax rate.

Tax Situation Tax Rule Tax Rate Invoice Region
Germany, tax ID available or not available DE-VAT 19% DE
EU country, tax ID not available EU-VAT 19% EU
Reverse charge applicable, tax ID available RC 0% RC
Non-EU country, tax ID not available NON-EU 0% NON-EU

Now, the invoice region determines the tax rule to apply for an account. So you must identify the region based on the account's billing country.

Following the example, you can use this formula to determine the invoice region (given that ONB2__TaxNumber__c specifies the tax ID):

IF(ISPICKVAL(BillingCountryCode,"DE"), "DE",
    IF(CASE(TEXT(BillingCountryCode),
        "AT","EU",
        "BE","EU",
        "BG","EU",
        "CY","EU",
        "CZ","EU",
        "DK","EU",
        "EE","EU",
        "ES","EU",
        "FI","EU",
        "FR","EU",
        "GB","EU",
        "GR","EU",
        "HU","EU",
        "IE","EU",
        "IT","EU",
        "HR","EU",
        "LT","EU",
        "LU","EU",
        "LV","EU",
        "MT","EU",
        "NL","EU",
        "PL","EU",
        "PT","EU",
        "RO","EU",
        "SE","EU",
        "SI","EU",
        "SK","EU",
        "SM","EU",
        "NON-EU")
        = "NON-EU", "NON-EU", IF(ISBLANK(ONB2__TaxNumber__c), "EU", "RC")
    )
)

Based on the billing country as defined for the account, this formula produces either DE, EU, RC or NON-EU as the value for the region.

This functionality requires Salesforce's state and country picklist to be enabled for your org.

Calculate payment provider fee

Create a custom formula field on the Balance object to calculate, for example, the provider transaction fee for Payeezy and Stripe.

API Name Data Type Decimal Places Blank Field Handling
ON_ProviderFee Formula (Currency) 2 Treat blank fields as blanks
BLANKVALUE(
    ONB2__ProviderFee__c,
    CASE(ONB2__PaymentProvider__c,
        "Payeezy", ABS(ONB2__Amount__c) * 0.0189 + 0.23,
        "Stripe", ABS(ONB2__Amount__c) * 0.029 + 0.30,
        null
    )
)

Make sure to adjust the formula to the actual fees of your payment service provider.