Excel Upload/Download Template
- Harshula Jhala
- Wilmarié Moreland
The Excel Upload/Download feature allows you to download Budget and Line Item records to an Excel sheet, make changes offline, and re-upload the templates.
Download a Budget & Line Items
To download a budget with its corresponding child financial line items, click on the 'Download' button on the relevant Budget record. The budget Excel will download directly on the page.
When opening the downloaded Excel file, there are three tabs:
Tab 1 - 'Instructions': this tab outlines how to enter data in a compatible format to upload the data onto the Budget page. All of the instructions are controlled by custom labels and can be edited to include customized or additional instructions.
Tab 2 - 'Reference List': this tab provides all possible values for any picklist or relationship fields that are included on Tab 3.
Tab 3 - 'Budget name': this tab displays a template with column fields from the Financial object. If there is existing data in the Budget record, it will display here; otherwise this will download as a blank template.
The following may be edited in the Excel sheet:
The number of tabs, and
The values in the input cells which are indicated by the blue background in Tab 3.
Opening Tab 3 displays a header with the name of the project and the name of the budget. The row just below this header displays the date of download and the name of the user who downloaded the file.
The fields Type__c, Category__c, and Subcategory__c are picklist fields with default values that come out of the box. Within the excel file, these picklist values can be accessed on the 'Reference List' tab. Values for custom picklists can also be accessed on the 'Reference List' tab.
The lookup field Activity__c is optional for the user to fill out in the downloaded spreadsheet to be uploaded back onto the Budget. The master-detail field Reporting_Period__c is required and must be filled out in order to successfully upload financial line items. Users must enter the desired value in the spreadsheet for all lookup/master-detail fields; these values can be accessed on the 'Reference List' tab. Values for custom lookup/master-detail fields can also be accessed on the 'Reference List' tab.
When a user downloads financials from the Budget record page, such that a formula field (e.g. ampi__Status__c) is added to ampi__BUDGET_EXCEL_COLUMNS fieldset on ampi__Financials__c, the financial Excel file displays the formula field as uneditable (i.e. with white background).
The following may not be edited for Tab 3 in order to ensure compatibility with the Upload feature:
Tab name
Header text
Names of the column headers
The existence of at least one tab with the Budget name as the label
All numeric data will display in US English format. Regardless of the currency locale enabled in Salesforce settings, all downloaded budgets will display in this format. Guidelines are provided in the Instructions tab to edit the Excel settings to view numeric data in the format of preference.
Upload a Budget & Line Items
In editing the downloaded Excel spreadsheet, users are able to update existing financial records and upsert - or create new - financial records. In the downloaded file, users are able to add/update records by entering data in the blue input cells.
Users are able to download a budget that does not have any child financial records. Upon downloading and subsequently opening the downloaded file for a budget with no financial line items, users will see a template to be used to upload financial records onto the budget. The first ten rows are marked as blue input cells.
Date and date/time fields must be inputted as YYYY-MM-DD and YYYY-MM-DD HH:MM:SS respectively for successful upload.
For example, date may be inputted as 2020-01-08 and date/time as 2020-01-08 02:26:00 in the appropriate cell.
Once data has been entered into the excel sheet, the user must navigate back to the Budget record page. On this page, click the Upload button.
Step 1 - Selecting the Upload button on the Budget page layout will prompt a popup for the user to attach the edited file to be uploaded. Ensure that the file type is ‘xlsx.’. Onclick of 'Upload' in this popup, an error message will appear if the uploaded document is not in the .xlsx format, or is an incompatible format. In this case, the user must revisit the document to be uploaded and edit it to make it compatible. If successful, clicking 'Upload' will prompt the next popup. The user may click Cancel at any time and return to the Budget page, with no records updated/upserted.
Step 2 - After a file has been selected in step 1, a new popup will ask users to confirm the upload. In this popup, users will have the option to click ‘Yes’ which will confirm the upload. Clicking on ‘No’ will close out of the step 2 popup and the page will return to the Budget page with no changes made.
Step 3 - The last popup encountered while uploading will read 'Operation Complete,' with the number of successfully updated records and the number of errors that occurred.
On this popup, there are several buttons:
'View Successes' - selecting this shows the user the successful uploads. Clicking on View Successes button will download an excel file with all of the financial items that successfully uploaded. This file will download on the Budget page.
If there are no successes, the View Successes button will not display in Step 3
'View Errors' - clicking on this button shows the user an error log of records that could not be successfully uploaded and the associated errors. Clicking on View Errors button will download an excel file with all of the financial items that were unable to be uploaded with a column explaining what the error was. This file will download on the Budget page.
Any values added to or updated in the formula field of the Excel file do not reflect in the formula field on the Financials object in Amp Impact. However, if a financial Excel file containing changes made to the formula field is uploaded to the Budget record, the file uploads successfully, without throwing any error messages in the error log Excel file.
If no errors occurred, the View Errors button will not display in Step 3
The user can download the success/error logs and then click on the 'Close' button in the step 3 pop-up. This will automatically refresh the Budget page. Upon navigating to the Related tab, the newly updated/upserted records will display.
The downloaded budget excel file, the success log, and error log will all be stored under the Files related list as contentversion files. With each download of a budget and each generation of a success and error log, these contentversion files are updated, while still allowing users to access previous versions.
If a budget record is approved/locked whereby no further edits should be made, clicking the Upload button will display an error message to notify users that the budget is locked from editing.
Validation
While processing the Excel file during upload, validation is built-in to check for:
invalid file type, (e.g. user selects a word document)
data entry (e.g. currency code entered in a number field),
metadata (e.g. user renames a column header).
Users are provided the full list of validation checks in the Instructions tab.
Ensure that the Budget Name is less than or equal to 31 characters to avoid an error during upload
Data Quality
If a user downloads a Budget Excel file and clears out existing data for a financial line item, then the upload process will clear information from the existing record. However, it will not delete the record.