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)
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.
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
Hi Natalie
Congratulations - I imagine you are well-married by now. I trust you had a wonderful day
I have two questions:
- My blog posting database is coming along really well. However, when I go to create subtotals for posts per client by type, I get a #keyerror message.
-
I have a CLIENTS table on the page as a table
-
I have a blog POSTS table on the page as a table, to record all posts created for all clients
-
I have a BLOGS table that is not on the page, to record all the domains where we post blogs
-
in the POSTS table there is a reference column to the client
-
in the POSTS table there is a formula to capture the TYPE (blog, press release, classified, business directory) from the BLOGS table
-
I add a widget to the page - table, POSTS, select by CLIENT, group by TYPE
What am I doing wrong?
- I have a few complicated tasks where it would be easier for me to pay someone to do them for us. Can you recommend anyone who is not too expensive but very capable?
Hey Andrew!
Thank you so much - it was an amazing day! 
I tried to recreate your set up here: Community #1943 - Grist
I’m not sure why yours is producing this #KeyError. Can you share your document with me (support@getgrist.com) and I can take a look to see what the issue might be!
For paid projects, we have our Sprouts program. I’ll have Anais reach out to you with information.
Thanks,
Natalie
Thanks so much
The CLIENT column in the POSTS table is a reference list and can have multiple clients. Could that be why?
I have shared with you.
Many thanks
Andrew
Hey Andrew!
The issue is that the Type column in the Blog table is a Choice List but the Type column in the Posts table is not.
I noticed that all of the blogs only have a single Type selected. Will there ever be an instance where a Blog might have multiple types? If so, keep the column as a Choice List. If not, you can change the column type to Choice. The only difference between Choice and Choice Lists columns is that Choice Lists allows you to select multiple values from the list of choices while a Choice column only allows you to select one.
If you change the type to ‘Choice’, the error will be resolved. If the column type should remain ‘Choice List’, change column type of the Type column in the Posts table to ‘Choice List’. You can copy/paste the choice values from the Type column in the Blog table. Updating the column type to Choice List in the Posts table, will resolve the error.
1 Like
Awesome - what a legend!
That was an instant fix, thank you.
So now, that it is working, how do I add a total to the subtotals?
| Directory |
12 |
| Blog |
9 |
| Client Recommended |
4 |
| Press Release |
3 |
| TOTAL |
28 |
You can add another summary table that will summarize all posts. To do this, click the green ‘Add New’ button and ‘Add Widget to Page’. Click the green summation icon to the right of the Blog data set then ‘Add to page’.
This will add a BLOG [Totals] table that summarizes the entire Blog table. Here we see their are 4 posts.
If you don’t always want to see the total count on the page, you can get the sum of a column by clicking the column header to highlight the entire column and then the sum will show at the bottom right of the table.
Sorry, not working for me
I have used subtotals to show the number of each type of POSTS,
|Directory|12|
|Blog|9|
|Press Release|3|
|Client Recommended|4|
I want to then total these to get 28. Currently my best efforts get 696???
It is still shared with you if that helps
Many thanks
Andrew
ps. my son got married on Sunday
1 Like
Ah - my apologies! Because you want totals for each Client, group by Clients then select by Clients as well. This will show the total posts for the selected client (rather than total for all clients).
Congratulations to your son! 
Beautiful pictures - I hope they had an amazing day!
Thanks so much!
I’d still love to find some affordable help for a few other challenges
Have a great week
Andrew
1 Like
