When a spreadsheet is imported into Amplify, each column is mapped to the data it holds. The mapping is set on the integration session page, under Edit Mapping. Every column gets a Type, and the Types that look up an existing record also take a Match on Attribute. This article lists the Type options, which import data types offer each one, and what each one does.

This article covers both the Advanced and Simple Models. The Types offered depend on the Import Data Type of the integration, and a Type that isn't listed for an import data type isn't offered for it. Where your instance calls benefits Impacts, these labels read Impacts instead. For creating an integration, running it and previewing the result, see Importing Data.

How mapping works

Each column in the spreadsheet has one Type. Amplify saves the mapping as you make each change.

• No Action — the column is ignored. A column holds this Type until it is mapped.
• Required — in the Type list, Required: with a plus icon marks a Type needed to create records. A pen icon marks a Type needed to update them.
• Create and update icons — the icons above the row numbers show whether the mapping allows create (Mapping allows create) and update (Mapping allows update). Where one is unavailable, hovering over its icon lists the columns still needed.

Some Types can be used on one column only. Identifier Lookup, Classification, Owner and Parent Initiative each appear once per mapping. Each Attribute, each Custom Field and each Team Role can also be mapped once. A second column gets an error saying a definition already exists.

Note — the mapping is saved on the integration and is matched to columns by position, not by heading. A spreadsheet with its columns in a different order needs the mapping changed before the next run.

Match on Attribute

A Type that looks up an existing record shows a Match on Attribute dropdown. It says which field of the existing record the cells in the column hold. A column is matched on one attribute only, so every cell in the column must hold the same kind of value, such as all names or all IDs.

Each Type offers its own options, which are listed under the Type. The options are:

• ID — the record's ID number.
• Amplify ID — the label for the ID number on Parent Initiative.
• External ID — the identifier from the system the data came from, saved on the record.
• Name — the record's name. Matching ignores capitals and spaces at the start or end of the cell.
• Email — a user's email address.

Where no record matches, the row fails with an error, unless the Type has a fallback. See Fallbacks.

Types offered on most import data types

• No Action — offered on every import data type. The column isn't imported.
• Attribute Assignment — offered on every import data type. The cell goes into one field of the record, chosen in the Attribute dropdown. The dropdown lists the fields alphabetically by the name shown on screen. Fields that have a Type of their own, such as classification, owner and the initiative a record goes under, aren't listed, and neither are fields Amplify calculates. A blank cell leaves the field as it is.
• Identifier Lookup — offered on Initiatives/Projects, Benefits, Schedule/Tasks, RAID, Costs, Measures and User. It finds the existing record a row updates, and gives a new record its External ID. See Creating and updating records. Match on Attribute is ID or External ID on Initiatives/Projects, Benefits, Costs and Measures. It is Task ID or ID on Schedule/Tasks, and ID on RAID. On User it is ID, Name or Email. Only active records are matched, except for users.
• Classification — offered on Initiatives/Projects, Benefits, RAID and Measures. It sets the classification a new record gets. Match on Attribute is Name or ID. The classification must already exist and be allowed where the record goes. An import doesn't create classifications or programs. The Type applies to create only. On an update the column is skipped with a warning, because the classification of an existing record can't be changed by an import. It has the fallback Fallback to Default Classification.
• Owner — offered on Initiatives/Projects, Benefits, RAID, Schedule/Tasks, Costs and Measures. It sets the owner. Match on Attribute is Name, Email or ID. The owner must be an active user who isn't locked, and can't be a support user or a guest user. On a Simple Model program, the owner must also be on the program team. Owner is required to create every record except a task. It has the fallback Fallback to Default User.
• Parent Initiative — offered on Initiatives/Projects, Benefits, RAID, Schedule/Tasks, Costs and Measures. It names the initiative a new record is created under. Match on Attribute is Name, Amplify ID or External ID. The initiative must be the one the integration belongs to, or beneath it. Name searches only that part of the hierarchy. The Type applies to create only. On an update the column is skipped with a warning, because an import can't move a record. It has the fallback Fallback to Default Initiative. On a Simple Model program, the Type isn't offered for Initiatives/Projects, because new records always go under the program.
• Custom Field — offered on Initiatives/Projects, Benefits, RAID, Schedule/Tasks, Costs, Measures and User. The Custom Field dropdown picks the field. The field must be mapped to the classification of the record, or the row shows a warning and the value isn't set. See Custom Fields (Advanced Model) and Custom Fields (Simple Model). A field that offers a list of options also shows Match on Attribute, with Value or Label. On a user field the options are ID or Name. For a field that takes several options, separate them with commas in the cell. An import doesn't create options. Where a cell doesn't match an option, the row shows a warning and the field is left unset. For a reporting group field the row fails instead. A text value longer than the field's maximum length fails. On User, a Classification Type dropdown also appears, with Users or Resources.

Types for specific import data types

Initiatives/Projects

• Team Role — also offered on Benefits. It adds the user in the cell to the team of the record, with the role chosen in the Team Role dropdown. The roles listed depend on the classification. Match on Attribute is Name or Email. It has the fallback Fallback to Default User.
• Team Mapping — maps a team to a project. Match on Attribute is Name or ID. It applies to projects only.
• Current Stage — moves the record to the stage named in the cell. The Advancing User field picks the user who approved the stage. It isn't available for programs, and a record can't be moved back to an earlier stage.
• Stagegate Milestone Date — sets the date of a stage's milestone. The Milestone dropdown picks the stage. Use one column for each milestone.

Benefits

• Team Role — as for Initiatives/Projects.
• Unit — also offered on Measures. It sets the unit. Match on Attribute is Name or ID. The unit must already exist. Where the classification of the benefit locks the unit, that unit is used and the row shows a warning. It has the fallback Fallback to Default Unit.
• Measure Series — maps a column to one of the measure series of the benefit. The Series dropdown picks the series. Use one column for each series.
• Contributing Measures — links the measures that feed the benefit. Match on Attribute is External ID or Measure Id. Use one column only.

Measures

• Unit — as for Benefits. Measures aren't offered on a Simple Model program.

Schedule/Tasks

• Stage-Milestone Mapping — links a task to a stage. Match on Attribute is Stage Name.
• Benefit-Milestone Mapping — links a milestone to a benefit. Its dropdown is named Match on Benefit Attribute, with ID, Name or External ID. It has the fallback Fallback to Default Benefit.
• Attribute Assignment with the Assigned Resources attribute — also shows Match on Attribute, with Name or ID. Separate several users with commas in the cell.

RAID

• Assign To — names the initiative or benefit the RAID item is assigned to. Pair it with an Attribute Assignment column for the Assign to Type attribute, which holds Initiative or Benefit. Match on Attribute is Name, ID or External ID. The Type applies to create only.
• Likelihood — sets the likelihood. Match on Attribute is Name or ID.
• Impact — sets the impact rating of the RAID item. Match on Attribute is Name or ID.
• Rating — sets the rating. Match on Attribute is Name or ID.
• Linked Task — links the RAID item to a task. Match on Attribute is Name or ID.

Note — the Impact Type on a RAID import is the impact rating of the RAID item. It isn't related to benefits.

Costs

• Cost Category — sets the cost category of a new cost. Match on Attribute is Name or ID. The category must be mapped to the classification of the initiative the cost goes under. The category of an existing cost can't be changed by an import. It has the fallback Fallback to Default Cost Category.

Time-phased import data types

• Cost — offered on Cost Values (Time Phased). It names the cost the values belong to. Match on Attribute is ID or External ID.
• Parent — offered on Benefit Values (Time Phased) and Measure Values (Time Phased). It names the benefit or measure the values belong to. Match on Attribute is Name, ID or External ID. External ID applies to benefits only.

Fallbacks

A fallback replaces a value that is blank or can't be matched. Each fallback is a Yes or No choice. Selecting Yes shows a second field, where you choose the default. A row that uses the default imports with a warning instead of failing.

• Fallback to Default Classification and Default Classification — on Classification.
• Fallback to Default User and Default User — on Owner and Team Role.
• Fallback to Default Initiative and Default Initiative — on Parent Initiative. The default must be in the hierarchy of the integration.
• Fallback to Default Unit and Default Unit — on Unit.
• Fallback to Default Cost Category and Default Cost Category — on Cost Category.
• Fallback to Default Benefit and Default Benefit — on Benefit-Milestone Mapping.

Creating and updating records

Each row is either a create or an update. The row-number cell shows a plus icon for a create and a pen icon for an update.

• Where the mapping has an Identifier Lookup column, Amplify first looks for an existing record that matches the cell. A match that is in the hierarchy of the integration is updated.
• Where nothing matches and the mapping has every column needed to create, the row creates a new record. A value matched on External ID is saved as the External ID of the new record.
• Where nothing matches and the mapping can't create, the row fails.
• Where the mapping has no Identifier Lookup column, every row creates a new record.
• A blank identifier cell creates a new record.
• Where the column is matched on ID and the row creates, the ID is ignored and the cell shows a warning.

An update changes only the mapped columns. A blank cell leaves the field as it is. Archived records aren't matched. On an update, Classification, Parent Initiative and Cost Category columns are skipped with a warning.

Columns needed to create

The mapping allows create once it has these. Attribute Assignment columns are named by the attribute they set.

• Initiatives/Projects — Name, Start Date and End Date attributes, Classification, Owner and Parent Initiative. Parent Initiative isn't needed on a program.
• Benefits — Name, Start Date, End Date and Resolution attributes, Classification, Owner, Parent Initiative and Unit.
• Measures — Name, Start Date, End Date and Reporting Cadence attributes, Classification, Owner, Parent Initiative and Unit.
• Schedule/Tasks — Name, Start Date and End Date attributes, and Parent Initiative.
• Costs — Name, Start Date and End Date attributes, Cost Category, Owner and Parent Initiative.
• RAID — Name and Assign to Type attributes, Assign To, Classification, Owner and Parent Initiative.

Cost Values (Time Phased), Benefit Values (Time Phased) and Measure Values (Time Phased) never create the cost, benefit or measure. They add values to one that exists, or replace a value already there. See Time-phased columns.

Clearing a custom field

On an update, a cell that holds null in any capitalization clears the custom field. A blank cell leaves the field as it is. On a create, a cell that holds null is treated as blank.

Time-phased columns

Cost Values (Time Phased), Benefit Values (Time Phased) and Measure Values (Time Phased) use one row for each value, not one column for each month. A row holds the record, the series, the date and the value. Until the mapping has every column below, the mapping is invalid and Preview and Import... aren't available.

• Parent or Cost — the Type that names the benefit, measure or cost. Benefit Values and Measure Values use Parent. Cost Values use Cost.
• Parent Type — an Attribute Assignment column on Benefit Values only. The cell holds Benefit for a benefit, or Measure for a measure.
• Series — an Attribute Assignment column. The cell holds the series name. For benefits and measures it is Plan, Actual or Forecast. Advanced measures also take CMO and FMO. For costs it is Budget, Forecast or Actual.
• Start Date — an Attribute Assignment column. The cell holds the date of the value. Amplify moves the date to the first day of its month.
• Value — an Attribute Assignment column. The cell holds a number.

A value for a date that already has a value in the same series replaces it. For the date and period rules of each import data type, see Importing Benefit Values and Importing Cost Values.

For the columns each import data type takes, see Importing Initiatives, Importing Benefits, Importing Benefit Values, Importing Measures, Importing Costs, Importing Cost Values, Importing RAID Items, Importing Tasks and Importing Users. For an overview of importing, see Importing Data.