search to sum values in a field, of filtered records in table 1
This is confusing. Do you want to find records in table 1 or table 2?
El, if you where to make a relationship between the two tables, what would it be? If you were to create a summary field in which table would you place it and in which table would you view it? (the relationship can narrow down the summary field)
Please define more of how you think the SQL should be and why you desire to use SQL rather than FM relationships and summaries.
-- sent from myPhone --
Table 1 is cash receipts and table 2 is Order Items. Order Items are created as a result of accepting Cash Receipts. We are showing the Order Items in a filtered portal in the context of Table 1 because we only want to show the Order Items which are being created as a result of this Cash Receipt. Since you cannot "sum" a filtered portal, in order to get a total of the amounts of each of the Order Items I created a zz_Sum field in Table 2 and showed in within the portal and that works well. However, I also need to show that same value in Table 1 because when I subtract the total of the Order Items from the amount of the Cash Receipt I know whether or not the client sent in the correct amount of money. Cash Received (from Table 1) - Amount of Order Items (Table 2) should = 0. Since I can't get that to work within any reasonable relationship on the graph I decided to try and do a SQL search. So the formula in Table 1 would be: Table1::AmountReceived - SQL of OrderItemAmounts of all Order Items which have the same Cash Receipt ID as the ID of the Cash Receipt in which I'm sitting = 'n'
I want to find the total of the records in Table 2 while Table 2 is sitting in a filtered Portal on a layout whose context is Table 1
Since you cannot "sum" a filtered portal
Ah, but you can do this:
as for the SQL to do this...
Whatever your original relationship, whatever your 'filter', these are the same that would be in any WHERE (or as part of the JOIN) in the SQL clauses.
So if I have a relationship:
table1::field1 = table2::field1
with a filter:
table2::field5 = "active"
with a sort:
Then my SQL might be:
SELECT table1.field2, sum(table2.field3)
FROM table1 JOIN table2 ON table1.field1 = table2.field1
WHERE table2.field5 = 'active'
GROUP BY table1.field2
ORDER BY table2.date1
Do you see how what you would do 'old school' in FileMaker is very similar to the SQL?
Message was edited by: Beverly Voth - OOPS! I forgot the Group By before hitting 'send'
yes, that works. Thanks. The first article was great.
I'm going to post a question on Bank Reconciliations, would appreciate your taking a look.