Creating Attributes

You can create custom attributes in Account Reconciliation from the Attributes tab under Application , then Configuration. There are various tabs that display based on your entries to guide you through the attribute creation process.

  • Properties - contains the core properties of the attribute
  • Format - this tab only displays for Number type attributes
  • List - only appears for List type attributes
  • Calculation - contents of this tab depend on the Calculation type chosen.

Some of the tabs only display if you create a certain type of attribute. For example, if you select Calculation in the Properties tab, you will see the Calculation definition tab and you are guided through the required entries based on what you enter.

To create attributes:

  1. From Home, click Application, and then Configuration, and then select the Attributes tab.
  2. Click New (+). The New Custom Attribute dialog displays.
    New Custom Attribute dialog
  3. In Name, enter an attribute name.
  4. In Type, select an option for the type of attribute:
    List of Type field selections
    • Date

    • Date and Time

    • Integer

    • List

      Enter a list of valid responses to the question. To import a list of attributes, see Importing List Attributes.

    • Multi-Line Text

      The maximum length should be less than 4,000 characters.

      Select Multi-Line Text, then enter the Number of Lines, from 3 to 50 lines. Multi-Line Text determines how many lines of text are visible, without scrolling, on the Actions dialog boxes.

      For Multi-Line text type: Select Include Attachments if you want the custom attribute to include an attachments section on the Reconciliation Actions dialog box.

    • Number

      If you select Number, select number formatting options in the Format tab:
      Example of Format tab for a number attribute

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

      • Select Display as Percentage to display a percent sign.

      • Select the Thousands Separator option if you want numbers to display a thousands separator (for example, 1,000.00)

      • From the Currency Symbol list, select a currency symbol, for example, Dollars ($).

      • From the Negative Number list, select how to display negative numbers, for example, (123).

      • From the Scale list, select a scale value for numbers, for example, 1000.

      For all numeric attributes, you can enter a value in the Total (Grouping Method) field. The group view displays on the Reconciliations List. The values for the grouping method can be:

      • None
      • Average
      • Count
      • Sum
    • Text

    • True or False

    • User

    • Yes or No

    • Select whether the new attribute is a Calculation attribute.

      Calculated attributes are read-only. Administrators can add attributes to the attributes sections in the Actions dialog boxes, and workflow users can view them in the actions dialog boxes and in transactions. Administrators can restrict access to certain roles by setting access to Do Not Display. For example, for calculated attribute XYZ, an administrator could add Viewer: Do Not Display access to it, so that XYZ would not be displayed to viewers.

      Any user role can add calculated attributes as columns in views and lists. They can also be added as filterable attributes in the Filter Panel.

    • When you select Calculation, a Calculation definition section is displayed:
      Example of Calculation dialog

      • Calculation Type: The list of values is determined by the attribute type:

        • Assign Value to List—Assign a value to a List type attribute

        • Assign List To Value—Assign a List Value to the value of a different attribute. Only available for attributes of type List

        • Conditional—A conditional calculation (If – Then – Else)

        • Scripted—A free-form scripted calculation. Scripted is available for attributes of type Date, Text, Number, or Integer

        The following table lists the calculation types that each attribute type can use when the Calculation option is chosen:

        Table 3-1 Calculation Types that Each Attribute Type Can Use When the Calculation Option is Chosen

        Attribute Type Assign Value to List Conditional Scripted Assign List to Value
        Date     X  
        Date/Time     X  
        Integer X X X  
        List   X   X
        Multi-Line Text X X X  
        Number X X X  
        Text X X X  
        True/False   X    
        User        
        Yes/No   X    
      • When you select Scripted Calculation type, to enter a free-form calculation equation, use the Add Attribute and Add Function:
        Scripted Calculation type example

        Add Attribute—Select an attribute and click Add to insert the attribute into the Calculation Definition box at the location of the cursor. If the cursor is in the middle of a word or attribute, the word/attribute will be replaced in the definition. Any attribute that is added will have brackets {} around the name, according to the scripting format.

      • Add Function—Select a function and click Add to add the function to the Calculation Definition. The Function is added with placeholders for each parameter.

        For example:

        Insert the TRANSLATE function in the calculation definition:

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

        Then replace the placeholders with attributes:

        TRANSLATE({Source System Balance (Entered)}, 'USD') - TRANSLATE({Subsystem Balance (Entered)}, 'USD')

        Other scripted function examples:

        • 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.

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

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

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

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

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

          Note:

          Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

          Note:

          Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.
        • Date: Returns a date value based on specified integer values for the year, month and day. For example, this function creates a value of type Date, so DATE(2018, 5, 31) would convert to May 31st 2018. This can then be used in the Date Difference function, to pull the difference in days between a date, such as an end date, and this specific date.

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

          You can also use PERIOD START, PERIOD END, or PERIOD CLOSE dates when creating a calculated data attribute. For example, if you wanted to compare the Closed Date on an Adjustment transaction to the PERIOD END date.

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

          Valid types are DAYS, HOURS, MINUTES, SECONDS.

          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. Extract Text/Text Location uses the value of 1 for the first character.

          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')))
          
        • Instring: Returns the index of the substring within the value.

          INSTRING(<Value1>, <Value to Search>)

          Example: INSTRING({Name}, 'a')

        • Length: Takes a text value as a parameter and returns an integer which is the number of characters in the text.

          LENGTH('Value') returns 5, and LENGTH({Name}) would return the number of characters in the name of the object. If the value is empty/null, the calculation will return 0.

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

          Example: 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>, <To Currency*>, <Rate Type*>, <Rate Period*>)

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

          Note:

          Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

          Note:

          Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.
        • Minimum: Returns the minimum value from a list of attributes. There can be any number of parameters.

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

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

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

          MIN_PRIOR (<Value>, <Number of Periods>, <To Currency*>, <Rate Type*>, <Rate Period*>)

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

          Note:

          Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

          Note:

          Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.
        • 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, so 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.

          Note:

          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. If the Number of Periods Prior is omitted, it is assumed to be the value 1.

          PRIOR(<Value>, <Number of Periods Prior*>, <To Currency*>, <Rate Type*>, <Rate Period*>)

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

          Note:

          Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

          Note:

          Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.
        • 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>, <To Currency*>, <Rate Type*>, <Rate Period*>)

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

          Note:

          Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

          Note:

          Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.
        • Text Location: Returns the index of the substring within the 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( {Source System Balance (Entered)}, 'EUR', 'Accounting')

          Note:

          Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation.
        • 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>)