4 Replies Latest reply on Nov 9, 2009 10:36 AM by philmodjunk

    how much of each ingredient is required for each single invoice?

    gorgyr

      Title

      how much of each ingredient is required for each single invoice?

      Post

      Can anyone help me on the road?

      I have 3 tables:
      ingredients                     products                   invoices<!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:HyphenationZone&gt;21&lt;/w:HyphenationZone&gt;   &lt;w:DoNotOptimizeForBrowser/&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:&quot;Baskerville Old Face&quot;;      mso-font-alt:&quot;Times New Roman&quot;;      mso-font-charset:0;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Arial Unicode MS&quot;;      mso-font-alt:&quot;MS Mincho&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face      {font-family:&quot;\@Arial Unicode MS&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} p.Formuletitelfeestgids, li.Formuletitelfeestgids, div.Formuletitelfeestgids      {mso-style-name:&quot;Formuletitel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      tab-stops:315.75pt;      font-size:14.0pt;      mso-bidi-font-size:16.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      text-decoration:underline;      text-underline:single;} p.Onderdeelformulefeestgids, li.Onderdeelformulefeestgids, div.Onderdeelformulefeestgids      {mso-style-name:&quot;Onderdeel formule feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} p.Titelfeestgids, li.Titelfeestgids, div.Titelfeestgids      {mso-style-name:&quot;Titel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:24.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      color:#632423;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} @page Section1      {size:612.0pt 792.0pt;      margin:70.85pt 70.85pt 70.85pt 70.85pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:HyphenationZone&gt;21&lt;/w:HyphenationZone&gt;   &lt;w:DoNotOptimizeForBrowser/&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:&quot;Baskerville Old Face&quot;;      mso-font-alt:&quot;Times New Roman&quot;;      mso-font-charset:0;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Arial Unicode MS&quot;;      mso-font-alt:&quot;MS Mincho&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face      {font-family:&quot;\@Arial Unicode MS&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} p.Formuletitelfeestgids, li.Formuletitelfeestgids, div.Formuletitelfeestgids      {mso-style-name:&quot;Formuletitel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      tab-stops:315.75pt;      font-size:14.0pt;      mso-bidi-font-size:16.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      text-decoration:underline;      text-underline:single;} p.Onderdeelformulefeestgids, li.Onderdeelformulefeestgids, div.Onderdeelformulefeestgids      {mso-style-name:&quot;Onderdeel formule feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} p.Titelfeestgids, li.Titelfeestgids, div.Titelfeestgids      {mso-style-name:&quot;Titel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:24.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      color:#632423;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} @page Section1      {size:612.0pt 792.0pt;      margin:70.85pt 70.85pt 70.85pt 70.85pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:HyphenationZone&gt;21&lt;/w:HyphenationZone&gt;   &lt;w:DoNotOptimizeForBrowser/&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:&quot;Baskerville Old Face&quot;;      mso-font-alt:&quot;Times New Roman&quot;;      mso-font-charset:0;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Arial Unicode MS&quot;;      mso-font-alt:&quot;MS Mincho&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face      {font-family:&quot;\@Arial Unicode MS&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} p.Formuletitelfeestgids, li.Formuletitelfeestgids, div.Formuletitelfeestgids      {mso-style-name:&quot;Formuletitel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      tab-stops:315.75pt;      font-size:14.0pt;      mso-bidi-font-size:16.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      text-decoration:underline;      text-underline:single;} p.Onderdeelformulefeestgids, li.Onderdeelformulefeestgids, div.Onderdeelformulefeestgids      {mso-style-name:&quot;Onderdeel formule feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} p.Titelfeestgids, li.Titelfeestgids, div.Titelfeestgids      {mso-style-name:&quot;Titel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:24.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      color:#632423;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} @page Section1      {size:612.0pt 792.0pt;      margin:70.85pt 70.85pt 70.85pt 70.85pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:HyphenationZone&gt;21&lt;/w:HyphenationZone&gt;   &lt;w:DoNotOptimizeForBrowser/&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:&quot;Baskerville Old Face&quot;;      mso-font-alt:&quot;Times New Roman&quot;;      mso-font-charset:0;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Arial Unicode MS&quot;;      mso-font-alt:&quot;MS Mincho&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face      {font-family:&quot;\@Arial Unicode MS&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} p.Formuletitelfeestgids, li.Formuletitelfeestgids, div.Formuletitelfeestgids      {mso-style-name:&quot;Formuletitel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      tab-stops:315.75pt;      font-size:14.0pt;      mso-bidi-font-size:16.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      text-decoration:underline;      text-underline:single;} p.Onderdeelformulefeestgids, li.Onderdeelformulefeestgids, div.Onderdeelformulefeestgids      {mso-style-name:&quot;Onderdeel formule feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} p.Titelfeestgids, li.Titelfeestgids, div.Titelfeestgids      {mso-style-name:&quot;Titel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:24.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      color:#632423;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} @page Section1      {size:612.0pt 792.0pt;      margin:70.85pt 70.85pt 70.85pt 70.85pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   -->   
      Product_ID------=---------ID-------=------Product_ID

      name                            name                   ID

      Quantity/product                                      Quantity products

       

      1 invoice can consist of several products.
      1 product consists of several ingredients.
      1 ingredient can avoid in several products.
      1 product can be 'ordered' by several invoices.


      I can see how much of each ingredient is needed in total for all invoices.
      And I can see what ingredients are needed for each single invoice.
      What I can not see is how much of each ingredient is required for each single invoice.

      Should I fix this with a self join table or multikey? or something els?

      Thanks.
      A helping hand is enough!







        • 1. Re: how much of each ingredient is required for each single invoice?
          david_lalonde@d-cogit.ca
            

          You have an interesting problem.

           

          You want something out of invoices that actually resides in ingredients. Worse, the calculations you seek can only be performed in ingredients.

           

          One solution is to create a new set of relationships for the sole purpose of calculating those numbers.

           

          Unless each invoice can contain only one product, a table is missing, the invoice line items table. That table should contain the invoice ID, the product ID and the product quantity. Insert the invoice line items table between the products and the invoices table.

           

          In ingredients, I would add an invoice ID global field.

           

          I would create a relationship between ingredients and a new occurence of invoice line items, lets call this new table occurence "selected invoice line items", using the invoice ID global field and the product id field as key fields. I would create a relationship between the selected invoice line items and a new table occurence of products, lets call this new table occurence "selected products", using the product ID as the key field.

           

          This now gives ingredients the abitily to see line items related to it for a single invoice.

           

          In ingredients, I would add a calculation field, lets call it "invoice quantity", that multiplies the sum of product quantity (from the selected products table) and the ingredients quantity/product. That will give you the total ingredient quantity per invoice.

           

          In this solution, you are limited to see only one invoice at a time. If you wanted to see this in a report for all invoices (total, in a date range, etc.), then your schema and a good chunk of your solution would need to be redone because the model just does not fit the needs.

          • 2. Re: how much of each ingredient is required for each single invoice?
            tria
              

            You need to make 2 list tables to link the 3 main table together.

             

              have 3 tables:

            ingredients                     products                    invoices    
            Product_ID------=List table---------ID-------List table=------Product_ID

            name                            name                   ID

            Quantity/product                                      Quantity products

             

            Because you have the following relationships:

            Ingridients -many to many ---Product ----Many to Many ---Invoices

             

            Once you've done that you can have a portal in Products and Invoices to add ingredients or products

             

            don't quite get that:1 ingredient can avoid in several products.

             

            • 3. Re: how much of each ingredient is required for each single invoice?
              gorgyr
                

              Hello David and Tria,

               

              Thanks for your help.

              I Think I was not clear enough.

              My database is a litle more complicated than i explane.

              It is a example of 'jonathan stark' that i have changed.

              To give you a better view, i gave insert a image of my relations.

              @David: i need to see them togheter in a report or a portal, not one invoice-item at a time.

              @tria:1 ingredient (egs) can avoid in more products (mayonnaise, omelette). (maybe i translate wrong!?)

              Now i have a layout based on 'invoice item to invoice' where i can see in a portal 'the ingredients needed for that invoice and also how much of the ingredients i need for the orderd product'.But i want to see how much of each ingredient i need for that invoice.

               

              I can't insert an image. Can somebody explain how i can insert one?

               

              Thanks!

               

              • 4. Re: how much of each ingredient is required for each single invoice?
                philmodjunk
                   Try setting up a summary report in Ingredients and use your relationships to add fields from the other two tables into Sub-summary parts. You can sort your ingredients by invoice and/or product to group your ingredients beneath sub-summary parts that display related data and sub-totals.