6 Replies Latest reply on Jan 9, 2015 7:15 AM by Cuelliam

    Generate invoice from Time records



      Generate invoice from Time records


      Can you help me adapt a filemaker database that was written many years ago to FM13?

      We are a service business and bill clients for our time, there are no products involved.
      I have a file where employees enter their time. Fields are: 
      Date    Job Number   Client   Activity   Time   Rate   Total

      I need to generate invoices from the records in this file. I’ve created a second table called Invoices. What is the best way to find all the time posted to a particular job number, sorted by activity? Should this be in the same file with 2 tables or in 2 separate files?

      Invoice detail needs to show a dollar figure for each activity within the job, for example:


      –Proofing . . . $00.00 (total dollar figure of all employees who did proofing on this job number)
      –Research. . . $00.00 (total dollar figure of all employees who did Research on this job number)
      –GRAND TOTAL of all proofing + research for this job number




        • 1. Re: Generate invoice from Time records

          My primary job is processing Accounts Payable.  I pay a number of invoices weekly for temp staffs' weekly time.  So I am visualizing their invoice to me.  Off of the top of my head, I would have a third table for the Product you Sell.  I would summarize the data in Table 3 for a week (or whatever time period).  [Actually there would be a 4th table -- Employees. ] 

          Using weekly billing as an example, each line of the invoice could be one employee for one week.   Time sheets could be printed from the Time Table (timecards) as the back up support.  

          Job codes would provide the breakdown of Proofing vs Research.

          • 2. Re: Generate invoice from Time records

            You'll probably want to invoice a specific project by both project and a range of dates--such as generating an invoice for all work done on Job Number 143 for the month of January 2015. Does that sound right or do you only ever bill once per project at the time it is completed?

            With either option you can set up an invoice for a specific job number that matches to either all your time records or just those over a specified date range via a relationship. You can then use a portal on the invoices layout to show the records and you can print an invoice from a layout based on your Time table if you set it up as list view and include fields from the Invoice table in the header, footer and grand summary layout parts. (The trick to using this layout is to perform a find or use Go To Related Records to pull up a found set of just the records that go with one invoice.)

            • 3. Re: Generate invoice from Time records

              Yes, that's the way we bill. The problem I have is that the way I've got it set up, the body contains the activities and I'm just generating a list that's too long (and redundant) for an invoice. (see attached screenshot).

              I need a way to combine all the proofreading by all employees during that time period onto one line with a single total, the same with all the research, etc. I there something wrong with the way I've set up the parts in the layout?


              • 4. Re: Generate invoice from Time records
                /files/0a3ee4ff7a/Parts.png 555x433
                • 5. Re: Generate invoice from Time records

                  make it a summary report where you replace the body with a sub summary layout part 'When sorted by" your activity field. The number amount can be a summary field that totals the field you have there now.

                  • 6. Re: Generate invoice from Time records

                    Thank you...that worked!