5 Replies Latest reply on Jul 29, 2009 9:08 AM by comment_1

    Multiple document category total in one invoice

    Socio.t

      Title

      Multiple document category total in one invoice

      Post

      Hello everyone,

       

      I'm trying create an invoice that includes multiple expense sheets that are related to one client and one project.

       

      In my company we work by projects. Every project has a client associated.

      As we generate expenses related to the projects that are later being included in the client bill, we identified that it would be nice to be able to generate the client invoives just by clicking the expense sheets that are related to him in a determined project.

       

      So the idea is that when I select a client and a project I'm able to see all the expenses sheets that are related to that client and project. After that just select the expense sheets that I want to include in the invoice and it would make the calculations, returning a report with the expense sheets sum by category, since every expense in the expense sheet has a category (like food, material, transportation,...).

       

      By now I have four tables:

      Clients: Client code; Client name

      Projects: Project code; Project name; Client code

      Expenses: Expense code; Expense Sheet code; Project code; Expense category; Expense description; Expense value; Total

      Expense Sheets: Expense sheet code; Expense code; Sheet total

       

      1. How can I make it show the list of expense sheet related both to client and projec?

      2. How can I select the expense shees and generate an invoice with the expense sheets total sum that sorts it by expense category?

       

      I already tried looking for forums and tested every idea I had, but already getting out of ideas.

       

      I will thank you if you can share with me some ideas. I really need them.

       

      Thank you a lot.

       

      Best regards,

       

      Socio

        • 1. Re: Multiple document category total in one invoice
          comment_1
            

          This part is confusing:

           


          Socio.t wrote:
          Expenses: Expense code; Document code; Project code; Expense category; Expense description; Expense value; Total

          Expense Sheets: Expense sheet code; Expense code; Sheet total


           

          Doesn't one expense sheet hold many expenses? If so, there should be a 'Expense sheet code' field in Expenses - instead of a 'Expense code' field in Expense Sheets.


          • 2. Re: Multiple document category total in one invoice
            Socio.t
              

            Absolutly right. I mixed up with the name. Already fixed above.

             

            Thank you for comment.

            • 3. Re: Multiple document category total in one invoice
              comment_1
                

              OK, then: assuming an invoice includes only expense sheets, not individual expenses, you could assign sheets to invoices by setting an InvoiceID field in the Expense Sheets table to the InvoiceID of the selected invoice. This would make all expenses in those sheets related to the invoice ( indirectly).

               

              To print an invoice, use Go to Related Record [Expenses; Show related only] to find the related expenses, and summarize them by category (or rather by invoice, then category - so you can print multiple invoices).

              • 4. Re: Multiple document category total in one invoice
                Socio.t
                  

                Thank you so much, I think we are on the right track.

                 

                But I have a doubt.

                 

                What you describe will list the expense sheets related to an invoice. But what i want to know is how do I select only the ones I want to include in an invoice.

                 

                Imagine that I have 3 expense sheets that are related to a project and client. But for the moment I just want to charge him two expense sheets because the other one I have to charge only in the next month.

                 

                Is there a way that I check the one's that I want from the list and it includes only those in the invoice?

                 

                Thank you very much.

                 

                 

                • 5. Re: Multiple document category total in one invoice
                  comment_1
                    

                  You "select" the expense sheet to include by setting a field in the expense sheet to the ID of the selected invoice. The exact method depends on how you want to build your user interface. You could be on the invoice layout, and select from a portal of unassigned sheets, or you could store the invoice ID in a variable, go to the sheets layout and do it from there - these are just two examples.