Tracking careers in Coda
Using a time stamp in a table to identify a time related position
The creation of the logic shown in this blog is the result of a double exercise. I am building a serie of related docs that once Coda launches the 2WS without shared permissions on both sides (stage 4) we can use together. In this scenario the HR team creates a doc with employees of your team you have access to. You can add information and this new information is shared with the HR team. You don’t have access to the main HR doc that contains all information about all teams, you only access the doc relevant for your team. We are not there yet.
The other and more direct motivation was that I got asked to develop a permission logic. Team leaders and managers have to sign off on proposals, budgets, you name it.
The signing off is not the issue. We have buttons to handle that and on top they can relate to a specific user, thus only become active for a specific user. Here is the thing, You don’t want to set manually who is responsible for what once an employee asks for permission. You want to use a filter to bring in the relevant person (team leader or manager).
This is very well possible when we apply a BOM logic that gives as back the most recent value when we use today or any other date related value. In this context we work with Today.
You can read about this and my previous ideas via the doc below.
Our focus is to show how we automatically get the right person to give permission. There is a key role for the function Sort(). I overlooked this function for too long, maybe because my mindset was still a bit spreadsheet oriented. In fact it is the first function that should come to mind directly after Filter().
The employee table
This table is the foundation in our approach. Ideally we get all the employees via a synced page coming for the main HR doc including info related to being absent for a long time or having left the organization. In our example we created a set of employees to show the logic. This introduction underlines the value and importance of putting a good doc schema in place. I wrote about it in this blog. Applying the principles outlined will take you some time, but afterwards you will be grateful and thank me.
Who is the N + 1?
When I started writing the code, I took this literal and I looked for the next in line (team leader or manager). I noticed soon my misunderstanding: what if there is no next in line because:
- you are already the big boss, nobody above you
- the N+1 does not exist, but an N + X does like no team leader, but a manager.
The solution is rather simple once you see the issue. The ranking in the screenshot is the numeric value from 1 up to 6.
To understand this logic, a few concepts:
It is based on the most recent role of the employee which gives us the position in a ranking. We set 1 for employee, 2 for team leader and 3 for Manager, you can add 4 for Director and 5 for VP and 6 for the CEO, up to you to create your own logic. The filter looks for values greater than and we exclude people no longer with us (or temp absent). The function First() takes care of getting the first in line, a team leader when there is also a manager.
The above table s based on what you see below.
Below is the schema. We have taks related to employees and based on the table DB Work we define the next in line (N + X).
As started with, this blog fits in an approach in which we use the most recent position of an employee in the organisation.
A variation is that you can also look for who worked where at any given date. I already discussed the issue in the blog below.
Coda the prices of products prudently
Get the most recent price applying a BOM logic
huizer.medium.com
In that blog you find also the screenshot below; we will blend the employee approach with the price approach to understand who worked with whom at what time.
This is what we have and the question is what are the 3 steps to get there?
- the date is a date picker and the minimal date is the first date of our datebase.
- the employee is the result of two steps. we relate to the table with all employees and second via options we filter on people working as of the selected date (screenshot below).
- The role and the department are a logical next step. We filter our main table DB Work on the employee and the date and we take the first outcome of the list of possible outcomes.
In the doc below you seen see how it works.
My name is Christiaan and blog about Coda. Since the summer of 2023 mainly about how to Coda with AI to support organisations dealing with texts and templates. Why I focus on Coda AI you can read here: ⤵️
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. You find my (for free) contributions to the Coda Community and on Twitter.
Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
