Enterprise Journal Template Sections: Columns Tab

When you create an Enterprise Journals template, it includes predefined Sections for the Header and Line Item Details.

The Journal Header stores the header attributes and their properties as data records in columns.

The following Attribute Columns are predefined in the Header section. You cannot edit or delete predefined attributes, however you can exclude them from a template.

Table 21-5 Header Attribute Columns

Name of Attribute Type Details
Journal ID Text Unique Journal Identifier - internal and defined in the workflow
Journal Name Text Name of the Journal (unique for Header)
Journal Description Text Description of Journal
Entered Currency List Entered Currency
Accounting Date Date Effective Date of the Journal Entry

Table 21-6 Line Item Details Attribute Columns

Name of Attribute Type Details
Line Number Text Line Item index (unique per line)
Account Text Line Item Account
Debit Number Debit Amount (Two Decimals, Thousand Separated)
Credit Number Credit Amount (Two Decimals, Thousand Separated)
Line Description Text Description of the Line Item entry
Amount Number Amount (Two Decimals, Thousand Separated)

Note:

Only one option of either Debit/Credit or Amount attribute can be selected for a particular journal Template.

Note:

  • The default Debit/Credit and Amount attributes have default validation logic associated with them to ensure the journal is balanced across all lines based on those attributes
  • For Debit/Credit the user cannot put a value into both fields on the same journal line

To specify Columns information:

  1. Open an Enterprise Journals template.
  2. From template Sections, double-click Header or Line Item Details.
  3. Select the Columns tab and edit information as needed.
  4. From Columns in Layout, select the number of columns for sections. The maximum is 3 columns.
    • Included - Select the attributes to be included for display. To exclude an attribute, uncheck the Included checkbox.

      Note: Predefined attributes have a Key icon enabled.

    • Name - The name of the attribute.

    • Dimension - The column from an attribute of a specific dimension. Read-only.

    • Data Type - The corresponding data type for the attribute. Read-only.

    • Display Width - For Line Item Details, select the column width in the Journal when rendered.
      • Small: Fixed pixel width of 70
      • Medium: Fixed pixel width of 100
      • Large: Fixed pixel width of 300
      • Size to Fit: Fit the width of the column based on the longest text in the rows
      • Custom: Specify a width. Minimum value: 20. Maximum value: 999.
    • Total - For Line Item Details, the total method for the attribute. Read-only.
    • Total Row - For Line Item Details, select how the Total row should be displayed: Top, Bottom, or None.
    • View Only - Select whether the column is for view only.
  5. Optional: To add new attributes to a Journal Template Section, click the Add (+) icon, select Add Attribute and specify the attribute information.
    • Add Attribute: Go to step 6.

    • Add Attribute from Dimension:

      1. Select a Dimension.
      2. Select attributes from the Available Attributes list and Move them to the Selected Attribute list.
      3. The system includes the key attribute of the dimension as a Selected Attribute. You cannot clear the key attribute.
      4. Click OK and then Save or Save and Close.
  6. If you selected Add Attribute, on Create New Attribute, enter the Properties tab information:
    • Name: You can enter a maximum of 80 characters.

    • Description: You can enter a maximum of 255 characters.

    • Specify the Attribute Type:

      • Text: You can enter a maximum of 255 characters.
      • Integer: You can enter values -2147483648 to 2147483647
      • List: You can enter value 255
      • Number: You can enter value xxxxxxxxxxxxxxxxx.xxxxxxxxx (17 digits and 9 decimal places)

      Changing the Attribute Type overrides previously specified Validations or Calculations. You cannot change this setting after the attribute is created.

      • Input: Input is the default, and the Validations tab is enabled.

        For periods that have been opened and form instances created, the modified validation rule does not apply. Changes made apply only to new form instances.

      • Calculated: If type is Calculated, then the Calculated tab is enabled.

    • Data Type

      Select one:

      • Date

      • Date and Time

      • Integer

      • List

        Click Add and enter values for the attribute. List is local to the collection and can't be shared among other collections.

      • Number

        If you select Number, select formatting options to override the defaults set in the Preferences section of System Settings.

        Note:

        You can only enter numeric values for this attribute.

        • For Decimal Places, enter the number of decimal places to display.

        • Select Display as Percentage to display a percent sign.

        • Select Use Thousands Separator to display a thousands separator (for example, 1,000.00). The system displays the thousands separator symbol for the user locale.

        • In Currency, select the currency, for example, (INR)

        • In Negative Number Format, select how to display negative numbers; for example, (123).

        • To scale a number, in Scale, select from 1000 to 1000000000000

      • Text (255 characters maximum)

      • True or False

      • Yes or No

    • Required

      If the attribute is required, you must enter a value in this field during data entry.

    • Use Value

      If Use Value is populated, the system applies the value entered by the designer for any record created by the user.

      You can change the default value during data entry.

  7. Click OK to save the attribute.
    You must save the attribute before including it in a calculation.
  8. To continue adding an attribute:
    • If you selected Input for the Attribute Type, then select the Validations tab.

      To add a conditional expression:

      You can create a special validation rule for the value entered by the user.

      1. Click Add.

      2. Select an Operand and enter Value1 and Value2.

      3. If you need a second condition, select from the following lists:

        • Conjunction; for example, And, Or

        • Operand; for example, Equals, Between, Does not Equal, Greater than, Is blank, Is not blank, Less than, Not between

        • Value1 and Value2

      4. Click OK.

    • If you selected Calculated for the Attribute Type, then select the Calculations tab.

      The following section is an example of how to add a calculation attribute.

      1. Create a Journal Template with these attributes:

        Int1 Input and Data Type: Integer. Click OK, and from Edit Journal Template Section, select Int1 as a Key Identifier.

        TextInput: Input and Data Type: Text.

      2. To use the attribute in TextCalc, save the data.

      3. Create an attribute TextCalc: Calculated and Data Type: Text.

      4. On the Calculations tab, enter the following fields:

        1. Calculation Type: Select Scripted.

        2. Add Function: Select TextLocation, and then click Add.

        3. INSTRING(<Value>, <Value To Search>) is added to Calculation Definition.

        4. Click <Value> and either enter a value (enclosed in single quotation marks and case sensitive), or, in Add Attribute, select an attribute, TextInput, and then click Add.

          <Value> changes to {TextInput}.

        5. Replace <Value to Search> with 'tion'

          Ensure that you replace <xxx> with single quotation marks: 'xxx'.

        Example INSTRING({TextInput}, 'tion')

  9. To add a calculation expression, select a data type and calculation type as described in the following table, and then click OK.
  10. Click OK to save your changes.

    Table 21-7 Data Type and Calculation Type

    Data Type selected on Properties Tab Calculation Type Description

    For all data types

    Assign Value to List, and then select the Attribute value.

    You must have saved the List attribute values.

    Return an attribute value based on the assignment to a List member.

    For all data types

    Conditional

    For a given attribute, return attribute value A if specified conditions have been met. If the conditions have not been met, return attribute value B.

    List

    Assign List To Value

    Based on the value of an attribute, return the related member from the list.

    Numeric, Integer

    Formula

    Calculate an attribute using common mathematical expressions.

    Example: (A+B)/C

    Numeric, Integer

    Round

    Round attribute to the specified number of digits. The default is 2.

    Text

    Concatenate

    Paste together text attributes. This includes literal strings and automatically converting non-text attributes to strings.

    Example: First_Name+" "+Last_Name+":"+Birth_Date

    Integer, Number, Text

    Scripted

    See the following section:

    Scripted Functions

    A free-form scripted calculation. Scripted is available for attributes of type Integer, Multi-line Text, Number, or Text.
  11. Click Save, Save and Close, or Close.

    Scripted Functions

    • Absolute Value: Returns the absolute value of a specified number. If a number is less than zero, the positive value of the number is returned. If the specified number is equal to or greater than zero, the specified number is returned.

      ABS(<Number>)

    • Add Month: Returns a date offset a specified number of months from the starting date. The date will always fall in the specified month offset. If the starting date has a day value beyond what is in the offset month, the last day of the offset month will be used. For example, EDate (31-Jan-2017, 1) returns (28-Feb-2017). For Months, enter the number of months before or after the start date. A positive value for months yields a future date. A negative value yields a past date.

      ADD_MONTH(<Start Date>, <Months>, <Length>)

      Example: ADD_MONTH(DATE(2017, 2, 15) 3)

    • Average Prior: Averages a numeric amount over the prior X periods.

      AVERAGE_PRIOR(<Value>, <Number of Periods>, <To Currency*>

      Example: AVERAGE_PRIOR( {Balance (Reporting)}, '2', 'EUR'

    • Date: Returns a date value based on specified integer values for the year, month and day.

      DATE(<Year>, <Month>, <Day>)

    • Date Difference: Returns the difference in days, hours minutes, or seconds between two dates. For DATE 1 and DATE 2, the values TODAY and NOW can be used, which denote the current date (with no time component) and date-time, respectively.

      DATE_DIFF(<Date1>, <Date2>, <Type>)

      Example: DATE_DIFF('TODAY', {Preparer End Date}, 'DAYS') or DATE_DIFF({Preparer End Date}, 'NOW', 'HOURS')

    • Day: Returns the day value of a date as an integer number

      DAY(<DATE>)

    • Extract Text: Returns the substring within the value, from the positions specified.

      SUBSTRING(<Value>, <Location>, <Length>)

      Example: SUBSTRING( {Name} , 5, 10)

    • If Then Else: Allows the user to insert a conditional calculation into the scripted calculation. IF_THEN_ELSE calculations can also be nested to support ELSE IF type calculations.

      IF_THEN_ELSE(<Condition>, <Value1>, <Value2>)

      Example:

      IF_THEN_ELSE( {Risk Rating} = 'Low', 'Good',
      IF_THEN_ELSE( {Risk Rating} = 'Medium', 'Better',
      IF_THEN_ELSE({Risk Rating} = 'High', 'Best','Bad')))
      
    • Length: Takes a text value as a parameter and returns an integer which is the number of characters in the text. If the value is empty/null, the calculation will return 0.

      Length ({<attribute>})

      Example: LENGTH('Value') would return 5, and LENGTH({Name}) would return the number of characters in the name of the object.

      Use the calculation with SUBSTRING to extract the last four characters of a text value.

      SUBSTRING( {MyString}, LENGTH ({MyString}) - 4

    • Lowercase: Returns the value in lower case.

      LOWERCASE(<Value>)

      Example: LOWERCASE( {Description} )

    • Maximum: Returns the maximum value from a list of attributes. There can be any number of parameters.

      MAX(<Value1>, <Value2>,<ValueN>)

      Example: MAX( TRANSLATE( {Source System Balance (Entered)}, 'USD', 'Accounting'), TRANSLATE( {Source System Balance (Functional)}, 'USD', 'Accounting'), TRANSLATE( {Source System Balance (Reporting)}, 'USD', 'Accounting') )

    • Maximum Prior: Returns the maximum value over the prior X periods.

      MAX_PRIOR (<Value>, <Number of Periods>)

      Example: MAX_PRIOR( {Balance (Functional)}, '6', 'CAD', 'REC')

    • Minimum: Returns the minimum value from a list of attributes. There can be any number of parameters.

      MIN(<Value1>, <Value2>,<ValueN>)

      Example: MIN( TRANSLATE( { Balance (Entered)}, 'CAD', 'REC'), TRANSLATE( {Balance (Functional)}, 'CAD', 'REC'), TRANSLATE( {Balance (Reporting)}, 'CAD', 'REC') )

    • Minimum Prior: Returns the minimum value over the prior X periods.

      MIN_PRIOR (<Value>, (<Value>, <Number of Periods>)

      Example: MIN_PRIOR( {Source System Balance (Functional)}, '6', 'EUR', 'Simplified')

    • Month: Returns the month value of a date as an integer number (1-12)

      MONTH (<DATE>)

    • Power: Raises one number to the exponential power of another.

      POWER(x,y) where x=BASE NUMBER,and y=EXPONENT and x and y can be attributes or calculations, as long as they are numeric.

      Example: POWER(3,4)=81

      Note:

      Fractional values will reduce the number to its root. For example, POWER(27, 1/3) = 3 the cube root.

      Negative values will perform an inverse of the exponential calculation. For example, POWER(2, -2) = 1 / (2^2) = 1 / 4 = .25.

    • Prior: Returns the value of the specified prior period.

      PRIOR(<Value>, <Number of Periods Prior>)

      Example: PRIOR( {Source System Balance (Entered)}, '1', 'EUR'')

    • Round: Returns the value rounded to the decimal places specified.

      ROUND(<Value>, <Decimal Places>)

      Example: ROUND( ({Scripted Translate} /7), 4)

    • Sum Prior: Returns the sum of a value over the prior X periods.

      SUM_PRIOR(<Value>, <Number of Periods>)

      Example: SUM_PRIOR( {Balance (Reporting)}, '3', 'EUR')

    • Text Location: Returns the location of the substring within the attribute value, starting at 1 as the first position.

      INSTRING(<Value>, <Value To Search>)

      Example: INSTRING( UPPERCASE( {Name} ), 'TAX' )

    • Translate: Translates a currency attribute to a numeric attribute using a specified rate type.

      TRANSLATE(<Value>, <To Currency>, <Rate Type>)

      Example: TRANSLATE( {Balance (Entered)}, 'EUR', 'Acct')

    • Uppercase: Returns the value in upper case.

      UPPERCASE(<Value>)

      Example: UPPERCASE( {Name} )

    • Year: Returns the year value of a date as an integer number.

      YEAR (<DATE>)