1 2 Previous Next 16 Replies Latest reply on Oct 7, 2016 1:44 PM by fmpdude

    ExecuteSQL Unique Count shows a "?"

    gremlin9297

      Hi everyone,

       

           Im trying to run a an an ExecuteSQL calculation that counts the unique SSN in a found set, this is the calculation im using:

       

      ExecuteSQL ("SELECT COUNT ( DISTINCT SSN) FROM \"36 Contributions \" " ; "" ; "" )

       

      SSN is the Field name

      36 Contributions is the Table name

       

      I'm placing the calculation in the Trailing Grand Summary, not sure why i'm not getting any results

       

      Any help would be appreciated thank you!

        • 1. Re: ExecuteSQL Unique Count shows a "?"
          beverly

          1. this would work:

           

          ValueCount( ExecuteSQL(" SELECT DISTINCT ssn FROM \"36 Contributions\" " ; "" ; "" ) )

           

          Since you are returning a return-delimited list from one field. ValueCount will tell you how many values in the list.

           

          2. although, your query should be correct

           

          3. you can place EvaluationError() around your ExecuteSQL() and if it returns an error code, let us know.

           

          4. also, this article is useful, if you have FMProAdvanced for using the DataViewer:

           

          beverly

          • 2. Re: ExecuteSQL Unique Count shows a "?"
            user19752

            There is an extra space ending of the table name.

            • 3. Re: ExecuteSQL Unique Count shows a "?"
              fmpdude

              Until FM has (some) useful error messages with ExecuteSQL in the currently brain-dead Data Viewer, you should consider using a separate tool like RazorSQL do craft queries using your actual FM database. Using a tool like RazorSQL you'll get SQL creation assist (imagine that) and real SQL errors to help you figure out what's going on. No extra tricks needed.

               

              If you're like me, you'll see that query generation is much, much faster using the external tool.

               

              Once you get the query working, you can then move it to FMP and add replaceable parameters or whatever to make ExecuteSQL syntax happy.

               

              Extra cool thing: RazorSQL is a generic tool (that is, not FMP-only like plug-ins) so it will work with any database that will accept JDBC connections (virtually all of them, including FMP).

               

              HOPE THIS HELPS.

              • 4. Re: ExecuteSQL Unique Count shows a "?"
                philmodjunk

                There are ways to get plain English SQL error messages in the data viewer. I use the custom function SQL.debug for that purpose.

                • 5. Re: ExecuteSQL Unique Count shows a "?"
                  fmpdude

                  Yes, but it's an extra (as in non-default) step, which should absolutely not be necessary considering the cost of FMP and the maturity of SQL.

                   

                  And, in the data viewer you don't get SQL query assist nor can you see more than a few lines in the tiny itsy-bitsy area at the bottom of the data viewer where you don't have control over the window panes positions, fonts, etc. I still can't resize the panes in the data viewer?  Come on.

                   

                  The external tool (like RazorSQL or virtually any tool that lets you do SQL against a JDBC connection) lets you browse a table, full-screen if you want, without those fields needing to be on a layout.

                   

                  HOPE THIS HELPS.

                  • 6. Re: ExecuteSQL Unique Count shows a "?"
                    beverly

                    Yes! It only works in DataViewer. The same principle as the article by TeamDF.

                     

                    Sent from miPhone

                    • 7. Re: ExecuteSQL Unique Count shows a "?"
                      philmodjunk

                      And it's a very easy extra step to add to the viewer. It takes maybe 10 seconds or so to add to the query in the viewer when it's needed.

                      • 8. Re: ExecuteSQL Unique Count shows a "?"
                        fmpdude

                        But you have to "remember" to do it. You should just GET SQL error messages by DEFAULT.

                         

                        And, the data viewer has so many other deficiencies as I noted above, at least for SQL it's just not, IMHO, worth it.  Data Viewer is fine for Let() or other commands where you don't need to see much data, but in those cases too there should be (again, IMHO) coding assist as with contemporary tools.

                         

                        Maye we just won't agree on this one, I guess.

                         

                        Try RazorSQL (as one example) and you'll never do another SQL in the data viewer. At least for any serious SQL work.

                        • 9. Re: ExecuteSQL Unique Count shows a "?"
                          philmodjunk

                          I don't have any trouble remembering to do it. If I get a ? and the reason isn't obvious, I take 10 seconds and enclose my original expression in the function call. not a big deal.

                           

                          I don't see that 10 second delay to be a reason not to use the data viewer. And in this thread, that was your only reason given for not using the Data Viewer.

                           

                          I do agree that FileMaker needs a much better tool for building and analyzing SQL expressions. I've actually created my own tool using custom functions for working with SQL to be used in conjunction with the data viewer for that reason.

                          • 10. Re: ExecuteSQL Unique Count shows a "?"
                            fmpdude

                            My point is the FM should be helping you out, by default, not the other way around. You shouldn't have to create your own SQL tool just to use SQL in FM effectively! The third party tool, which you shouldn't need either, does it already.

                             

                            FMP is a database product. I could understand the "?" if this was, say, Excel, or OpenOffice, but a multi-hundred dollar database product? Especially when free products do SQL with FileMaker better and more clearly than FM itself.

                             

                            It seems you missed my other reasons stated above why the data viewer isn't good for SQL (teeny tiny non-resizable panes, no SQL assist, no easy way to just browse a table in data viewer, for example.)

                             

                            Phil, I have the utmost respect in your abilities. Over many years, your comments and replies to me have been monumentally helpful and helped me learn FM much more than any of the books. I therefore value your comments, but on this point, I think we might just have a disagreement.

                             

                            Thanks Phil.

                            • 11. Re: ExecuteSQL Unique Count shows a "?"
                              philmodjunk

                              I don't disagree with you that FileMaker should be helping us out more. I've just said that in my last post. And I did just now miss that part of your earlier post. My original response was to point out that you are not correct that you can't get error messages in the data viewer as you can do that.

                               

                              So it seems we are really just arguing over the best way to augment the Data Viewer's limitations. For most uses, the small results pane is a minor pain. ExecuteSQL and List are the primary cases where this can be a bigger pain, but normally find it an annoyance that doesn't hold be back all that much as there are some pretty simple ways to still inspect a long list of results--from just scrolling thru them for a quick check to copy/pasting the text somewhere else such as into a word processor--I often have one open for note taking purposes anyway or onto a blank area of a layout.

                               

                              So to each their own and this isn't an argument I'm going to spend any more time on.

                              • 12. Re: ExecuteSQL Unique Count shows a "?"
                                beverly

                                Let us keep in mind: DataViewer in only available in FMP Advanced.

                                EvaluationError() while not as helpful as text in DV, does give some coded clue...

                                 

                                 

                                Sent from miPhone

                                • 13. Re: ExecuteSQL Unique Count shows a "?"
                                  gremlin9297

                                  Kinda want to face plant right now *sighs*. Can't believe I missed that. Thanks!

                                  • 14. Re: ExecuteSQL Unique Count shows a "?"
                                    fmpdude

                                    For me, the term "Data Viewer" is just a bit too generous based on the limited functionality.

                                     

                                    "Calculation checker" would be far more appropriate though in the CF editor you should be able to step through/debug those just like a script.

                                     

                                    OK, I'm done too.

                                     

                                    Thanks Phil.

                                    1 2 Previous Next