What version of FileMaker are you using?
If you are using FileMaker 12, you have the option of using the new Execute SQL function which can be used to produce such totals.
If you are using FileMaker 11, you have the option of using filtered portals to get the totals that you want.
If you are using a version older than 11, you can set up "filtered" relationships that produce the desired totals.
Can you confirm the following tables and relationships? Is this what you have?
Products::StoreID = Stores::StoreID
It looks like you really need three tables here, not two:
Products::ProductID = Prices::ProductID
Stores::StoreID = Prices::StoreID
This allows you to link one product to many stores and link many products to one store--a many to many relationship.
I'm using FM Pro Advanced 9. I'm thinking of getting the latest soon.
This is the relationship I have set up. I figured the stores were the children so I put a foreign key. I'm going to get rid of the PriceSale info. Just going to use the store name and price of the product.
This may be sufficient, but if so, I'd name that second table "Prices" rather than "store" since you have many records with the same store name. I think you'll get better flexibility if you add that third table like I described. (For one thing, you can identify each store with a unqiue serial number if you have a table of Store data with only one record for each store.)
Using names--not the best option as store names can change on you, and a bunch of calculation fields, you can do this in FileMaker 9.
You could, for example, define constSafeway as returning the text "Safeway" and constWinco as "Winco".
Product::constSafeway = SafewayStore::ID_Store
Product::constWinco = WincoStore::ID_Store
can be relationships that work for this.
SafewayStore and WincoStore would be new "occurrences" of the Store table. In Manage | Database | relationships, make a new table occurrence of Store by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as "SafewayStore".
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Repeat the process to get an occurrence named WincoStore.
If you define a summary field named sPriceTotal in Store to compute the total of the price field, you can put SafewayStore::sPriceTotal on your Product Layout to get a total of all prices for SafeWay and WincoStore::sPriceTotal would display the total of all Winco prices.
Not the most flexible approach and rather complex, but it works FileMaker versions 10 and older.
Thank you Phil. This sounds good.