Set Targets & Add Results Excel Download & Upload

This page covers:

Overview: Set Targets and Add Results offline using an Excel template

Amp Impact allows users to report Targets and Results data offline by downloading data to an Excel file and then uploading the Excel file(s) to a Project once connectivity is restored. To use this functionality, the user must first download the templates from the Set Targets and/or Add Results component(s), input the data to the downloaded template(s), and then upload the template(s) back to the Set Targets / Add Results component(s). The uploaded data will then reflect in the Set Targets / Add Results components in the Project record.

LIGHTNING WEB COMPONENT Download Results data to an Excel template

1. To download Results template for a specific Reporting Period and Geographic Area, the user needs to click the “Download Results” button.

2. On the click of the “Download Results” button, a pop-up opens. The ‘Generate’ pop-up will ask the user to confirm generating the excel sheet for the particular Reporting Period and Geographic Area selected. In this pop-up, the user can either click “Generate” or “Cancel”.

3. If there is at least one Project Indicator linked to a Geographic Area and Reporting Period, then selecting “Generate” will generate the Results template and user will get an option to View and Download the generated Results template. On this popup,

• Selecting “View” will help users to view the template. It opens a new tab where users can access the preview the file before downloading it.

• Selecting “Download”’ will prompt the download of the excel file onto user’s system.

4. The generated Results excel file will all be stored as ContentVersion files under the Files related list on the relevant Reporting Period. 

• ContentVersion record’s Created Date is the time stamp of download. 

• ContentVersion record’s Owner field displays the name of the user who downloaded the file. 

• If a user downloads an excel file multiple times for the same Geographic Area and Reporting Period, these ContentVersion files are updated to reflect the last download date, and the owner of the ContentVersion record is updated to the user who last downloaded the Results. 

• The user may access all of the versions that have been downloaded by selecting ‘View previous versions’.

• The updated ContentVerion file is posted on Chatter of the related Reporting Period record. 

5. The downloaded Results template will have 2 tabs:

• The first tab will be labeled as “Instructions” - this tab will outline how to enter data in a compatible format to upload the document onto the AddResults page.

• The label of second tab will reflect the Reporting Period and Geographic Area for which the Results template is downloaded. It’s format will be - ‘[Reporting Period label] - [Geographic Area name]’.

  • Below the header on the second tab, the date and time of download, and the name of the user who downloaded the file are also displayed. 

6. The Result values can be added to the second tab of the downloaded Result template spreadsheet.

LIGHTNING WEB COMPONENT Upload Results from an Excel template

Once the results have been entered in the downloaded Results template the user can now upload that template to the Project.

1. Navigate to the Add Results page on the Project record and click the Upload Results button (as highlighted in the screenshot)

2. In the Upload Results popup, click on the Attach FIle button and select the Results template on user’s system

a. Alternatively, the user can drop the Result template in the drop zone (indicated by dotted box) inside the popup

3. If the selected file is validated, a green check will be displayed next to the attached file.

a. If the selected file is not validated, a red cross will be displayed next to the attached file and a list of error messages will be displayed below the  attached file.

b. Please correct the errors in the Results template and re-upload that file to upload Results.

4. After validation of the template, click on the Upload button to uplod the reported Results to the Project

5. Once the user clicks on the Upload button, the system will upload the Results and display the status of Results. The details include:

a. Number of successfully uploaded Result records

b. Number of Result records not uploaded

6. The user can choose to download the Success log by clicking on the “View Successes” button

a. The success log excel file will all be stored as ContentVersion files under the Files related list on the relevant Reporting Period. With each generation of success log, these ContentVersion files are updated to reflect the last upload date, and the owner of the content version record is updated to the user who last  uploaded the results. ContentVersion records also allow users to access previous versions that have been uploaded by selecting ‘View previous versions’.

b. The updated ContentVerion file is posted on Chatter of the related Reporting Period record.

7. If some Results have not been uploaded, please click on “View Errors” to download the Error log to user’s system

a. The error log excel file will all be stored as ContentVersion files under the Files related list on the relevant Reporting Period. With each generation of error log, these ContentVersion files are updated to reflect the last upload date, and the owner of the content version record is updated to the user who last  uploaded the results. ContentVersion records also allow users to access previous versions.

b. The updated ContentVerion file is posted on Chatter of the related Reporting Period record.

c. Refer to the errors listed in the Error log to correct them.

d. Once all errors have been rectified, please upload the corrected Results to the Project using the same steps as before.

8. Once the Results have been uploaded, click on the Close button.

9. The Add Results page will be refreshed and display the uploaded Results.

VISUALFORCE PAGE Download Targets data to an Excel template

1. To download Targets for Project Indicators for a specific Reporting Period and Geographic Area, the user clicks the “Download” button.

2. On Click of the Download button, a pop-up opens. The Generate pop-up will ask the user to confirm generating the excel sheet for the particular Reporting Period and Geographic Area selected. In this pop-up, the user can either click ‘Generate’ or ‘Cancel’.

a. If there is no project indicator linked to a Geographic Area and Reporting Period, then selecting ‘Generate’ will prompt an error message. 

b. Selecting ‘Cancel’ will exit from the ‘Generate’ pop-up. The page will remain on the SetTargets page.

3. If there is at least one project indicator linked to a Geographic Area and Reporting Period, then selecting ‘Generate’ will prompt a Confirm pop-up.

4. The Confirm pop-up will ask the user to confirm the download of the excel sheet for the particular Reporting Period and Geographic Area selected. In this pop-up, users can either click ‘Confirm’ or ‘Cancel’. 

a. Selecting ‘Cancel’ will close the pop-up and the user will remain on the SetTargets page.

b. Selecting ‘Confirm’ will prompt the download of the excel file onto the SetTargets page. 

5. Once ‘Confirm’ has been selected, the file will download. On opening of the file, 2 tabs will be displayed 

a. The first tab will be labeled as, “Instructions’ - this tab will outline how to enter data in a compatible format to upload the document into the SetTargets page

b. The second tab will change dynamically to reflect the Reporting Period and Geographic Area, and will be formatted as such - ‘[Reporting Period label] - [Geographic Area name]’. Below the header, the date and time of download, and the name of the user who downloaded the file will be displayed.

6. The downloaded Targets excel file will all be stored as ContentVersion files under the Files related list on the relevant Reporting Period. If a user downloads an excel file multiple times for the same Geographic Area and Reporting Period, these ContentVersion files are updated to reflect the last download date, and the owner of the ContentVersion record is updated to the user who last downloaded the Targets. The user may access all of the versions that have been downloaded by selecting ‘View previous versions’.

VISUALFORCE PAGE Upload Targets from an Excel template

1. Once the Targets have been downloaded for a given Reporting Period and Geographic Area, the user can open the downloaded excel to view and add Targets to upload (See the section above on downloading Targets as an excel file for details),  

2. To upload the Targets, data may be added to the 2nd tab of the Excel spreadsheet. The following may be edited:

   1. The number of tabs

   2. The values in the input cells which are indicated by the blue background

   3. The cells in the Comments column corresponding to each project indicator 

      1. Currently, upserted comments via Excel upload will not reflect in the Indicators table. This limitation will be addressed in a future release.  

      2. Comments added via Excel will not upload if the corresponding project indicator has no target or result value. For example, if a user uploads a comment to a project indicator that has no target or result input, then the comment will not be upserted. If the project indicator does have Targets or Results, then the comment will be upserted.

3. The following portions of the excel sheet may not be edited in order to ensure they are compatible with the Upload feature

   1. The header text on the tab with the downloaded data

   2. The names of the column headers in the downloaded tab

   3. The name of the downloaded tab

   4. The existence of one tab with the Reporting Period and Geographic Area as the label

4. Once data has been entered into the excel sheet, the user must navigate to the SetTargets page and click the Upload button in the top right corner of the screen

• Step 1: Attach File - Selecting the Upload button will prompt a popup to allow the user to attach the file to be uploaded for that particular Geographic Area and Reporting Period

  • The user may click Cancel at any time and return to the SetTargets page, with no records updated

  • When choosing a file, ensure the file type is .xlsx. Clicking Attach will prompt the next popup.

• Step 2: Upload Targets - Once the upload popup appears, click on the Upload button. This popup will confirm the Reporting Period, Geographic Area, and Project name.

• Step 3: Confirm Upload - Once the upload is complete, a “Confirm Upload” popup will appear. The popup will ask the user to confirm that the records uploaded should update the Targets for the chosen Reporting Period and Geographic Area 

  • Selecting “No” will cancel out of the popup, with no records updated

  • Selecting “Yes” will confirm the upload and the records will update

• Step 4: Operation Complete - Upon selecting “Yes” in Step 3, the user will receive a popup that reads “Operation Complete.” This popup includes the number of successfully updated records and the number of errors that occurred. On this popup, the user can select from three options:

  • View Successes shows the user the successful uploads. Clicking on the View Successes button will download an Excel file with all of the indicators that were successfully uploaded. This file will download on the SetTargets page, and a record on ContentVersion will also be created.

    • If there were no successes, the View Successes button will not display in Step 4

    • The uploaded success log Excel file will all be stored as ContentVersion files under the Files related list on the relevant Reporting Period. If a user uploads an Excel file multiple times for the same Geographic Area and Reporting Period, these ContentVersion files are updated to reflect the last upload date, and the owner of the ContentVersion record is updated to the user who last uploaded the Targets. The user may access all of the versions that have been uploaded by selecting ‘View previous versions’.

    • The rows in the downloaded success log will be ordered by alphabetically ascending Indicator definitions.

  • View Errors shows the user an error log of records that were not successfully uploaded and the associated errors. Clicking on the View Errors button will download an Excel file with all of the indicators that were unable to be uploaded with a column explaining what the error was. This file will download on the SetTargets pages and a record on ContentVersion will also be created.

    • If no errors occurred, then the View Errors button will not display in Step 4

    • The uploaded error log excel file will all be stored as ContentVersion files under the Files related list on the relevant Reporting Period. If a user uploads an excel file multiple times for the same Geographic Area and Reporting Period, and if some errors are generated on upload, these ContentVersion files are updated to reflect the last upload date, and the owner of the ContentVersion record is updated to the user who last uploaded the Targets. The user may access all of the versions that have been uploaded by selecting ‘View previous versions’.

    • The rows in the downloaded error log will be ordered by alphabetically ascending Indicator definitions.

  • Close closes the popup once the success/error logs have been downloaded. This will automatically refresh the SetTargets page and display the newly updated records.

VISUALFORCE PAGE Download Results data to an Excel template

1. To download Results template for a specific Reporting Period and Geographic Area, the user needs to click the  “Download Results” button. 

2. On the click of the “Download Results” button, a pop-up opens. The ‘Generate’ pop-up will ask the user to confirm generating the excel sheet for the particular Reporting Period and Geographic Area selected. In this pop-up, the user can either click “Generate” or “Cancel”.

a. If there is no project indicator linked to a Geographic Area and Reporting Period, then selecting ‘Generate’ will prompt an error message. 

b. Selecting ‘Cancel’ will exit from the ‘Generate’ pop-up. The page will remain on the AddResults page.

3. If there is at least one project indicator linked to a Geographic Area and Reporting Period, then selecting ‘Generate’ will prompt a ‘Confirm’ pop-up. The ‘Confirm’ pop-up will ask the user to confirm the download of the excel sheet for the particular Reporting Period and Geographic Area selected. In this pop-up, users can either click ‘Confirm’ or ‘Cancel’.

a. Selecting ‘Cancel’ will close the pop-up and the user will remain on the AddResults page.

b. Selecting ‘Confirm’ will prompt the download of the excel file onto the AddResults page.

4. Once ‘Confirm’ has been selected, the file will download. OnOpen of the file, 2 tabs will be shown:

a. The first tab will be labeled as, “Instructions’ - this tab will outline how to enter data in a compatible format to upload the document onto the AddResults page

b. The second tab will change dynamically to reflect the Reporting Period and Geographic Area, and will be formatted as such - ‘[Reporting Period label] - [Geographic Area name]’. Below the header, the date and time of download, and the name of the user who downloaded the file are also displayed

5. The downloaded Results excel file will all be stored as ContentVersion files under the Files related list on the relevant Reporting Period. If a user downloads an excel file multiple times for the same Geographic Area and Reporting Period, these ContentVersion files are updated to reflect the last download date, and the owner of the ContentVersion record is updated to the user who last downloaded the results. The user may access all of the versions that have been downloaded by selecting ‘View previous versions’.

VISUALFORCE PAGE Upload Results data from an Excel template

1. Once the Results have been downloaded for a given Reporting Period and Geographic Area, the user can open the downloaded excel to view and add Results to upload (Please see the section above on downloading Results as an excel file)

2. To upload the results, data may be added to the 2nd tab of the Excel spreadsheet. The following may be edited:

   1. The number of tabs

   2. The values in the input cells which are indicated by the blue background

   3. The text in the comments column per Project Indicator where Data_Type__c = Input (Comments may not be uploaded for Aggregated/Calculated Project Indicators on AddResults)

3. The following portions of the excel sheet may not be edited in order to comply with the compatibility of re-uploading

   1. The header text on the tab with the downloaded data

   2. The names of the column headers in the downloaded tab

   3. The name of the downloaded tab

   4. The existence of one tab with the Reporting Period and Geographic Area as the label

4. Once data has been entered into the excel sheet, the user must navigate to the AddResults page and click the Upload button in the top right corner of the screen

• Step 1: Attach File - Selecting the Upload button will prompt a popup to allow the user to attach the file to be uploaded for that particular Geographic Area and Reporting Period.

  • The user may click on “Cancel” at any time and return to the AddResults page, with no records updated.

  • When choosing a file, ensure that the file type is ‘xlsx.’. Clicking “Attach” will prompt the next popup. At this step, an error message will appear on click of “Attach” if the uploaded document is not in the .xlsx format, in which case, the user must revisit the document to be uploaded and edit it to make it compatible.

• Step 2: Upload Results - Once the upload popup appears, click on the “Upload” button. The popup will confirm the Reporting Period, Geographic Area, and project name. 

• Step 3: Confirm Upload - Once the upload is complete, a “Confirm Upload” popup will appear. The popup will ask the user to confirm that the records uploaded should update the Results for the chosen Reporting Period and Geographic Area 

  • Selecting “No” will cancel out of the popup, with no records updated

  • Selecting “Yes” will confirm the upload, and the records will update

• Step 4: Operation Complete - Once the user has selected “Yes,” there will be a popup that reads “Operation Complete,” with the number of successfully updated records and the number of errors that occurred. On this popup, the user can select from three options:

  • View Successes shows the user the successful uploads. Clicking on the View Successes button will download an Excel file with all of the indicators that were successfully uploaded. This file will download on the AddResults page, and a record on ContentVersion will also be created.

    • If there were no successes, the View Successes button will not display in Step 4

    • The uploaded success log Excel file will all be stored as ContentVersion files under the Files related list on the relevant Reporting Period. If a user uploads an Excel file multiple times for the same Geographic Area and Reporting Period, these ContentVersion files are updated to reflect the last upload date, and the owner of the ContentVersion record is updated to the user who last uploaded the Results. The user may access all of the versions that have been uploaded by selecting ‘View previous versions’.

    • The rows in the downloaded success log will be ordered by alphabetically ascending Indicator definitions.

  • View Errors shows the user an error log of records that were not successfully uploaded and the associated errors. Clicking on the View Errors button will download an Excel file with all of the indicators that were unable to be uploaded with a column explaining what the error was. This file will download on the SetTargets pages and a record on ContentVersion will also be created.

    • If no errors occurred, then the View Errors button will not display in Step 4

    • The uploaded error log excel file will all be stored as ContentVersion files under the Files related list on the relevant Reporting Period. If a user uploads an excel file multiple times for the same Geographic Area and Reporting Period, and if some errors are generated on upload, these ContentVersion files are updated to reflect the last upload date, and the owner of the ContentVersion record is updated to the user who last uploaded the Targets. The user may access all of the versions that have been uploaded by selecting ‘View previous versions’.

    • The rows in the downloaded error log will be ordered by alphabetically ascending Indicator definitions.

  • Close closes the popup once the success/error logs have been downloaded. This will automatically refresh the SetTargets page and display the newly updated records.