Guidelines for Designing Spreadsheet Templates
Spreadsheet templates are highly configurable. Therefore, you can design them to support specific business use cases. This topic provides some guidelines to help you create spreadsheet templates that minimize the scope for error and are easy to use.
Supported Actions
Depending on the business object, you can use HCM Spreadsheet Data Loader to either create and update or delete object components. You can design a single spreadsheet for both creating and updating an object. However, spreadsheet templates that support a single action are better for users and easier for you to manage. For example:
-
In templates for creating objects, you can mark as required all attributes that must be supplied for new objects. Typically, the same attributes aren't required when updating objects, so you can mark different attributes as required in templates for updating objects.
Tip: To mark an attribute as required, edit the attribute in the Selected Attributes panel on the Design page and select Required. -
In templates for updating objects, you can configure lists of values to identify records to update.
Lists of Values
For many attributes, you can ensure that users enter only valid data by providing choice lists and searchable lists of values. Some attributes have lists of values by default. In particular:
-
Some lookup-validated attributes are automatically configured with choice lists. A list of values is displayed when the user clicks in the cell.
-
To reference a foreign object from your template, you can include the Oracle Fusion Surrogate ID attribute. Attributes like this are automatically configured with searchable lists of values, and the selected attribute is returned to the spreadsheet. While a surrogate ID itself may not be meaningful, you can also configure your own lists of values for attributes included in a spreadsheet. These attributes can be validated using a lookup type, value set, or view object.
For example, you can return additional values to other attributes, such as Person Number, instead of just returning the person ID. You can also copy the list of values configuration to another attribute that is typically used to identify a record, such as the Name or Number identifier. You configure the list of values to return the recognizable number.
Default Values
You can configure default values for attributes in spreadsheet templates. You may want to exclude attributes with default values so that you can enforce their values. Alternatively, you can include the attribute so that users can enter a value, which overrides the default value.
Date Editors
A date editor is provided automatically for all date attributes. However, the user must click in the spreadsheet row to initialize date editors. Therefore, you're recommended not to make a date value the first column in the row. This approach ensures that date editors in the row are initialized before they're used.