Skip to main content

Hi, I’m hoping there is a way to do this that’s just a bit above my creative thinking right now.


The setup is this: I have a table of projects, with a linked field to records of individual yarns I have used in the project.


I need to record the quantity I used of each of the yarns.


These quantities will then feed through into a formula in my yarn records which picks up the quantity used in projects and recalculates the remaining available quantity.



I’ve got this working for single-yarn projects, but I can’t see how to have multiple values associated with specific linked records.



So for example if Project 1 has links to Yarn A and Yarn B records, I want to say that I used 100g of Yarn A and 50g of Yarn B.



I then need to be able to lookup only the quantities used in projects of each specific yarn, in order to sum that and use the figure to calculate remaining quantity.



This is my current project table structure.



Welcome to the community, @Nicole_Hawkesford! :grinning_face_with_big_eyes: This is where a junction table comes in handy. Junction tables are used to help you track many-to-many relationships. In this case, any given yarn can be used across multiple projects, with a different quantity used on each one. With a junction table, you would have (at a minimum) two link fields and a quantity field. The link fields would allow you to link each record to one yarn and one project, with the quantity of that yarn entered into the quantity field. Over in the [Yarn in stash] table, you can then roll up the total quantity used of each yarn across all projects.



Here’s some info about many-to-many relationships, including examples of junction tables:



Thanks! Yes, this solves it (at least I think this is what I’ve now done). So I have my Yarn in Stash table and my Projects table. I’ve created a third table, Yarn/Projects, which is my junction table. In this table I have a line per unique yarn/project combination, a Linked field to the Projects table, a Linked field to the Yarn in Stash table, and a numeric entry field for the quantity used.





And on my Projects table and my Yarn in stash table, I have the Linked fields to Yarn/Projects table and a lookup that pulls the quantities used as listed in that table, that can then feed through into the sum calculations that I wanted.



Reply


Terms & Conditions