AnsweredAssumed Answered

Generate invoice items with multiple additional sub-items?

Question asked by CameronL. on Sep 10, 2012
Latest reply on Sep 12, 2012 by philmodjunk

Title

Generate invoice items with multiple additional sub-items?

Post

Hi all,

I'm currently building a database for a door installation company that generates invoice items. It's working fine to add the line items themselves, but I'm having trouble figuring out the best way to add sub-items for those line items.

For example, a typical job invoice might have 3 doors. Each line item might say "Door installation". However, additional materials or custom work is usually required. These additional items need to be presented as a sub-item of each door, since they are specific to that door. Here's what the ideal 3-door invoice might look like:



Exterior Hinged Door Installation                                      

Existing Unit: 84'' x 48'' x 6'' Right-hand Inswing                 $500

   Additional Material:

   (2)   3" x 7' Pine Casing                                                $28

   (1)   3" x 16' Pine Casing                                              $35
        Custom Work:
        Trim door bottom to fit over carpet.                                $20
        Custom Work:
        Refinish existing hardware                                            $80


Interior Slab Door Installation                                      

Existing Unit: 80'' x 40'' x 4'' Left-hand Inswing                 $130

   Additional Material:

   (1)   3" x 16' Pine Casing                                              $25
        Custom Work:
        Remove dry rot                                                            $40

      

Exterior Hinged Door Installation                                      

Existing Unit: 84'' x 48'' x 6'' Right-hand Inswing                 $500

   Additional Material:

   (2)   3" x 7' Pine Casing                                                $28

   Custom Work:
        Paint                                                                           $80


     As you can see, the Material and Custom Work sub-items vary item by item and vary in number. I currently have a Join table for Invoice Line Items, and I'm thinking to use another Join table for the Materials / Custom Work items associated with each product. But I'm stuck as to how I'll get that to populate to the invoice PDF's.

     I've attached a picture of my ERD for reference.

     Help is greatly appreciated!
     Thank you.

      

Screen_Shot_2012-09-10_at_6.34.33_PM.png

Outcomes