What you want is a "sum if" calculation but you have created an "If sum" calculation. The reference to Invoices_details2::Company references only the first related record. If that value is 1, all related records are summed. If it is not, you get zero. It is doing exactly what you set it up to do, not what you want it to do.
There are a number of ways to selectively sum related records:
a) you can use additional table occurrences set up a series of relationships where each relationship matches only to the values you want to sum.
b) you can set up a series of one row filtered portals where each portal filters for just one company. A summary field defined in your InvoiceDetails table can compute the needed total and it will only show the total of the records that pass the filter in this context.
c) Execute SQL can be used to list each company and a sub total all in one field. FMP 12 Tip: Summary Recaps (Portal Subtotals)
d) It may be possible to set up a portal for this if you have a table of companies with one record for each company.
I don't recommend that you set up a single field or one row portals for your company subtotals here. Those approaches require that you set this up in advance and if you end up with more companies listed in your portal than you have fields or one row portals to compute the sub totals, you have to alter the design of your database in order to get the needed sub totals. I would use either c) or d) to do this as they more readily adjust to different numbers of vendors listed in your portal.
Thank you very much for your answer. I will certaintly take a look at what you've written and to the sql too!
I have been thinking about this, and first of all I have to admit I'm far too ignorant on filemaker
I created a table of companies and matched the record of invoice_details2 with that table but my question is, how do I now sum up each company in Invoices1? dont I have to write an "if" again?. I may not have made the relations properly
The answer given in b), means I have to create as many portals as filters right? well if so that is no logical, so I stick to answer d) but I'm not sure how to solve it....
At this point, I don't know what your relationships are nor exactly what you want to appear in your report. The typical relationships in an invoicing database would be set up like this:
Companies::__pkCompanyID = Invoices::_fkCompanyID
Invoices::__pkInvoiceID = InvoiceDetails::_fkInvoiceID
Products::__pkProductiD = InvoiceDetails::_fkProductID
Each record in companies records a record for a different company. Each Record in Invoices records a different sales transaction to a different company and a portal to Invoice Details records the "line item" sales of different items that make up a given sales transaction.
With this setup a Creating Filemaker Pro summary reports--Tutorial created on a layout based on the InvoiceDetails table can be used to compute and show the totals for each product sold, grouped by Company.
If the company is NOT the customer, but the vendor--the company supplying you with the products you want to sell, the same report method works, but your relationships will be different.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained