Manage User-Defined Formulas

You can use Subledger Accounting user-defined formulas to derive a value that's used in a journal entry, such as a journal amount or description.

Formula Usage and Assignments

A user-defined formula can be used to:

• Calculate a numeric value

• Derive an alphanumeric value

• Return a date value

It can also be used in any of the following accounting rule components:

• Journal line rule

• Account rule

• Mapping set

• Description rule

• Supporting reference

Formula Definition

Define user-defined formulas using sources for the selected event class. Predefined functions and conditions can also be used to derive the resulting value.

• Enter source in double quotes (").

• Enter constant values in single quotes (').

• Enter date values in the format YYYY-MON-DD.

Example 1

A user-defined formula has been defined to use in the description rule for the journal entry rule set for the Invoice event class.

Steps:

1. Define a formula:

   • IF "Project Number" IS NOT NULL AND "Invoice Date" >= '2015-Jan-01'

     THEN Concatenate("Project Number", "Invoice Number")

     ELSE Concatenate("Supplier Name", "Invoice Number")

2. Use the formula in a description rule.

3. Assign the description rule to a journal entry rule set.

Results:

Accounting is created for two project invoices and one nonproject invoice.

This table contains the invoices created by example 1.

Invoice	Invoice Details	Journal Entry Description
1	Invoice number = MA0024 Invoice date = 2014-Dec-11 Supplier name = ABC Inc. Project number = 12345	ABC Inc.MA0024
2	Invoice number = MA0045 Invoice date = 2015-Jan-30 Supplier name = ABC Inc. Project number = 12345	12345MA0045
3	Invoice number = MA0012 Invoice date = 2015-Jan-15 Supplier name = ABC Inc.	ABC Inc.MA0012

Example 2

A journal entry is recorded for the actual 401k funding every quarter. The monthly accrued amount entry is created for each month of the quarter using the following user-defined formula.

Note that the accrual entry is to be reversed at the beginning of the next month by the accrual reversal feature.

Steps:

1. Define a formula:

   • "Fund Balance" / 3 * "Month Number in the Quarter"

2. Assign the formula to the Entered Amount accounting attribute for the event class.

3. Use the formula as the Entered Amount for the journal line rule.

4. Assign the journal line rule to the journal entry rule set.

Fund balance = 1,200 USD

This table contains the monthly entries created using example 2.

Month	Results
1	Accrual amount for the first month in the quarter: 1200 / 3 * 1 = 400
2	Accrual amount for the second month in the quarter: 1200 / 3 * 2 = 800
3	Accrual amount for the third month in the quarter: 1200 /3 * 3 = 1200

Predefined Formula Functions

The following predefined functions are available to be used in user-defined formulas.

This table contains a listing of predefined formula functions.

Type	Function	Description	Example
Alphanumeric	Concatenate	Concatenate two strings into one single string.	Concatenate('This is ', 'a test.') returns 'This is a test'.
Alphanumeric	Substring	Extract part of a string.	Substring('How are you?', 3,5) returns 'w are'.
Alphanumeric	ToText	Convert a numeric value to text.	ToText("Tax Rate") returns the tax rate in text format. Note: A formula with ToText can't be used in a rule condition.
Date	FirstDayOfYear	Return the first day of the calendar year based on the parameter.	FirstDayOfYear('2013-Jul-11') returns '01-Jan-2013' in date format.
Date	LastDayOfYear	Return the last day of the calendar year based on the parameter.	LastDayOfYear('2013-Jul-11') returns '31-Dec-2013' in date format.
Date	FirstDayOfMonth	Return the first day of the month based on the parameter.	FirstDayOfMonth('2013-Jul-11') returns '01-Jul-2013' in date format.
Date	LastDayOfMonth	Return the last day of the month based on the parameter.	LastDayOfMonth('2013-Jul-11') returns '31-Jul-2013' in date format.
Date	AddMonth	Return the date of specified number of months after the date in parameter.	AddMonth('2013-Jul-11',2) returns 11-Sep-2013 in date format.
Numeric	YearNum	Return the year of a date source in 4-digit format.	YearNum('2012-Feb-28') returns the number 2012.
Numeric	MonthNum	Return the month of a date source in numeric format.	MonthNum('2012-Feb-28') returns the number 2.
Numeric	DayNum	Return the day of date source in numeric format.	DayNum('2012-Feb-28') returns the number 28.
Numeric	Round	Round a number to a specific decimal place.	Round(183.1123, 2) returns 183.11 Round(183.1123, -1) returns 180
Numeric	RoundUp	Round up a number to integer.	RoundUp(0.01) returns 1. RoundUp(1.50) returns 2.
Numeric	RoundDown	Round down a number to integer.	RoundDown(0.01) returns 0. RoundDown(1.50) returns 1.
Numeric	Power	Return the power of a number.	Power(3,2) returns 9.
Numeric	NumberOfGLPeriod	Return the number of nonadjustment accounting periods between two dates. If start date isn't the first day of an accounting period it's counted as one period. If end date isn't the last day of an accounting period, it's counted as one period.	If accounting calendar is defined as Monthly, NumberOfGLPeriod('2013-Jul-11', '2013-Oct-10') returns 4.
Numeric	ToInteger	Convert a text value to integer.	ToInteger("Cost Center Segment") returns the cost center segment value as a numeric value.