Linked data tables allow you to connect one or more tables by information common to both. For example, you can have one table of schools that contains all the categorical information about each school (location, size, level, urban v rural, etc.) and a separate table of all teachers. To disaggregate teachers by the information about their schools (e.g. # teachers in urban settings) you can link teachers to their schools without having to repeat a school's categorical information for each teacher. Another term for this method of organizing information is relational data; the data is related by a shared column. This page includes:
- How to define the Lookup Key, the shared column between tables
- How to Link Tables
- How to Add Data to linked tables
First, make sure you have enabled the Linked Data Tables feature under Administration > Settings.
To link tables, find the Lookup Key section in the Design tab of the table that contains the information you'd like to like to pull into a different table. In our example, we want to add a Lookup Key to the Schools table so that we can pull information about schools into our Teachers table.
The Lookup Key serves two functions:
- It produces a unique value for each row in a table that is used to link each row of a table to a row in another table.
- You can configure it to display information in a different table in a specific format.
To begin entering a Lookup Key, click in the text box and add a bracket. This will give you a dropdown menu of all columns in your table. You can continue typing the name of the column or click on the column name to select it.
In the following example, we've created a unique Lookup Key by combining School ID and School Location. The Lookup Key in this case is [School ID][School Location].
If we were to link a row in this table with a row in a different table, a row where School ID is "123" and School Location is "Washington DC" will now display as "123Washington DC".
To configure the Lookup Key in a specific format, users can add spaces and special characters between column names. In the example below, we've made the Lookup Key and display more readable by adding a comma between the School ID and School Location.
Linking Existing Data Tables
To link a data table to another, navigate to the data table you want to link. In this example, we'll be connecting the School table to a table of Teacher Records.
Click on the Design tab.
This table already has several columns added, including the # years as a teacher, # years post-training, when the school year started, and where they are based. To pull in information about their school from the Schools table, click on (+) Add new column.
In the popup, you can specify what the column should be called. Click on the dropdown for Type and select Table. You can now click on the Table dropdown and select which data table you would like to connect.
Click on (+) Add Column.
Once you've added the column, DevResults will display all columns available from related tables, including those currently available and ones that are pending.
Once you've reviewed the pending changes, click on Save changes to link the tables.
You can now navigate to the Data tab to begin adding data, including information stored in the "Schools" table.
Click on (+) Add new row.
You'll notice that adding information to the new row remains the same.
When adding information from the "Schools" table (column: School) to a row, you can click on the dropdown and select the School ID and Location you want to include in that specific column.
Click Save to add the new row.
If you want to display the School ID and Location separately, you can do so by clicking on the gear icon on the top right corner of the table.
In the list of columns below, you can choose to display any columns in the table you linked to this one. In our example, we can choose to display School: Urban/Rural, School: # classrooms, etc. If we want to remove the Lookup Key from the table, we can uncheck the box next to it. In our example, that would be the School column.
Once you've created a linked table, you can start populating indicators with this data.
Didn't answer your question? Please email us at firstname.lastname@example.org.