6 Replies Latest reply on Nov 26, 2014 8:08 PM by Elements

    SQL SEARCH

    Elements

      In a layout whose context is table 1 with a portal from table 2 How would I write a SQL search to sum values in a field, of filtered records in table 1 in which the value of the foreign key of table 2 which is sitting in table 1 = the value of the primary key of table 2

        • 1. Re: SQL SEARCH
          wimdecorte

          Elements wrote:

           

          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?

          • 2. Re: SQL SEARCH
            beverly

            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 --

            Beverly Voth

            --

            • 3. Re: SQL SEARCH
              Elements

              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'

               

              Thanks

              • 4. Re: SQL SEARCH
                Elements

                Hi

                 

                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

                 

                Thanks,

                Elements

                • 5. Re: SQL SEARCH
                  beverly

                  Since you cannot "sum" a filtered portal

                   

                  Ah, but you can do this:

                   

                  <http://filemakerhacks.com/2012/09/13/aggregates-in-filtered-portals/>

                   

                  HTH.

                   

                  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:

                  table2::date1

                   

                   

                   

                  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?

                   

                  Beverly

                   

                  Message was edited by: Beverly Voth - OOPS! I forgot the Group By before hitting 'send'

                  • 6. Re: SQL SEARCH
                    Elements

                    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.

                     

                    Fred