An integration with the Import Data Type Costs creates costs from an Excel spreadsheet, and updates costs that already exist. Each row is one cost: its name, start and end dates, cost category, owner, External ID, and the custom fields mapped to its cost category. The integration is set up from the initiative's Tools > Integrations, by clicking New Integration, leaving the Adapter as Excel, and selecting Costs as the Import Data Type. Each imported cost appears in the Costs Register of its initiative.

This article covers both the Advanced and Simple Models. It needs the Integrations permission on the initiative, as set out in Before you start. The import doesn't record any Budget, Forecast or Actual values. For those, see Importing Cost Values.

This is different from Administration > Integrations, which connects third-party applications. See Connecting a Third-Party Application.

Before you start

Who can import

Importing needs both of the following:

• Enable Integrations turned on for the initiative. On the Advanced Model, it is a setting on the initiative's classification, under General Configuration. See Configuring Initiative Classifications (Advanced Model). On the Simple Model, it is under the program's Configuration > Features. See Program Features.
• The Integrations permission set to Full on a team role held on the initiative. See Team Roles. A Full Admin, or a user whose admin role sets Hierarchy to Full, can import without a team role. See Admin Roles.

What must exist first

The import doesn't create anything other than the costs. Set up the following before importing:

• Initiatives — each cost is created on an initiative that already exists, at or below the initiative the integration is set up on.
• Costs enabled — Enable Costs must be on for the classification of each initiative that receives costs, and the cost category must be mapped to that classification. A cost on an initiative where costs aren't enabled is skipped. See Configuring Cost Categories (Advanced Model) or Configuring Cost Categories (Simple Model).
• Owners — each owner must be an existing user. A support user or a guest user can't own a cost. On a project, the owner must also be on the project team. On the Simple Model, the owner must be on the program team.
• Custom fields — a custom field must be mapped to the cost category to be filled in. See Custom Fields (Advanced Model) or Custom Fields (Simple Model).

Where to set up the integration

Set the integration up on the highest initiative that holds every initiative in the file. A cost on an initiative outside that initiative's hierarchy is refused, not moved. On the Simple Model, set it up on the program.

File limits

• The file is an Excel file (.xlsx) of 5 MB or less.
• The sheet has up to 2,000 rows. A sheet with more can't be run, so split it into several sheets or files.
• Only one sheet is read each time the integration runs. The sheet is chosen when the file is uploaded.

Preparing the spreadsheet

Put one cost on each row, with a column for each field. A row of column headings at the top makes mapping easier. The columns can be in any order, but each integration remembers the mapping by column position, so keep the order the same from one run to the next.

Columns to create costs

A new cost needs all of the following:

• Parent Initiative — the initiative the cost is created on. It can be matched on its name, Amplify ID or External ID.
• Cost Category — matched on the cost category's name or ID. The cost category must be mapped to the initiative's classification.
• Name — a name longer than 120 characters is shortened.
• Start Date — the first day of a month.
• End Date — the last day of a month, and later than the start date.
• Owner — matched on the user's name, email or ID.

Columns to update costs

To update costs that already exist, include one column that identifies each cost: the cost's ID or its External ID. The other columns hold the fields to change. See Updating costs later.

Optional columns

• External ID — a reference from another system. It must be unique across costs, and it can be used later to find the cost again.
• Custom fields — one column for each custom field mapped to the cost category. Where a custom field is required, a cost without a value for it isn't created.

Creating the integration

To create the integration:

1. Open the initiative the integration is set up on.
2. Navigate to Tools > Integrations. The initiative's integrations are listed.
3. Click New Integration.
4. Enter a Name for the integration.
5. Select Costs as the Import Data Type.
6. Click Create.

The Adapter is always Excel.

Uploading the spreadsheet

To upload the spreadsheet:

1. On the initiative's integrations, click Run... next to the integration. A window opens.
2. Where the integration has been run before, select Upload New to choose a file, or Use Previous to use the last file again.
3. Choose the file.
4. Select Header? where the first row holds column headings.
5. Select the Sheet that holds the costs.
6. Click Continue. The session page opens, with the rows from the sheet.

The session page runs a preview straight away. Map the columns as described in Mapping the columns, then click Preview again to check the new mapping.

Mapping the columns

The columns are mapped on the session page that opens once the spreadsheet is uploaded. To map the columns:

1. Click Edit Mapping. A header appears above each column.
2. Select the Type for a column.
3. Set the fields that appear for that type.
4. Repeat for each column that is imported.

The mapping saves as you go. It is kept on the integration and applies by column position the next time the integration runs. A column left as No Action is ignored.

Mapping types

• Parent Initiative — the initiative a new cost is created on. Required to create.
• Cost Category — the cost category of a new cost. Required to create.
• Attribute Assignment — puts the value into one of the cost's fields, chosen from the Attribute list: Name, Start Date or End Date. Required to create, once for each of the three.
• Owner — matches a user as the cost's owner. Required to create.
• Identifier Lookup — finds an existing cost to update. For a new cost, the value becomes its External ID.
• Custom Field — puts the value into a custom field mapped to the cost category, chosen from the Custom Field list.
• No Action — leaves the column out of the import.

The types that are required to create a cost are marked Required:. Until the mapping includes every type required to create costs, or an Identifier Lookup to update them, a message says the minimum requirement hasn't been met, and Preview and Import... are disabled.

Match on Attribute

For a column that is matched to existing records, Match on Attribute sets which field of the record the cell is compared with. Capital letters and spaces around the value are ignored.

• Parent Initiative — Name, Amplify ID or External ID. Matching on Name looks only at the integration's initiative and the initiatives below it.
• Cost Category — Name or ID.
• Owner — Name, Email or ID.
• Identifier Lookup — ID or External ID.
• Custom Field — Value or Label, for a custom field with a list of options. Choose whether the cell holds the option's value or the label shown on screen.

Fallbacks

A fallback gives a row a default when its cell has no match, so the row isn't skipped. A row that uses a fallback shows an amber warning.

• Fallback to Default Initiative — creates the cost on the Default Initiative you choose. It must be at or below the initiative the integration is set up on.
• Fallback to Default Cost Category — uses the Default Cost Category you choose. It must be mapped to the initiative's classification.
• Fallback to Default User — uses the Default User you choose as the owner.

Without a fallback, a row whose initiative, cost category or owner isn't found is skipped.

Previewing and importing

Previewing the import

The preview checks every row against Amplify and saves nothing. Each row shows a plus icon where it will create a cost, or a pen icon where it will update one. A red icon on a cell marks a problem. Hover over it to read the message. An amber icon marks a warning, and the row still imports.

Where rows fail, a banner says how many rows were unsuccessful. Click View these rows only. to show just those rows, and Show all rows. to return to the full list. Fix the file or the mapping, then preview again.

Import... is disabled while a preview, import or mapping change is running, and until the mapping can create or update costs. Rows with errors don't disable it.

Importing the costs

To import the costs:

1. Click Import.... A confirmation opens.
2. Click Confirm.
3. Wait for the banner to report that the import has been completed.
4. Click End Session. The integrations list opens.

Rows with errors aren't imported, but every other row is. To import the failed rows, fix them and import a file that holds only those rows. Otherwise the costs already imported are created a second time, unless the mapping has an Identifier Lookup. See Updating costs later.

Until the session is ended, the integration shows Terminate in place of Run... and can't be run again. If a session was left in progress, click Terminate on the integrations list to end it.

Updating costs later

The same integration can be run again with a new file. To update costs that already exist, map a column as Identifier Lookup, matched on ID or External ID. Each row then works as follows:

• Where the value matches a cost, the row updates that cost. The cost must be active and on an initiative at or below the integration's initiative.
• Where it matches no cost and the mapping has every column needed to create one, the row creates a new cost. A mistyped identifier therefore creates a new cost instead of failing.
• Where it matches no cost and the mapping can't create, the row fails.

An update changes only the mapped columns: Name, Start Date, End Date, the owner and custom fields. Two things can't change in an import:

• Cost Category — the column is skipped with a warning.
• Parent Initiative — the column is skipped with a warning. A cost can't be moved to another initiative.

A column set to No Action leaves its field as it is. A blank cell for an optional field also leaves it as it is.

Changing a cost's Start Date or End Date in an import removes any Budget, Forecast and Actual values that fall outside the new dates.

Duplicate External IDs

Where the Identifier Lookup is matched on External ID, each External ID can appear on one row only. If the same External ID appears on several rows and doesn't exist in Amplify yet, the first row creates the cost and the later rows fail. If the External ID already exists, every row with it updates that cost.

Things to know

• A cost created by the import has no Budget, Forecast or Actual values. Load them with Importing Cost Values.
• The cost's page and the Costs Register show the imported costs as they show any other. See Managing Costs.

An import can't be undone. Preview every file first, and try a few rows before importing a large file. To correct a cost afterwards, update it with another import or edit it on its page.

For an overview of imports, including importing other types of data, see Importing Data.