PowerSchool ERP allows your users to upload budget ledger data directly into the appropriate system tables from Microsoft Excel spreadsheets. To facilitate this feature, you need to create spreadsheet templates which correlate the spreadsheet columns with the appropriate database fields for the budget ledger record.
Each spreadsheet template consists of a header and then mapping information tying the spreadsheet columns to specific database fields. The header identifies the type of data that will be mapped: expenditure/revenue budget or project budget.
As system administrator, you can either allow user access for updating spreadsheet templates by assigning the appropriate security resource, or you can handle this setup yourself.
Menu Path: System Administration > All > Reference Tables > Spreadsheet Templates
Plan Template Formats
When setting up template records, be sure to get feedback on how your colleagues format their budget ledger spreadsheets. This will help establish standards for the applications the template formats support.
You will need to consider the following questions for each template you set up:
What name should be assigned to the template?
Choose a descriptive Template Name. This is what the user sees when selecting the template for an Excel spreadsheet upload.
What file format should apply to the template?
Your selection of File Format determines the way users will need to save their spreadsheets in Excel. If the template is Comma-Delimited, then users need to save their spreadsheets with the .csv file extension. For a Tab-Delimited template, spreadsheets must have a .txt file extension.
What database fields need to be mapped?
No matter what type of template you create, there will be several fields that must be included in the source spreadsheet and therefore need to have columns assigned in the template format. Beyond the required fields, there are likely other pieces of data you need to map for a given template. This depends on your record-keeping and data entry practices.
What column position will each field occupy in a given spreadsheet row?
This is another important topic to coordinate with your user base. Once you know which fields to map, you will assign each one to a spreadsheet column. Note that there cannot be any blank columns within an import row, so your template needs to follow this rule. If six fields are defined, then the template must map six consecutive columns.
Add a Spreadsheet Template Format
-
Select System Administration > All > Reference Tables > Spreadsheet Templates to display the Spreadsheet Templates page.
-
Click Add New.
-
In the Header Information section, complete the Template Name, Template Type, and Fire Format fields, all of which are required.
-
Click OK.
-
For the template type's required columns, which display by default, assign the appropriate Column Numbers where the information will actually appear in the spreadsheet.
-
Map the remaining fields users need defined in the template. For each field, select the appropriate Column Name, and then assign the spreadsheet Column Number.
-
You can enter the fields in any order.
-
Be sure to assign consecutive Column Numbers to the fields you are defining. For instance, if the template includes six fields, use six sequential spreadsheet columns.
-
-
Click OK to save the template format.
Update an Existing Spreadsheet Template
-
Select System Administration > All > Reference Tables > Spreadsheet Templates to display the Spreadsheet Templates page.
-
In the Search Criteria section, enter criteria identifying the records to list, and then click Find.
-
In the List section, select the template to update, and then click OK to display the Upload Template page.
-
In the Header Information section, you can change only the File Format field.
-
Change the Column Number fields as needed.
-
Remember, each Template Type has required fields that must be mapped. For budget ledger records, only Budget Unit and Account Code are required.
-
To remove a non-required field from the template, select the appropriate row, and then click Delete Row.
-
Be sure to keep the template's fields associated with sequential spreadsheet columns.
-
-
Click OK to save the template changes.
Copy a Spreadsheet Template Format
Some template records may differ only in terms of a few minor settings. The Spreadsheet Templates page lets you copy formatting from one record into a new one. You can then make changes without having to re-enter the template's other data.
-
Select System Administration > All > Reference Tables > Spreadsheet Templates to display the Spreadsheet Templates page.
-
In the Search Criteria section, enter criteria identifying the records to list, and then click Find.
-
In the List section, select the template you want to copy, and then click Copy on the Action Bar to display the Copy Template page.
-
In the New Template Name field, enter a description for the template you are creating from the indicated source template. Character/25
-
Click OK to create the new template and return to the Spreadsheet Templates page.
-
Change to the new template as needed following the update procedure above.
Delete Spreadsheet Templates
-
Select System Administration > All > Reference Tables > Spreadsheet Templates to display the Spreadsheet Templates page.
-
In the Search Criteria section, enter criteria identifying the template to delete, and then click Find.
-
In the List section, select the template.
-
Click Delete.
-
In the Confirmation dialog, click Yes.
Generate the Spreadsheet Template Report
-
Select System Administration > All > Reference Tables > Spreadsheet Templates to display the Spreadsheet Templates page.
-
In the Search Criteria section, enter criteria identifying the records to list, and then click Find.
-
Select the desired spreadsheet template, and then click the action bar's Print .
-
In the Print window, specify how to generate the report, and then click OK. The report's default file name is upload.rpt.
Fields
The Add Spreadsheet Template page has two sections: Header Information and column information (untitled). Following are descriptions for each section's fields.
Header Information Section
Use this section to identify the spreadsheet template format:
|
Field |
Description |
|---|---|
|
Template Name |
Description of the spreadsheet template format. This is what users see when selecting the template to apply during the Excel upload procedure, so make sure you assign a significant title. Character/25 |
|
Template Type |
Setting indicating the type of spreadsheet information this template maps in the column information section. Your selection determines the available entries in the Column Name field. Select:
|
|
File Format |
Format convention for reading and uploading spreadsheets processed by this template. The template can only be applied to Excel spreadsheets saved with the correct file extension. Based on the File Format setting, the system discerns where one piece of data ends and the next one begins in a spreadsheet. Select:
|
Column Information Section (untitled)
Use this section to associate database columns with the spreadsheet columns where they will appear for files read by this template format:
|
Field |
Description |
|---|---|
|
Column Name |
Database field the system will upload based on reading the Excel spreadsheet. For each field you select, you assign the Column Number where the information is saved for all spreadsheets read by this template format. Based on the record's Template Type, some Column Name entries are required and must be mapped. For budget ledger record templates, Budget Unit and Account Code are required. After you complete the required fields, which display by default, you can select additional fields from the drop-down list. Determine the additional fields to map for a given template by consulting the appropriate personnel within your organization. |
|
Column Number |
Column position within the Excel spreadsheet row for the database field identified in the Column Name field. For example, if the Account Code appears in the fifth spreadsheet column, you would select 5 - E.
|