Reference columns are greyed out and not selectable

Ask for Help Formulas
#1

I have a number of tables for recording the development of a set of training lessons, in the following hierarchical order:

  • Areas table: Areas are broad areas of learning

  • Subjects table: Subjects break the Areas down into a number of smaller grouping

  • Modules table: Subjects are then further divided into a number of related modules

  • Lessons table: Lessons describe the specific lessons that will take less than one hour to complete.

Each table has a reference column to show the preceding tables in the hierarchy. Thus, the lesson table will also show which area, subject and module it belongs to. This all works fine. (Img 1)

image
image2097×1993 571 KB

I also have a Progressions table which records when each lesson moves a development stage in the following order:

  • Assigned to a team member
  • Outlined and content is planned
  • Started and in progress
  • Submitted for peer review
  • Work signed off and published

The table records the date, and the progression which comes from a STATUS table as a reference.

When I go to record a lesson changing a stage, I want it to include the Area it belongs to for dashboard purposes.

So I tried to add another reference column but AREA, SUBJECT, MODULE, etc are greyed out. (Img 2)

So I tried to add it But AREA and SUBJECT are not available for selection.

Can someone help me to resolve this, OR, suggest a better way to complete the reporting aspect? That is, show how many progressions have been made per AREA within a date range.

#2

Hi Andrew!

These are greyed out because they are reference columns themselves in the Lessons table. Because you have a ‘Lesson’ column in your current table that already pulls data from the Lessons table, you can use this to pull other valuable data, using what we call ‘Dot Notation’. Dot notation follows the format $REF_COLUMN.COLUMN_NAME where REF_COLUMN is the name of your reference column in the current table (Lesson) and COLUMN_NAME is the name of the column you want data from in the referenced table. Lesson references the Lessons table so COLUMN_NAME would be a column from that table. In this case, we want data from the Area column. This would give us the formula $Lesson.Area. Because Area is a reference column, it stores data for the entire record that it is referencing. We need to add a bit more to this formula to specify what data from the Area table we want. This is referred to as ‘Chaining’. We want data from the Area column of the Area table so we use dot notation again to add this on to the end of our formula. Our final formula is:

$Lesson.Area.Area

With this, you’ll be able to create summary tables to show how many progressions have been made per area.

Let me know if you have any questions!

Natalie