Smartstaff, administrator
3,315
edits
Changes
→Field Mappings on the Multi-File Upload Field
===What and Why===
The Excel Parser feature gives your applicants the ability to complete their budgets using the Microsoft Excel application offline, and then the applicant can upload the completed Excel spreadsheet into your system later. The data contained within the spreadsheet will be used to populate custom fields in your system, or the data can be used to generate multiple activities. This feature may be a better option than using the '''Advanced Data Table''' custom field as this feature is well suited for complex budgets or tables of information with many columns.
This article will show you how to: set up Platform3 and an Excel spreadsheet to work with the Excel Parser process so your community can upload spreadsheets to populate their applications and generate activities.
You need to have '''Global Administrator''' access to setup or edit the elements used in the Excel Parser process. You also need to have '''Microsoft Excel''' to set up the spreadsheet that users will complete and upload.
===The process from the applicants perspective===
# Log into your instance and download the MS Excel template. [[File:Excel-parser-2020-instructions.png|thumb|none|500px|See instructions with a link to download the template the applicant needs to complete]] # Complete and save the template. [[File:Excel-parser-2020-spreadsheet.png|thumb|none|500px|A sample completed Excel spreadsheet]] # Upload the template. [[File:Excel-parser-2020-upload.png|thumb|none|500px|Uploading the completed Excel spreadsheet]] # Information in the template is parsed and mapped fields are populated and activities are created. [[File:Excel-parser-2020-activities.png|thumb|none|500px|Activities created when spreadsheet uploaded]] # Optionally, you can delete the previously uploaded spreadsheet, then upload a new spreadsheet to update the information already in your system. [[File:Excel-parser-2020-delete.png|thumb|none|500px|Select the uploaded spreadsheet and click the delete button then upload a new spreadsheet to update your values in the system. Note: new values appear on refresh or save.]]
===Preparing the Spreadsheet===
===Naming Cells===
Platform3 will need to know which cells you want to parse so we need to name those cells so we can refer to them later. We will do this by naming cells in Excel. You can name individual cells and groups of cells. In this example we are going to name individual cells only and use those to create activities.
# Give a name like '''Salaries''' to the header cell you want to parse into Platform3. I will use that named cell to populate a specific custom field value later.
# You can define the cell name in Excel by clicking on the desired cell, go to the '''Formulas''' tab and click '''Define Name'''. Once a name has been defined for a cell you can see it in the '''Name Box''' when the desired cell is selected. [[File:Excel-parser-2020-spreadsheet-namebox.png|thumb|none|500px|Defining a name for a cell, so we can refer to it later. The defined name shows up in the name box when a cell or group of cells is selected.]]
# Give a name like '''StartingSalaries''' to the first cell you want to parse into Platform3. I could give names to all the cells in this row but instead I chose to name the first cell and then I will refer to the other cells in the row using the syntax '''"col_1"''', '''"col_2"''', etc.
# You may wish to name other cells, for example I also named '''SalariesHeader''' and '''SalariesSubTotal''' so I could map to those as well.
===Locking The Excel Spreadsheet===
Now that we have set up our Excel spreadsheet and named the cells we want to parse, we need to lock the spreadsheet. We will lock the entire spreadsheet except our desired cells so the applicant cannot add rows, columns or otherwise modify the spreadsheet as that could cause the parse process to fail.
# Click the box to the left of column A (in between column A and row 1). This will select all cells. [[File:Excel-parser-2020-spreadsheet-selectall.png|thumb|none|500px|Select all cells in the spreadsheet by clicking between column A and row 1]]
# Right click the same box – select '''Format Cells''' then click the '''Protection''' tab.
# Make sure the "Locked" checkbox is checked. [[File:Excel-parser-2020-spreadsheet-locked.png|thumb|none|500px|Make sure all cells are locked]]
# Click '''OK''' (these first few steps make sure that all cells are locked. See steps 5-6 for unlocking individual cells).
# Right click just the cell(s) you wish to have editable by the end user (i.e. unlocked).
# Select '''Format Cells''' then click the '''Protection''' tab. Then un-check the '''Locked''' box. This will '''unlock''' the cells that you want the end user to be able to edit.
# Click the '''Review''' tab, then click '''Protect Sheet''' (In older versions of Excel, this may be found under the '''Tools''' menu, then '''Protection''').
# Only allow the user to select unlocked cells and enter a password in the prompt to be used to un-protect the sheet.
===Upload the Spreadsheet to a SmartFolder===
Now that we have our spreadsheet setup for the applicant to use, we need to put that spreadsheet somewhere the applicant can get it. We will put the spreadsheet in a SmartFolder.
# In your instance of Platform3 go to the '''Menu Icon''' > '''SmartFolders'''.
# Create a new folder to hold the spreadsheet and call it something like '''Excel Parser''' (You could also place this in an existing folder). [[File:Excel-parser-2020-smartfolders.png|thumb|none|500px|Upload Excel spreadsheet template to a SmartFolder]]
# Navigate to the Excel spreadsheet file and upload it into your SmartFolder
# Once the file has been uploaded, under '''File Options''' change the drop down to '''View URL''' this will provide you with the link to the file we just uploaded. [[File:Excel-parser-2020-smartfolders-url.png|thumb|none|500px|View URL to see the path to the Excel spreadsheet template]]
# Copy the link to the spreadsheet into notepad and save it. We will use this link later. The link should look something like https://alias.smartsimple.com/files/1234567/f123456/activity-excel-parser.xlsx
===Create a Download Link===
Once you have uploaded the spreadsheet to a SmartFolder you need to provide a link for your applicants so they can download the spreadsheet template you expect them to complete. You could put a link in the instructions for the “Upload - Multiple Files Storage” custom field we will create later, but I have chosen to create a separate instructions field for greater visibility. Here is how you can create an instructions field with a link to download the Excel spreadsheet that the applicant will download and complete.
# Click the '''Custom Fields''' link.
# Create a new custom field of the type '''Display - Instructions''' and call it something like '''Budget Instructions'''.
# For the content enter something like the following: [[File:Excel-parser-2020-instruct-code1.png|thumb|none|500px|Content for the plain text editor of a Display – Instructions custom field]] # Now replace the link above that looks like /files/1234567/f123456/activity-excel-parser.xlsx with the link to the spreadsheet you uploaded to a SmartFolder in our previous steps. Remember to remove this part https://alias.smartsimple.com from your link. Always use relative links inside Platform3 to ensure they function correctly and work across environments.
# Save the custom field.
===Field Mappings on the Multi-File Upload Field===
Previously we set up a field for the applicant to upload the completed spreadsheet and we created a spreadsheet with named cells or groups of cells. Now we need to tell your system what data we want to parse, and what we want to do with that data. Typically you will either populate a custom field with one or more values from the spreadsheet or as in this example we will create level 2 activities based on rows from the completed spreadsheet.
# On the Multi-File Upload custom field we set previously with '''Enable Excel Parsing''' will see a new section near the top called '''Field Mappings'''. This is where we define what will be parsed to where.
#If you have already created the activity custom fields you want to map to, you will need to get the custom field IDs for each. Otherwise you need to create the desired activity custom fields. [[File:Excel-parser-2020-fieldid.png|thumb|none|500px|Custom field IDs are found on each custom field's configuration page near the top]] #If you followed the instructions above you can place the following into the '''Field Mappings''' input and replace Custom Field IDs with your desired custom field IDs. [[File:Excel-parser-2020-field-mapping-code.png|thumb|none|500px|Code entered into Field Mappings]]
# Click '''Save'''.
Now you can test out the entire process from the applicants point of view and watch the activities get created and populated with data upon upload of the completed spreadsheet.
===Updating Existing Custom Fields and Activities===
