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.
3 Likes
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.
2 Likes
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.
2 Likes
@Nathalie_Collins
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.
6 Likes
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!
4 Likes