If you are using FileMaker 12, you can use that SQL with the ExecuteSQL function: FMP 12 Tip: Summary Recaps (Portal Subtotals)
If you do not have FileMaker 12, you can still get these totals but how you go about it is quite different from SQL.
You might consider the fact that you don't need the FeeSummary table at all to get these subtotals for each EbayItemID. A summary report based on the EbayInvoices table can produce a report that looks like this:
and so forth...
And then there are these methods that you can use: Sum_Calculation based on condition
Ok, so I am playing with the ExecuteSQL. In my table TBLAllListedItems I have a field called InsertFees. It is a calculated lookup field, field type number. The calculation is:
ExecuteSQL ( "SELECT Sum (i.Amount ) FROM TBLEbayInvoices i join TBLAllListedItems a on a.EbayItemId = i.EbayItemId WHERE i.FeeType = ?" ;"" ; "" ;"Insertion Fee")
The Amount field is a number field that displays amounts with currency signs. All I am getting is $0.00 for a resut when I should be getting $0.50. Do I need to specify Where i.EbayItemid = a.EbayItemid. That should be implied in the join shouldn't it?
So if I need to add addional clauses to the Where statement. How do I do that?
For example if I want to say "Where i.FeeType = "Insertion Fee" and i.FeeCode = "2"
We're all still figuring out what is possible and practical with ExecuteSQL().
Do you get any difference in results if you use:
ExecuteSQL ( "SELECT Sum (i.Amount ) FROM TBLEbayInvoices i join TBLAllListedItems a on a.EbayItemId = i.EbayItemId WHERE i.FeeType = \"Insertion Fee\"" ;"" ; "" ;)
I honestly don't see why that would make a difference, but since you are using literal text, you don't need the ? parameter here.
I'm also wondering if it would make a difference to use:
i.EbayItemId = a.EbayItemId
I would think that that expression also would produce identical results, however.
Make sure that ItemID in the two tables are both the same data type and that they really do exactly match values.
Is amount a field of type text or type number when you check the field type in Manage | Database | Fields?
Two clauses in a WHERE clause can be written exaclty like you have, but with the double quotes "escaped".
i.FeeType = /"Insertion Fee/" and i.FeeCode = 2
I don't think you'll need to put the 2 in quotes, but if FeeCode is a text field you can try it if you don't get the results you expect.
You may find the free SQL Explorer file from SeedCode helpful in working with ExecuteSQL.
Well I tried your ideas and no luck. The Amount field is a Number type field. So I was looking at some of your other options you mentiond in the post you directed me to
If you use sum, the relationship used, must fliter out the values you do not want summed.
So I created a calc field "IF" with value of Insertion Fee in TBLAllListed Items and defined the relationship to include EbayItemID and IF. Then defined the InsertFees field as a lookup Calc field using the standard Sum function. That works. Problem is I have several different fees I want to look up and put into different fields. I don't think I can create multiple relationships between the same two tables. And the fees I want to look up may not always be in the Invoice table, so if I use one relationship with a bunch of ANDs it will fail if only one fee is missing. Correct?
Define a portal and filter the portal to sum only the records you want.
This also works. A portal is not permenant storage is it? It just shows on the layout and will not actaully store the data in the TBLAllListedItems table like I want.
I have a third option that I know will work, just not sure it is practical. I could create 9 different tables, one table for each fee type. When I import an Ebay invoice, I will have to import it 9 times. Import into first fee table, find all records that are the fee type I want and delete all the others. Then I can create a relationship from each fee table to TBLAllListedItems and do lookups or whatever else I want. Could do this fairly easily all in one script.
Would there be a better way to duplicate data between tables or is the multiple import idea the easiest way? Sorry for so many questions in one post. Just thinking out loud.
I don't think I can create multiple relationships between the same two tables.
You can create as many different relationships between the same two tables as you need.
The secret is in creating additional Tutorial: What are Table Occurrences? so that you can create the additional relationships.
A portal is not permenant storage is it?
A portal is FileMaker's version of what is known as a "sub form" or "sub report" in MS Access. It's a way to view a group of records related to the current record in the layout's table.
Separate tables, generally speaking are NOT a good idea for this. Trying to report the results in a combined report are much more difficult with this approach. Please note that a duplicate table occurrence and a table are not the same thing.
The execute SQL method WILL work by the way. I've gotten it to work in my test files. But there's some issue in either your data, the SQL or your tables that we haven't identified here that's keeping it from working.