KBA-01628: Setup for Change Item Budget Entry Import from Excel

Question:

How do I set things up to be able to import Budget Entries for a Change Item from a Microsoft Excel Workbook?

Answer:

Lots of setup is required, but if you already import budgets into BFA you are most of the way there. Start with your Microsoft Excel Import workbook and its corresponding BFA Import Map.

Prerequisites:

  • You will need an Import map (.xml file). Import mapping files can be created through the Import Wizard tool on the BFA workbook. For assistance, contact Support.
  1. Add a copy of your Import map to the Templates library as a BFA Budget Import Map template.
  2. Decide if you want to drag and drop the Import workbook (probably with data) to the Change Order each time or if you want to create a template from the Import workbook. If you decide to add an “empty” copy of the Import workbook to the Template library:
    1. Make sure there are no dollar values in the workbook; however, there can be plenty of rows and columns and calculations.
    2. Make sure you have a “SpitfireImportData” sheet in the workbook (the name can vary using the Import Map features).
    3. You probably want to start with a standard Attachment template that is manually selected and attached to the Change Order so make the template type Attachment.
  3. Review your current configuration of the VXLConfig rules. You may need to add or change some of the rules. For example, if you want to automatically add allocations immediately after the import, you would set the ImportCIRunAllocations rule to Always.
    Note: the VXLConfig | ImportCIEnabled rule must be checked for the Change Order Doc type.

Testing

  1. On a Change Order document, add a Change Item.
  2. Attach your CI import workbook to the Change Item
    1. Either use the Create from Attachment icon to create a file from the Import template and then add data to the workbook, save and close
      OR
    2. Drag and drop an Import workbook to the Attachment tab.
  3. A message will ask if you want to “load it now?”  Click Load Now. Data from the workbook will be copied to the Items tab.

Additional Comments:

  • The Import template can be based on an existing Microsoft Excel budget import workbook.
  • The Import template can be based on an existing Microsoft Excel Change Item budget entry workbook.
  • The template cannot have any project-specific data in it; however, it can have any number of rows and columns and can include calculations.
  • The template should include Cost Codes and Account Categories. sfPMS will use Cost Codes, Account Categories and logic to determine how to import your budget entries so they appear with the correct cost type.
  • The template must include a consolidation sheet. By default, this sheet is named SpitfireImportData. The consolidation sheet must match the Import map.
  • See KBA-01732 for how to do the actual import

 


KBA-01628; Last updated: March 28, 2019 at 21:40 pm