7 Replies Latest reply on Jan 24, 2013 3:53 AM by beverly

    Count, summary, ExecuteSQL ... or another way?

    akkadian

      I'm trying to create a calculation which will give me the total number of books for sale with the same author.

      eg. I have 40,000 books in the database of which 25,000 are for sale. There are 30 Dan Brown books of which 20 are for sale. I want each of the Dan Brown records to have a field stating 20.

      And every other author to also have their quantity of books for sale listed in each of their records.

       

      Summary and aggregate type functions only work when the records are sorted which is not an option (because of the next step in my plans).

      I have tried Execute SQL with the following:

      ExecuteSQL ("SELECT COUNT (Author) FROM Books WHERE Author = Author)" ;"" ; "") but it doesn't seem to like a field name where the last "Author" is, and I suspect it wants a string.

       

      From my research so far ExecuteSQL appears to be the only way to get the result I want but as I am very new to SQL I would appreciate some help in the matter. Any other alternatives would be welcome too.

        • 1. Re: Count, summary, ExecuteSQL ... or another way?
          phochstr

          Let's assume that only your last Author (of 3) actually denotes a field. I reference it as AuthorField below.

           

          You have to enclose literals in SQL with single quotation marks (').

           

          Therefore, you'd have to write your statement about like this:

           

          ExecuteSQL ("SELECT COUNT (Author) FROM Books WHERE Author = '" + AuthorField + "')" ;"" ; "")

           

          Copy above to get the sequence of double and single quotes correct.

          • 2. Re: Count, summary, ExecuteSQL ... or another way?
            beverly

            not quite there....

             

             

            ExecuteSQL ("SELECT COUNT (Author) FROM Books WHERE Author = '" + AuthorField + ' ")" ;"" ; "")

             

             

            This is what the arguments are for!

            ExecuteSQL (
                 " SELECT COUNT(Author)
                 FROM Books
                 WHERE Author = ? "
                 ; "" ; ""
                 ; Books::Author
            )
            

            If this is an unstored calculation AND in the Books table, it will supply the correct value (quoted properly as needed) for the "?"

             

            BTW: The "+" is the incorrect concatenation in the FMP calculation, and other errors.

            Beverly

            • 3. Re: Count, summary, ExecuteSQL ... or another way?
              akkadian

              I've tried both your suggestions but I keep getting "The specified field cannot be found" for the first ' .

               

              Let's assume that only your last Author (of 3) actually denotes a field. I reference it as AuthorField below.


              Actually all of the Authors in the query are the same field.

              • 4. Re: Count, summary, ExecuteSQL ... or another way?
                phochstr

                Beverly is spot on, using prepared statements is certainly the secure way to go.

                Should have tried it first, sorry for that.

                 

                P.

                • 5. Re: Count, summary, ExecuteSQL ... or another way?
                  PowerSlave

                  Be careful using the ExecuteSQL function if you have a large dataset and the result being shown in a list view as it will cause a large performance hit. The better (and faster) method to find the total number of books by the same author is to use a self-join relationship , a unique ID field and one non-stored calc field, and a global to store the value of books that are for sale.

                   

                  With the unique ID field (Unique_ID) , use a auto enter calc of Get(UUID) (don't forget to populate it with Get(UUID) if you've just created it)

                  With the non stored calc field (Found_Count), use Get(FoundCount)

                  With the global , put in a value that matches the value of any records that are for sale.

                   

                   

                  Create a self join in your book table from Author to Author, and where Unique_ID does not equal Unique_ID, and your global equals the 'for sale' field. (for name's sake , let's call the TO "Same_Author")

                   

                   

                  Then to show how many books are also written by the same author (not including the book you are looking at) , simply place the "Same_Author:Found_Count" field on your layout.

                  The advantage of this is your workstation won't have to 'read' every field of every related record from the server , but instead a very fast 'count' (and nothing more) will be returned.

                   

                  Another advantage is if you wanted to , you could just add a portal and 'show' all of the other books by the same author that are for sale, but that's out of context of your original question.

                   

                  Some FileMaker purists may argue that using executesql is the way to go, however after extensive testing making dashboards that look at large datasets , I have found that using TO's is much,much faster , and using 'too much' 'ExecuteSql' can render a solution unuseable.

                  • 6. Re: Count, summary, ExecuteSQL ... or another way?
                    akkadian

                    Thank you so much to everyone who helped. I'm getting the exact results I was hoping to acheive. In the end Power Slave's solution proved to be the answer.

                     

                    • 7. Re: Count, summary, ExecuteSQL ... or another way?
                      beverly

                      P. has some valid points. FM is a toolbox full of good tools. Use the proper/best one(s) for the task. 

                       

                      -- sent from my iPhone4 --

                      Beverly Voth

                      --