5 Replies Latest reply on Jan 25, 2013 4:43 PM by philmodjunk

    Using Where Statement on Aggregate function in Lookup Field

    WilliamK

      Title

      Using Where Statement on Aggregate function in Lookup Field

      Post

           I'm sure this question or ones simular to it have been asked many times, but I can't find an anwer that makes sense to me.  I have a table called EbayInvoices.  This table has the following fields.  "EbayItemID", FeeDate", "FeeType", "FeeAmount:". 

           I have a second table called FeeSummary that will show me a summary of all the fees related to a specific EbayItemID.  This table will have the Fields "EbayItemID", "TotalInsertFees", "TotalFinvalValueFees", TotalBuyItNowFees", etc.    The relationship between the two tables will be EbayItemID. 

           I want all the different fee fields in the FeeSummary table to be calculated look up fields.  So for instance the TotalInsertFees field needs to show me a sum of all the related records in the EbayInvoices Table where the FeeType field = "Insert Fee"

           In SQL I could do this with a SUM aggregate function and filter it with a where clause.  How can I do this in FM?  I am so used to working with SQL that I am have a hard time wrapping my head around how to do similar things in FM.  I know I could probably do this with a report and grouping, but I would rather have this be a table with permenant stored look up fields as I want to use this data in other ways.

        • 1. Re: Using Where Statement on Aggregate function in Lookup Field
          philmodjunk

               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:

               EbayItemID 1
                   TotalInsertFees
                   TotalFinalValueFees
                   TotalBuyNowFees

               EbayItemID 2
                   TotalInsertFees
                   TotalFinalValueFees
                   TotalBuyNowFees

               and so forth...

               Grand Totals

               And then there are these methods that you can use:  Sum_Calculation based on condition

          • 2. Re: Using Where Statement on Aggregate function in Lookup Field
            WilliamK

                 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" 

            • 3. Re: Using Where Statement on Aggregate function in Lookup Field
              philmodjunk

                   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.

              • 4. Re: Using Where Statement on Aggregate function in Lookup Field
                WilliamK

                     Hi,

                     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

                     Option 1:

                     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? 

                     Option 2:

                     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.

                • 5. Re: Using Where Statement on Aggregate function in Lookup Field
                  philmodjunk
                       

                            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.