Skip to content
Aggregating data about my organizations
Aggregating data about my organizations
Data
More
Share
Explore

icon picker
Aggregating data about my organizations

Using ForEach() and Filter() to analyze multiple tables with relation columns
Shoutout to the Coda team for the advanced tutorial. I’m publishing this example as a thank you and for the Community.

Background

There are 3 data tables in this example doc—Employees, Organizations, Projects.
Employees and Organizations are directly related to each other so we know which employees are assigned to each organization
Employees and Projects are directly related to each other so we know which employees are working on each project

With formulas, we can use Employees to retrieve data about Projects and aggregate that information for views of the Organizations table.
Note that the following examples also use
WithName()
⁠
as advised by Coda. I was able to validate the examples below also work with CurrentValue. I think WithName() is better for two reasons—1) it’s easier to read/remember the variable reference deep inside the formula 2) if you build on top of these examples and end up having an inner and outer CurrentValue, you won’t have to refactor the whole thing

⁠


Get the unique projects that an organization is working on

Partial formula: Projects for each Employee

The
Organizations
⁠
table has a list of employees. To get the list of projects that an organization is working on, we have to pull out the projects that each employee of the organization is working on. The formula to accomplish that produces a list of lists (a list of the list of projects for each employee).
Employee.ForEach(
WithName(CurrentValue, Person,
Projects.Filter(Team.contains(Person))
))
Org
Projects by Employee
1
PM
[
Pixel
Echo
Pulse
Blaze
Solar
Prism
][
Flux
Nova
Zen
Shift
Zephr
Nexus
]
2
Design
[
Flux
Zen
][
Pixel
Echo
Luna
Quest
][
Nova
Pulse
Spark
]
3
Engineering
[
Pixel
Pulse
Zephr
Nexus
Prism
][
Pixel
Zen
Blaze
Spark
][
Nova
Zephr
Prism
][
Flux
Spark
][
Echo
Pulse
Blaze
Nexus
Solar
]​​[
Nova
Zen
Shift
][
Nova
Shift
Solar
][
Shift
Spark
Nexus
Solar
][
Pixel
Zephr
Quest
][
Flux
Pulse
Luna
Quest
][
Flux
Blaze
Luna
][
Echo
Zen
Luna
][
Echo
]
There are no rows in this table
⁠

Full formula: unique Projects for Organizations

Building on
Projects for each Employee
⁠
, we add two other functions—
.ListCombine()
⁠
to collapse the list of lists into a single list and
.Unique()
⁠
to dedupe the list of projects from each repeat occurrence corresponding to each employee.
Employee.ForEach(
WithName(CurrentValue, Person,
Projects.Filter(Team.contains(Person))
)).ListCombine().Unique()
Org
Projects Across Org
1
PM
Pixel
Echo
Pulse
Blaze
Solar
Prism
Flux
Nova
Zen
Shift
Zephr
Nexus
2
Design
Flux
Zen
Pixel
Echo
Luna
Quest
Nova
Pulse
Spark
3
Engineering
Pixel
Pulse
Zephr
Nexus
Prism
Zen
Blaze
Spark
Nova
Flux
Echo
Solar
Shift
Quest
Luna
There are no rows in this table
⁠
⁠

Visualizing how the members of an organization are resourced

Partial formula: count of Projects for each Employee

Again, the
Organizations
⁠
table has a list of employees. Instead of displaying the output as projects, this time we use
.Count()
⁠
to display the list of list as a number of projects (a list of the count of projects for each employee).
Employee.ForEach(
WithName(CurrentValue, Person,
Projects.Filter(Team.contains(Person)).Count()
))
Org
Project Count for Employees
1
PM
66
2
Design
243
3
Engineering
543250033434331
There are no rows in this table
⁠

Full Formula: Filter Employees By number of Projects

Building on
Count of Projects for each Employee
⁠
, we’ve extended a view of the Organizations table further to show a distribution of employees by the number of projects they are working on. The formula to accomplish that lets us see who is unassigned (0 projects), who might be on too many projects (5+), and everything in between.
To accomplish this, we swap the ForEach() with Filter() and update the expression to a boolean that shows the employees with count of zero project assignments. Then we repeat that formula in each column to match the criteria we want—1 project, 2 projects, 3 projects, 4 projects, 5+ projects.
Employee.Filter(
WithName(CurrentValue, Person,
Projects.Filter(Team.contains(Person)).Count() = 0
))
Org
0 Projects
1 Project
2 Projects
3 Projects
4 Projects
5+ Projects
1
PM
[  ]
[  ]
[  ]
[  ]
[  ]
Phil
Maya
2
Design
[  ]
[  ]
Ethan
Zoe
Luke
[  ]
3
Engineering
Cole
Max
Ivy
Ryan
Noah
Ava
Emma
Grace
Lily
Ruby
Liam
Mia
Ella
Adam
Owen
There are no rows in this table
⁠

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.