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:
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.
"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?
What do you mean by "reports"?
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
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?
yes some clients pay a flat fee instead of itemized charges.
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.