Adding Collection Attributes

Attributes are user-defined fields defined centrally by administrators and used in many places. You can specify different value types for attributes: Date, Date and Time, List, Number, Text, and True or False.

For collections, you can add date calculation attributes such as a Start Date and End Date for a data collection period. You can also persist the historical dimensional attribute values on existing data forms.

To add collection attributes:

On the Home page, click Application, and then click Supplemental Data.
Click the Collections tab on the left.
Select a collection, and then click Edit. The Edit Collection window is displayed.
Click the Attributes tab. It displays the following columns:
- Key Identifier: Indicates whether the attribute is a key identifier or not. If no data exists for this collection, you can modify the Key Identifier and attributes.
  Note:
  - The Key Identifier and Assign Workflow check box options for Entity is selected by default. As you add attributes, you can select one or more as the Key Identifier.
  - Supplemental Data Manager does not support the attributes of type Calculated as a Key Identifier attribute. If you select this type of attribute as a Key Identifier, you may encounter unexpected behavior and errors in collections.
- Assign Workflow: Displays the workflow defined in the collection interval. It is not editable.
- Persist: Indicates whether the dimension attribute’s value should persist in the referenced forms. This column is applicable only for Dimension Reference type of attribute.
- Name: Name of the attribute.
- Dimension Name: This column is applicable only for dimension attribute and provides the dimension name.
- Attribute Type (displayed if there are existing attributes): Indicates the type of attribute:
  - Input
  - Dimension Reference
  - Calculated
- Data Type: Date, Date and Time, Integer, List, Multi-Line Text, Number, Text, True/False, or Yes/No
- Total: Allows you to specify the totaling method for the attribute:
  - Sum: Additive total
  - Average: Average of the rows with data. Rows without data are not counted in the denominator.
  - Count: Count of the rows with data
  - None: Total isn't calculated
Click the Add icon and then click any of the following:
- Add Attribute: The Create New Attribute window is displayed. See See Adding a New Attribute for a Collection.
- Add Attribute from Dimension: The Add Attribute From Dimension window is displayed. See Adding Attributes from Dimension.

Adding a New Attribute for a Collection

To add a new attribute for a collection:

In the Create New Attribute window, specify the following on the Properties tab:
- Name: Enter a name for the attribute. You can enter a maximum of 80 characters.
- Description: Enter a description. You can enter a maximum of 255 characters.
- Select the Input or Calculated option as the attribute type.
  Input is the default, and the Validations tab is enabled. For periods that have been opened and form instances created, the modified validation rule doesn’t apply. Changes made apply only to new form instances.
  
  If you select Calculated, then the Calculations tab is enabled.
  
  Note:
  Once you select Input or Calculated and save the attribute, you can’t change this setting after the attribute is created. Changing the attribute type overrides previously specified validations or calculations.
- Data Type: Select one of the following as the data type. After you select the data type, you can also select Required and/or Use Value options.
  - Date
  - DateTime
  - Integer
  - List: Click Add and enter values for the attribute. List is local to the collection and can't be shared among other collections.
  - Multi-Line Text: If you select this option, select the Number of Lines, from 3 to 50 lines. The default value is 3. Multi-Line Text determines the number of lines of text visible in the form without scrolling, on the Actions dialog boxes. The maximum length should be 2000 characters. However, the width remains fixed for the text area.
    Select the Required check box if it is mandatory for the Data Type to hold this value. If you select the Use Value check box, then the value you entered for the Number of Lines will by default also be applicable here. Beyond that, it will be a scrolled text.
  - Number: Select this option to specify the formatting options. This will override the default settings in the Preferences section of System Settings.
    
    Note:
    You can only enter numeric values for this attribute.
    - Decimal Places: Enter the number of decimal places to display.
    - Display as Percentage: Displays a percent sign.
    - Use Thousands Separator: Displays a thousands separator (for example, 1,000.00). The system displays the thousands separator symbol for the user locale.
    - Currency: Select the currency.
    - Negative Number Format: Select how to display negative numbers.
    - Scale: Scale the number. Range is 1000 to 1000000000000.
  - Text: Enter maximum of 255 characters
  - True/False
  - Yes/No
- Required: If the attribute is required, you must enter a value in this field during data entry.
- Use Value: If you specify this value, 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.
Click OK to save the attribute. The Create New Attribute window closes.
In the Edit Collection window, click Save to save the collection.

Note:
You must save the new attribute and the collection. Only after doing this, you will be able to access the Validations tab or the Calculation tab for the attribute.
Select the attribute you have created and click the Edit icon.
The Edit Attribute window is displayed.
If you've selected the Input option (step 1), then you can add a conditional expression on the Validations tab:
1. Click Add. A row is added to the table.
2. Click the columns within the first row to display the drop-down lists, where you can select the Operator and enter Value1 and Value2 accordingly.
3. If you need a second condition, click Add to add another row.
4. Click the columns within the second row to display the drop-down lists, where you can select the Conjunction such as And or Or.
5. Click OK to save the conditions.
If you’ve selected the Calculated option (step 1), then you can add a calculation on the Calculations tab. Select the Calculation Type:
- Assign Value to List: Select the associated Attribute.
- Conditional: Add the condition for Calculation Definition.
- Scripted: Provide the Calculation Definition and select the attribute and function.

To add a calculation expression, specify the data type and calculation type as described in the following table.

Table 43-2 Data Type and Calculation Type

Data Type selected on Properties Tab	Calculation Type	Description
For all data types	Select 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.



Integer, Number, Text	Scripted See Scripted Functions	A free-form scripted calculation. Scripted is available for attributes of type Integer, Multi-line Text, Number, or Text.

Click OK to save the attribute.

Note:
Changes to attributes apply only to subsequent data collection periods.

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>)

Example to Add a Calculation Attribute

Create a collection with the following attributes:
Int1: Input as attribute type and Data Type as Integer. Click OK, and from Edit Collection, select Int1 as a Key Identifier

TextInput: Input as attribute type and Data Type as Text.
Save the data.
Create an attribute called TextCalc with attribute type as Calculated and Data Type as Text.
On the Calculations tab, specify the following:
- Calculation Type: Select Scripted.
- Add Function: Select TextLocation, and then click Add.
  INSTRING(<Value>, <Value To Search>) is added to Calculation Definition.
- Click <Value> and either enter a value (enclosed in single quotation marks and case sensitive), or, in Add Attribute, select the TextInput attribute, and then click Add.
  
  <Value> changes to {TextInput}.
- Replace <Value to Search> with 'tion'.
  Note: Ensure that you replace <xxx> with single quotation marks: 'xxx'. For example, INSTRING({TextInput}, 'tion')
Click OK to save the attributes.

Adding Attributes from Dimension

In the Add Attribute From Dimension window:

Select a Dimension
Select attributes from the Available Attributes list and move them to the Selected Attribute list.
The system includes the key attribute of the dimension as a Selected Attribute. You cannot clear the key attribute.
Click OK.
The Add Attribute From Dimension window closes and the selected attributes are displayed in the Edit Collection window.
You can select the check box in the Persist column to indicate that the attribute’s value should persist in the referenced forms. If the dimension attribute is updated, then the changes won’t be updated in the forms where the attribute is referenced. If you don’t select this check box, then the referenced attribute is updated with the latest changes. After you save the collection, you can’t change this setting.
Click Save or Save and Close.