Hard to say without knowing more about your setup. A summary report is probably the best approach for you, but would need to know how you've structured your invoice and customer data.
Does your invoice use a related line items table so that multiple items (hours maybe?) can be listed on a single invoice?
If so, what data do you record in this line items table?
Invoice has a relationship with contract and contract with the customer. The hours are on the invoice. For service we want to show (on every new invoice) the total hours of every earlier send invoices (for that specific year).
"The hours are on the invoice"
One field on each invoice record records the hours or are their multiple fields or a repeating field?
Assuming one field for hours plus the following: A ClientID field. A cYear field defined as a calculation field: Year (Invoice Date)
Create a new relationship between Invoices and a new table occurrence of Invoices linked by ClientID and Year to the first.
- Open Manage | Database | Relationships
- Drag from ClientID to outside the box and back to ClientID to create a new table occurrence of Invoices. Name the new occurrence, InvoiceClientYear.
- Drag from Inovices::cYear to InvoiceClientYear::cYear to add that pair of fields to the relationship
- Click the fields tab and define this field in the Invoices table: cYTDHours : Sum ( InvoiceClientYear::Hours )
- Put this field on your invoices layout to show the total hours for all invoices for the same client and year.
Thanks for your quick response!
I get the idea! I've done it but it will not work...no result?
Any other idea?
Your assuming is ok! It's not a ClientID but a customer field (selected form customer (also for the contract data).
No result means there are no matching records. It will work if set up correctly. You'll need to check your relationship, layout and data to figure out why it doesn't work.
Thanks! PhilModJunk you are the best!