Is company a field in Customer? Is there more than one customer from the same company?
I'd base the report layout on LineItems, given the structure you have there. (Many would link projects to invoice rather than lineitems, but then may be one invoice can bill on more than one project?)
You can perform a find on this layout for all line items falling in a given date range and which have a related customer record with the specified company name.
Summary fields can then compute the total. (And there are ways to get just this total if you don't want to see the individual items.)
I've made projects link to lineitems because it is possible, like you say, to invoice for more than one project.
If the report is based in lineitems, how can I graph by project? I mean, how can I group all the projects together on the x-axis?
Thanks so much for you help.
Every line item links to one and only one project. Thus, if you sort by ProjectID (or a project name field in the related projects table if you want this to be alphabetical by a name.) Then you can use the grouped data for your graph. You will need summary fields to compute the totals and you may find that you need a calculation field that uses Get Summary to compute the project sub totals for your chart.
It's tempting to try this in Projects, but then finding records by a range of dates becomes problematic.
Thanks Phil. I've got the charts to work, now I want to do a report and struggling. For the charts I've used the calculation of sum(inv_total) to display total costs. How can I make a report like:
Project and costs
subtotal costs for company
Total costs for date period