7 Replies Latest reply on Jan 9, 2012 12:27 PM by philmodjunk

    creating automatic invoices.

    weedonpaul

      Title

      creating automatic invoices.

      Post

      Hi

      I have a table that logs hours spent working for different clients, I record date, client, type of work,  hours spent, and rate. As each client has different rates depending on job done etc, the rates are filled in by a calculation field. so a tipical day might look loke this

      1/1/12         Client A        Reports        2 Hours       £4.50

      1/1/12         Client B        Admin         1 Hours       £5.00

      1/1/12         Client A        Admin          2 Hours       £6.50

      1/1/12         Client A        Reports        3 Hours       £4.50

      1/1/12         Client B        Meetings       2 Hours       £7.50

      1/1/12         Client B        Admin          2 Hours       £5.00

      I also have a second table that records expenses. with fields for date, type, amount and client to be billed.

       

      Now I need to bring all this together in an invoice, that generates a unique invoice number.

      So if i enter client and month it will generate an invoice for me. so client A will get

      *********************************************

      Client A                                invoice number 0001

      Address

       

      Bill for January

       

      Reports              5 hours           £22.50

      Admin               2 hours            £13.00

       

      Expenses

      Type                                     amount

      Type                                     amount


                                Total £xxxxxx

      *************************************************

      I would like each invoice in it's own record.

      Your help would be apreaciated

                                       

        • 1. Re: creating automatic invoices.
          philmodjunk

          You might consider using a single table for time worked and expenses. That may seem the wrong approach at first, but it makes possible a more flexible approach to printing your invoices. Hours worked and expenses can be seen as items you've "sold" to your client and which represent revenue you epect to receive when you bill your client. Seen in that light they can be records in the same table that do not use all the same fields and which are labeled and sorted by which type of entry they are to produce the above report.

          Should you take that approach, the classic invoicing set up with these three tables would work for you:

          Customers-----<Invoices-----<LineITems>------Products

          Time worked and expenses would be records in LineItems. You might or might not use a table in place of products (Use it if you have items for which you repeatedly bill customers.)

          With this structure, you can base your invoice print layout on LineItems with fields from the related Invoices record included on that layout. The Invoices starter solution uses this method so you can check out some more of the details there.

          Keeping your current structure, you can set up an invoices table and print from a layout based on that table, but with a portal to TimeWorked and a Portal to Expenses. You size your portals very large and use the Sliding and Visibility section of the Inspector's Position tab to set them to slide up and resize the enclosing part.

          A third option is to generate the report using save as PDF, creating the first part of the report on a layout based on time worked and then appending page(s) that come from a layout based on expenses. Both layouts would draw data from related invoice can customer tables. The main draw back here, besides the extra scripting needed, is that your invoice is now at least a two page document.

          • 2. Re: creating automatic invoices.
            weedonpaul

            "Keeping your current structure, you can set up an invoices table and print from a layout based on that table, but with a portal to TimeWorked and a Portal to Expenses. You size your portals very large and use the Sliding and Visibility section of the Inspector's Position tab to set them to slide up and resize the enclosing part."

            If i used the portal aproach can i use subheadings in it so that (using the example above), reports only apears once on the invoice and has the total for all time spent on reports next to it?

            thanks

            • 3. Re: creating automatic invoices.
              philmodjunk

              What do you mean by "reports"?

              • 4. Re: creating automatic invoices.
                weedonpaul

                i ment projects, i have it working now using your first suggestion. now to through a spanner in the works, as my dear wife pointed out some of our clients pay a fixed amount per month regardless of whar we do, she would however like to track their hours...

                So with that in mind, could I (how would I), set up a stament that say's if client pays monthly amount we disregard the subsummary by project and have a single   line that say's "months activites" and their "rate"?

                clear as mud I know, sorry

                • 5. Re: creating automatic invoices.
                  philmodjunk

                  I'd use a separate table for payments from that used for invoicing. That way customers can pay off multiple invoices with one payment or one invoice with several payments.

                  You can then use your invoicing to generate a monthly statement for your customer.

                  Hmmm, is it that you've agreed to accept a "flat fee" in place of the itemized charges in this case?

                  • 6. Re: creating automatic invoices.
                    weedonpaul

                    yes some clients pay a flat fee instead of itemized charges.

                    • 7. Re: creating automatic invoices.
                      philmodjunk

                      How is that fact recorded in your database? Do you have a field for that monthly fee in your customer table?

                      If so, you can use calculaiton fields in invoices that either return the total of the portal records or the flat fee as the amount to be billed.

                      IF ( Customer::MonthlyFee ; CustomerMonthlyFee ; //put summary field from portal table 1 + summary field from portal table 2 here to get the usual total of the two portals.)

                      It's even possible to set this up so that the customer sees what they would have paid on an itemized bill and the amount, hopefully, that they saved with their monthly fee.