Junction Table Samples

Hey Sheeters!

Has anyone had any luck using a Junction Table in their relational tables?

Hello Nathalie, good question!
Yo do not need Junction Tables in Spreadsheet.com since Related Row column could be multivalued - unlike SQL. In that way, Spreadsheet.com is very NOSQL-ish.

1 Like

Nathalie, In continuation to my earlier post - There are indeed situations where one would want to use a Junction Table and store information in the JT itself.
A somewhat contrived example is a Directors - Movies - Writers relationship. The Movies is the ‘Junction Table’ where a Director and a Writer collaborate. It has a name, year, BO Collection etc ( For the sake of simplicity we’ll assume that all movies have just one Director/Writer each). I put together a very small dataset for illustrative purposes.

From the Directors Sheet
→ It pulls all Junction Table entries ( the Movies)
→ But it does not pull all the related Writers or BO Collections.

It is the same story from the Writers Sheet side as well.
We call it the ‘Related Row Lookup - Rollup’. So this is a limitation currently. It happens to be a much asked for feature and is definitely in our short term roadmap.

I will update this space as we make progress.


Thank you for following up. This is a great example of what I have been experiencing. From my samples, the rollup only grabs the 1st item from the lookup list. Just like in your Director collection, it only grabbed the $2.9M for the Some Like It Hot movie. I haven’t tested to see if the results change if I change the order in the Movie table.

For me, this rollup would be a huge plus.


I think there are a lot of examples where junction tables are useful.

The example on wikipedia is one example: Users, Permissions, and UserPermissions

A junction table is especially needed when the association between the two tables involves other information, such as quantities. A couple of examples:

Manufacturing: Assemblies table, Parts table, and a junction table that lists the quantities of individual Parts for each Assembly. The Parts table contains cost and weight of each part. The common rollup fields in the Assembly would then be Total Cost and Total Weight for each assembly based on the quantities of parts within the assembly.

Kickstarter: I’ve been working on a Kickstarter planner that has SKUs as a table and Rewards as a table. The junction table determines how many of each SKU is included with each Reward. The SKU has a manufacturing cost and weight (just like the assembly example above).

Project Resource Management: Tasks table and a Resources table (where Resources are employees). The junction table defines the loading factor for each of the resources assigned to a specific task. For example, Employee A working 50% of their time on the task, Employee B working 25%, Employee C working 100%. If a task takes 12 hours, then the roll-up could calculate the total labor cost based on the Employees’ hourly rates and loading factors.


Junction Table functionality is now available with the latest release of enhancements to ‘Related Row Lookup’ and introduction of ‘Related Row Rollup’

  • Related Row Lookup can now list all the Lookup values from multiple related rows.
  • Related Row Rollup rolls up multiple values from across all related rows using functions such as SUM, AVERAGE, MIN, MAX, UNIQUE etc.

You’re the best @Murali_Mohan !!!


Awesome! I just tried this with my Kickstarter spreadsheet, and was able to reference the junction table using the Related Row Rollup to reproduce in a few clicks what took me quite a bit more time with SUMIFS. And it’s much more robust with the rollup due to not requiring a complex formula that must be copied to newly inserted rows.

The Related Row Rollup is a pretty amazing new update, and I look forward to using it in other spreadsheets. Kudos to your team!