1 Reply Latest reply on Feb 5, 2015 11:45 AM by philmodjunk

    Time sheet and invoice



      Time sheet and invoice


      I am currently building a database for as small pr firm. I am creating the invoice solution and have currently got an 'invoice' table that links to 'line items' that links to 'projects' so currently i go to the invoice layout and add line items via a portal, which calls projects in. and 'invoices have a relationship with clients.

      However, we invoice based on a period of time, so this solution is not working. I would like to track time for each client continually and at any given moment create an invoice with a start and end period it then calls only the line items that are relevant. I would also like it so that one line item can only belong to one invoice (i guess this can be done by making the foreign key invoice number a unit value and linking it to invoice number).

      So my plan is to use the table 'line items' as a time sheet with a relationship with clients clientID = foreign key clientID etc

      How can i get invoices to only assign invoice numbers at time of invoice and to filter by date of line item is between start date and end date and is this the best solution?

        • 1. Re: Time sheet and invoice

          I bill one of my clients in this fashion. He's my previous employer and I agreed to check over his DB system each work day morning to make sure that it's in good shape. It only takes a few minutes each day so I only bill every few weeks.

          I set up this relationship:

          Invoice::DateStart > LineItems::Date And
          Invoice::DateEnd < LineITems::Date

          I only have one client on this plan, but if I had more, I'd add ClientID match fields as well. I create my line item records on a layout based on that table instead of via a portal. I can thus create a new record in Invoice, select Dates in DateStart and DateEnd. A portal shows the matching records so that I can confirm that I am getting the correct records. I then click a button that pulls up the lineItems in a list view with fields from Invoices added to complete the invoice, Save a PDF and email it off to him.