System Roles

Administrator

Process Designer

Creating Excel Templates to Update Project Metadata

Create a Smart Excel template using the Process Document - Smart Excel <version>.xltx base template and Accolade field codes to create a template that allows deliverable and activity owners to update project metadata, such as gate dates and project start and end dates, within their projects. Creating the template involves designing and laying out the display worksheets that users of the template interact with, as well as creating the appropriate mappings for any information that is pulled from Accolade.

Administrators and Process Designers create the templates, Process Designers add the templates to models, and team members on projects use the template to complete and update project data within Accolade.

Base Smart Excel templates on the Process Document - Smart Excel <version>.xltx file available on the Base Templates Reference Page. See Templates to Update Project Data Overview (Smart Excel) for more information about the template.

The procedure below describes how to add project metadata to an Excel template that displays and updates values when published to Accolade. A single template can also contain metrics, matrices, and metrics in related projects.

To setup an Excel template that updates metadata:

  1. Open the Process Document - Smart Excel <version>.xltx file and save a copy to your computer, using a file name that clearly identifies the purpose of the template.

Ensure that the master Process Document - Smart Excel <version>.xltx file is kept in a safe place, such as the template library, with no modifications to it.

  1. (Optional) Hide the SGM_MetricTransfer, SGM_RelatedProjectsMetrics, SGM_DeliverablesActivities, and SGM_Notes worksheets, as none are required for a metadata update; however, do not delete them.
  2. On Sheet1, create the display worksheet that users of the template interact with, identifying and noting the purpose of the cells throughout the layout.
    • Cells that only display downloaded data from Accolade.
    • Cells that only upload changed values to Accolade.
    • Cells that contain information and data that is not sent to or received from Accolade. For example, label cells.

Enter all the labels, column headings, background color, formulas, and so on, into the cells that do not receive or send data to Accolade to verify that the worksheet looks as you intend it to for users.

It can be useful as you are laying out the display worksheet to name the upload and download cells to more clearly identify them in the layout prior to entering the field codes to establish the data connection with Accolade.

You can add additional display worksheets, as necessary.

  1. In the SGM_Metadata worksheet, enter one or more of the following metadata field names into the Name column. These metadata names represent project metadata that users can update using the spreadsheet.
    • ActivityDeadline
    • ActivityPlannedFinishDate
    • ActivityStart
    • ActivityStatusID
    • DeliverableDeadline
    • DeliverablePlannedFinishDate
    • DeliverableStart
    • DeliverableStatusID
    • ProjectStartDate
    • ProjectEndDate
    • ProjectGateDate-(gate number) **Enter a dash and the gate number (no spaces) after the name.
    • ProjectName
    • ProjectID **This is the displayable project ID, not the SysProjectID
    • ProjectDescription

You can add other metadata names to the Name column for display on the display worksheet; however, only the metadata names listed above can be updated using the template. For the complete list of metadata names, see Accolade field codes.

  1. (Optional) Rename Sheet1 to an appropriate, meaningful name for the display worksheet for users completing the template.
  2. In the SGM_Metadata worksheet, enter Y or Yes in the Publish will use New Value column to update the project with values in the New Value column when a new version of the file that uses the template is published in Accolade.
  3. Create cell references between the display worksheet (originally Sheet1) and the SGM_Metadata worksheet.

     

    Cell Purpose on Display Worksheet Instructions

    Cells that only display downloaded data from Accolade

    On the display worksheet, in the cells that display the current metric value when the document is downloaded and opened, create cell references to the corresponding "current value" cells in the SGM_Metadata worksheet.

    Cells that document owners change to upload back to Accolade

    On the SGM_Metadata worksheet, in the "new value" cells, enter cell references to the cells on the display worksheet where the document owner enters new values to upload back to Accolade.

  4. Protect the display worksheet.

Protecting the display worksheet keeps users from overwriting cells that contain references or formulas.

Important! Do not protect the entire workbook. The SGM_ worksheets must remain unprotected for the download and upload between Accolade to work correctly.

  1. In the display worksheet, remove the lock on the cells that users use to update metric values.
  2. Hide the SGM_Metadata worksheet, as this is a worksheet that users interacting with the template do not need to see.

Prior to saving, select the cell in the display worksheet where a template user typically starts typing in the template to default the cursor to that cell when the template is opened.

  1. Save the file and add the template to the Template Library.

Notes:   

  • Sopheon recommends that all SGM_ worksheets should be hidden prior to publishing for use in a process model, to prevent users from making inadvertent changes. Do not rename these worksheets, and do not delete them from the file.

  • You can add the SGM_Metadata worksheet to any version of a Smart Excel template. It is not necessary to update the template to the latest version to update the listed types of metadata. Add columns headings in the first row of the sheet from left to right: Name, Current Value String, Current Value Number, Current Value Date, Publish will use New Value, New Value.
Learn More: