Miscellaneous Formula Use Cases

Determining field values with formulas is a common mechanism on the Salesforce 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.

In B2B scenarios, you can use the following formula to determine the invoice region (given that ONB2__TaxNumber__c specifies the tax ID).

With respect to taxation rules and depending on your type of business, you may handle Switzerland or the United Kingdom like EU countries, adding "CH","EU" or "GB","EU" to the country list in the formula.

Be aware that this formula is an example. You may have to adjust it according to your business use case.

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",
        "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.

If your org has enabled JustOn's VAT number validation, you can use the following formula to determine the invoice region, provided that the VAT validation has produced a result (true or false).

With respect to taxation rules and depending on your type of business, you may handle Switzerland or the United Kingdom like EU countries, adding "CH","EU" or "GB","EU" to the country list in the formula.

Be aware that this formula is an example. You may have to adjust it according to your business use case.

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",
        "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(ONB2__VATIsValid__c = true, "RC", "EU")
    )
)

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.

The scenario may be more complex. Assume, for example, your org sells electronic goods or services to individuals. In this case, local taxes apply in the EU, as explained in When and where to charge VAT?. Consequently, you need a tax rule for every possible EU billing country, and your region formula must yield the billing country for EU accounts without tax ID.

To support this use case, you can use the following formula to determine the invoice region (given that ONB2__TaxNumber__c specifies the tax ID).

With respect to taxation rules and depending on your type of business, you may handle Switzerland or the United Kingdom like EU countries, adding "CH","EU" or "GB","EU" to the country list in the formula.

Be aware that this formula is an example. You may have to adjust it according to your business use case.

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",
        "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), TEXT(BillingCountryCode), "RC")
    )
)

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

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

Calculate payment provider fee

Calculate contract end date

Assume you need to calculate a contract end date based on a given start date and a duration (like start date + duration - 1 day). To this end, create a field ON_EndDate__c of the type Formula (Number) that returns the end date based on the values set in the number fields Contract_Start_Date__c and ContractDurationMonth__c.

ADDMONTHS(Contract_Start_Date__c, ContractDurationMonth__c) -1