4 Replies Latest reply on Mar 5, 2015 8:32 AM by MorkAfur

    How to get Summary Field Right?

    MorkAfur

      Title

      How to get Summary Field Right?

      Post

      Doing some "hack analysis" on my server, I'm trying to get a list (single table) of fields that confirm to the following SQL Query:

      SELECT
        max(DateOfHack) AS max_date,
        remoteIP,
        count(*) GroupCount
      FROM "firewall fields"
      WHERE YEAR (dateOfHack)= 2015
      GROUP BY DateOfHack, remoteIP HAVING COUNT(*) > 5
      ORDER BY GroupCount DESC, DateOfHack DESC

      Now, although this query took only about 5 minutes to get right, trying to do this in FMP with FMP tools on a FMP layout is causing me problems.

      In particular, I can't see quite how to get a summary field for a layout (like the group count of the individual IPs) that I can further limit via a FIND so I'm only looking at groups greater than a certain count.

      Even with the data sorted, a calculation field for COUNT(remoteIP) or a summary field (also for count) both give the wrong answer (1 vs. the total record count).

      This isn't a report, but a layout I'm trying to create for data analysis.

      Thanks in advance,

      - m

        • 1. Re: How to get Summary Field Right?
          philmodjunk

          A summary field can be set as teh "maximum of" DateOfHack.

          Another summary field can be defined as the "count of" any never empty field in your table in order to count your records.

          Summary fields will display "grand total" type results if placed in the body, header, footer, grand summary layout parts, but will display a sub total type result based on a group such as all records with the same dateOfHack or the same remoteIP if placed in a sub summary layout part "when sorted by" dateOfHack or "Whensortedby" remoteIP

          If you use sort records to sort your records by DateOfHack, RemoteIP, your recorsd will be grouped first by dateOfHack, then subgrouped by remoteIP. This has now covered everything but the "Having" clause.

          When you sort records, there's an option in the bottom of the dialog that you can use to "re-order" your sorted groups by the subtotal value of a summary field. So I think you might get what you want using this option followed by a script that omits records to where the subcount is 5 or less. GetSummary is a calculation that allows you to access the sub total value of a summary field for a given record.

          Not pretty or as simple as SQL (for those "fluent" in SQL) but there you are.

          I will also note that you could use ExecuteSQL to produce such a SQL query to list all of this inside a large scrolling single field or you could modify this query to produce just a list of Primary Keys from this table for all such records that meet your criteria and then you can use this calculation field as a match field to match to records in a self join and Go To Related records could be used to bring up a found set of just the records you need for your summary report.

          • 2. Re: How to get Summary Field Right?
            MorkAfur

            Thanks Phil. Great info.

            Hopefully, some FMP future version will let you define/view a layout by a SQL command giving you what you want in one simple command.

            Thanks again.

            - m

            • 3. Re: How to get Summary Field Right?
              philmodjunk

              That would be very nice. As I see it, FileMaker's "Query by Example" method for querying the database makes simple queries very simple and far easier that SQL to use in order to get what is needed. BUT, as the complexity of your search criteria increases, there's a "break even" point where an SQL query becomes simpler and faster to use (at least for those with the expertise to use SQL) than the corresponding combination of Find, Sort and scripting needed to get the same results in FileMaker.

              • 4. Re: How to get Summary Field Right?
                MorkAfur

                Very nicely put. I totally agree.

                In my current project's case, I opted for Java over filemaker since I needed to write an output text file and also ran into other issues.

                For example, the input file itself I was reading also had some inconsistencies that were cumbersome to deal with using the script editor in FM, but very easy when you have a large monitor you can dedicate to freely writing code in a window with no script editor. This file had a strange, and even inconsistent name-value pair format not supported directly by FM for import. Of course, reading one of your posts, I just imported the file directly into FM, and I could then get part way there, but that didn't really help that much due to the other issues with the file and my project's overall requirements.

                Moreover, I needed to be able to do the SQL (above in this post) and then programmatically read the ResultSet the sql creates, and, finally append some of the data from that SQL, to another file.

                Easy in Java (maybe not quick, perhaps, but straightforward).

                FM definitely has it's place, but I tend to use it like others use "Access": for personal productivity projects. To keep track of clients and things like that. I've also found that third-party add-ons can be expensive where just doing the project in Java/MySQL (Oracle & SQL Server too for development at least) is free from end to end. Having said that, of course, FM brings the great RAD environment if you need a quick front-end. Or, maybe just use FM for prototyping and initial screens for more complicated projects.

                It's great to have such wonderful tools to pick from for the particular project's requirements.

                Thanks as always for your posting, Phil!  :)

                - m