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:
-
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")
-
-
Use the formula in a description rule.
-
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:
-
Define a formula:
-
"Fund Balance" / 3 * "Month Number in the Quarter"
-
-
Assign the formula to the Entered Amount accounting attribute for the event class.
-
Use the formula as the Entered Amount for the journal line rule.
-
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 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. |