7 Replies Latest reply on Sep 12, 2012 3:59 PM by philmodjunk

    Generate invoice items with multiple additional sub-items?

    CameronL.

      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

        • 1. Re: Generate invoice items with multiple additional sub-items?
          philmodjunk

               I'd put all these entries in the same table whether a "Header" item such as "Exterior Hinged Door Installation Existing Unit: 84'' x 48'' x 6'' Right-hand Inswing" or a "sub" item such as "3" x 7' Pine Casing"

               A field in the LIneItems table--which can be populated from a field in the products table and/or controlled by the user clicking a check box field in the portal row, can identify which are "sub" items. You can then use this field in a List view layout used for printing the invoice to indent the "Sub" line item entries to produce the results you have here. One way to do that is to add a calculation field such as:

               If ( SubField = "Sub" ; Char ( 9 ) ) & DescriptionField )

               Char ( 9 ) returns the tab character so if you use this calculation field in place of your description field, you'll see the description field indented when the item is marked as a sub item. And you can use a setting on the Inspector's Appearance tab to set a tab stop to control exactly how far the text will be indented.

          • 2. Re: Generate invoice items with multiple additional sub-items?
            CameronL.

                 Thank you for the answer PhilModJunk. This seems like it will be simpler.

                 I'm still wondering, though, how would I make sure each "sub"-item is associated with its respective "header" item? This is important in the presentation of the invoice PDF.

            • 3. Re: Generate invoice items with multiple additional sub-items?
              philmodjunk

                   How and when do you select them?

                   If you create a new portal record for the "header" item and then list each sub item before creating the next 'header' item, then they are already grouped under the appropraite header item.

                   You may need to use a field to give all items in a group a common value. If you have some kind of product ID for the header items, this field could be copied from the previous record in an auto-enter calculation that copies this data if the "sub" field = "sub" and does not copy from the previous value if it does not.

              • 4. Re: Generate invoice items with multiple additional sub-items?
                CameronL.

                     Usually the sub items are added after a header item is added.

                     Presently I have an "Add Item" button that navigates to an add item page. On that screen I have an "Add Materials" and "Add Custom Work" button that pop up respetive layout windows to add those things.

                     This is the standard use case, but ideally the database should allow users to add Materials / Custom Work to "header" items that have already been added.

                     Right now I have a portal on the Measures (Invoice) table layout that shows all records from the Measure Line Items table. Each row in the portal (i.e. each "header" item) can be clicked which navigates to its related record for any editing and, ideally, to add any associated Materials or Custom Work.

                     I hope this all makes sense. I can post screenshots of the layouts if it helps.

                     Thank you again for your help so far!

                      
                • 5. Re: Generate invoice items with multiple additional sub-items?
                  philmodjunk

                       You can define an auto-entered serial number field in Measure Line Items that uniquely identifies each record. If you then define a second number field that gets the serial number ID of the correct "header" record, you will have successfully grouped your line item records by a common value taken from the appropriate Header Line Item Record.

                  • 6. Re: Generate invoice items with multiple additional sub-items?
                    CameronL.

                         Thank you PhilModJunk.

                         For the most part your advice has worked great!

                         I modified my ERD to the attached screenshot. I also added Foreign Key fields into the Measure Line Items table so that items are now "grouped" by associated Product ID.

                         I'm still stumped though as to how to sort the records on the invoice layout list view so that the sub items will appear underneath their respective header items.

                         Basically I want to sort the line items by Product, then that Product's associated Custom Work entries, then the associated Material entries. Then after all product entries are done, list the Delivery and Haulaway entries. Essentially like this:

                         Product 1
                              Product 1 Custom Work 1
                              Product 1 Custom Work n...
                              Product 1 Material 1
                              Product 1 Material n...
                         Product n...
                              Product n Custom Work 1
                              Product n Custom Work n...
                              Product n Material 1
                              Product n Material n...
                         Haulaway
                         Delivery

                         So far the closest I've been able to come is with sorting the related records by ProductID, CustomWorkID, MaterialID, HaulawayID and then DeliveryID, all ascending, when I generate the PDF. However, with this method the Custom Work entries appear after the Materials. It also puts the Haulaway and Delivery lines at the very top, though I assume I could fix this by sticking them in a Trailing Grand Summary layout Part since they always come at the end.

                         I feel like I need something similar to Excel's "sort by: X, THEN sort by: Y" etc. ability.

                         Any ideas?

                         Thank you again for your help so far!

                    • 7. Re: Generate invoice items with multiple additional sub-items?
                      philmodjunk

                           What you post looks much like a summary report with Sub Summary parts serving as the "sub head"--an approach that I suspect you can get to work if you get the right data in the right fields and then sort your records.

                           Another trick that you might find useful is that you can define a custom value list and then use the order of the values in the custom value list to determine the sort order for the same values in one of your fields. This allows you to group your records in an arbitrary order instead of either ascending or descending.