Forum Discussion

JH's avatar
JH
Qrew Member
5 years ago

Relationship through 2 tables

Hello!

Hoping someone can help. 

I have 3 tables 
Purchase orders
Styles 
Raw Materials

Every Purchase order or PO has multiple styles on it. 
Every style has many raw materials 

I would like to have a report on the PO that displays all the Raw Materials that go into making this PO.

I am having trouble figuring out-
1. How to list all related styles on this PO (Multi select?)(Create a table of PO-STYLENUMBERS?)
2. MAIN GOAL- To see a PO and see all the raw materials that go into it


Post if anyone has any suggestions. Thanks!

------------------------------
J H
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    The setup for the PO Lines is this

    One PO has many PO Style numbers (ie the PO Lines)
    One Styles Master has Many PO Style numbers (ie the PO Lines)

    So you will set up what is sometimes called a "join" table which is a child of both Parents of the PO and the Style Masters tables.   The join table will have lookup fields to identify the PO and the Style and will have data entry fields for the Qty, Price, and calculated field for extended price.


    That is the easy part.

    Then the typical setup for the Raw Materials is this.

    One Raw Material Master has many Style Raw Materials.
    One Style has Many Style Raw Materials.

    ie again, there a many to many Relationships because the "recipe" for one Style will have Many Raw Materials and also a Raw material will get used on Many different Styles.   

    The middle join table will have lookup field to identify the Style and the Raw Material.  Then it will have a data entry field for the qty of the Raw Material required per Unit of Style.

    Once you get all that setup, then post back I can try to explain to you how you can get the Raw Materials  and their Quantities to show on the PO.  Depending on your skill level, it may take an hour of one on one consulting time to get that working as it may be too involved to explain on this Forum.

     


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------