In conjunction with PowerSchool’s Extended Schema, the Enhanced Collection Table (ECT) element allows you to create an unlimited number of sub-records tied to either your student or faculty record. This is useful for adding any kind of repeating data where you cannot predict the amount of data per response, for instance a list of medications, household members, or community service hours. This element requires that you first create a database extension and table. You can then link the ECT element to the new table.
Beyond the properties common to all elements, the ECT element has the following additional properties:
One-To-Many Table Extension
The dropdown list contains all the one-to-many extensions of the Students table (for student forms) or Users table (for teacher forms) in your database. Select the one that contains your data.
Once you select an extension, this dropdown list contains the tables in that extension. A collection table can only contain fields from a single database table.
Filter By Response (Optional)
Select the field within the database table that will hold the Response ID. Use in conjunction with the form property Clear Form on Submission to not display previously submitted additions to the ETC. Only those rows added during a given response will be associated with that response.
Creating The Table in PowerSchool
In order to use an ECT on an Ecollect form, you first need a one-to-many table within PowerSchool to store your data. A number of database extensions and tables were created as part of the Ecollect installation and are used on the pre-built form pack forms. You can reference these tables on your own forms where appropriate.
To create your own database extensions and tables, you will need to do the following:
- Go to System > Page and Data Management > Manage Database Extensions
- Choose the Database table you wish to extend. For Student forms, choose the student table. For Teacher forms, choose the User table.
- Choose Advanced Extension for the Workflow Type. Press Next.
- To create a new database extension, press the Add button at the top-right of the list of extensions. Give the extension a name. Press Apply.
- To add a table to the new or a pre-existing extension, select the extension in the list and press Next. Press the Add button at the top-right of the list of tables. Give the table a name and optional description. Press Apply.
- To add columns to the table, select the table in the list and press Next. Press the Add button at the top-right of the list of columns. Give the column a name and choose its datatype. Fill out any additional information required for that datatype. Press Apply.
- Add as many columns as necessary for your table. Press Submit.
Linking a Table to An Enhanced Collection Table Element
To add an ECT on a form, add the element and then link it to the database extension and table you would like to reference.
Next add elements to the ECT to collect/display data for the columns within the table. To do this, add the elements to the form and drag them into the ECT, much like you would for a Side-by-side Container. When the elements are first dragged into the ECT, they will have a red warning icon. This is because the element is not yet linked to a column in the extension table.
To create the link, enable the element’s PowerSchool field mapping. When you click the blue square to choose the field to link to, you will find that instead of a list of PowerSchool fields, you will see a list of columns in the table. Choose the column the element corresponds to. If the PowerSchool Field List displays the full list of PowerSchool fields instead of table columns, de-select and reselect the element and try again.
By default, all rows in the table associated with the student or user record will be loaded into the ETC. If you would prefer to only display those records the user adds with their current submission, you can set the Filter By Response property. Your table will need to have an additional column to store the response ID and you will select that column within the Filter By Response property. In addition, you will need to set the Form Property ‘Clear Form on Submission’.
Using an Enhanced Collection Table as an End User
When a user opens a form containing an ETC, unless the Filter By Response property is set, all rows within the one-to-many table for that student/user’s record will load in the ETC. If there is no data yet in the table for this student/user’s record or the Filter By Response property has been set, no rows will load.
To add a row to the ETC, the user must press the Add Row button. The user can then enter data into the elements within the ETC row. To add additional rows, the user can press the Add Row button again or the + button on the right side of the row. To remove a row, the user can press the - button on the right. When the user hovers the mouse over the - button, the row that will be removed is highlighted red.
Only if a row is visible will the required fields need to be filled out in order to submit the form. If the user does not add a row, the fields are not required.