4 Replies Latest reply on Jan 17, 2015 9:14 AM by philmodjunk

    Trying to get a simple count report

    MorkAfur

      Title

      Trying to get a simple count report

      Post

      Since FMP does not allow an expression in a GROUP BY clause, I've tried to create some intermediate fields, but I always get to the point where FMP just won't give me the listing I want.

      Say you have two fields: a date field called "datefield" and a vendor id field called "vendorID" (we don't need the vendorID field for this query).

      In MySQL, the query, because I don't have to include everything in the select into the GROUP BY (unlike in FMP), this listing report took me maybe 2 minutes.

      SELECT
          YEAR (datefield) AS YEAR,
          count(dateField) AS cnt
      FROM
          fliers
      GROUP BY
          YEAR
      ORDER BY
          YEAR DESC , COUNT(datefield) DESC

      To give me a simple list of counts by year

      2015    2

      2013   2

      2012   1

      ------------------

      In FMP, trying to do the same thing is difficult at least for me today.

      Time spent so far: 3 hours!   Success: None.

      In the actual FMP portal table, I have fields with the full date each of these portal records was created. That "should" be enough for a count by this date. This is a "simple" one table query. I'm just trying to get a simple SQL to feed to the graph SQL so I can get a bar chart with these counts by year.

      I've tried creating an intermediate field like YEAR(FullDateField) and that gives me, as expected,

      2015

      2014

      etc.

      So far, so good...

      Yet, then to get the actual counts of the records in the same table for each of these years, I get totally stuck. If I try to create another fields like COUNT(YEAR(FullDateField)), FMP barfs.

      --------

      Would appreciate any suggestions how to get this simple listing report out of FMP. I could create a program and do it that way, but then, as far as I know, I can't feed that to the graph.

      Please let me know this posting isn't clear.

      Look forward to any suggestions.

      Thanks,

      - m

        • 1. Re: Trying to get a simple count report
          TKnTexas

          You need to create Summary field CountOfVendors (or whatever name).  On the left SELECT COUNT.  Then the field you want to count.   Place this field in a sub-summary part of the layout, that breaks on how you want the count, i.e. years.

          • 2. Re: Trying to get a simple count report
            MorkAfur

            Hey,

            Thanks for your reply, but my issue wasn't with creating a report, I have that working fine. Rather, it was having a SQL that could "feed" a graph. Based on the severe limitations of FMP SQL's GROUP BY, making it nearly unusable for certain simple reports, I'm going to just create a new table and write a script to populate it with the values (Year, count-by-year-sum) that the graph needs.

            So, instead of this taking several minutes, like it should, it will take several hours total (including time already spent).

            :(

            • 3. Re: Trying to get a simple count report
              MorkAfur

              Update - As I wrote above, I ended up creating a separate FMP table to hold the year and count of items per year from my "many" table implemented as a portal in the layout.

              The script to generate the data took about 30 minutes to write (add this time to the 3 or 4 hours yesterday of trying to get the "helper" fields to work with the SQL).

              The MySQL query took, MAYBE, 2 minutes (including creating the test table and a few sample data rows).

              FileMaker clearly took too much time in this case due to its SQL limitations.

              It's also possible that I'm not as proficient in FileMaker as many others since it's not my full-time product of choice.

              While in the past, I've been able to "supplement" the FIleMaker's "SQL" limitations with "helper fields" (since FMP won't allow expressions in GROUP BY!!!!). Yet, for this particular case, I couldn't figure out how to do a nested COUNT of a YEAR(datefield). FileMaker would either give me the total number of records in each of the calculated fields or all "1s" - since there was only 1 2010, 1 2011, etc., instead of the counts of dates for each of these years.

              I find some things in FMP really fast and easy yet other things, which seem like they should be quick and easy, I find cumbersome and time consuming compared with other database environments.

              - m

               

              • 4. Re: Trying to get a simple count report
                philmodjunk

                Since your are counting records, I would have thought (haven't tried it) , that Count (*) would have worked in this query...

                Wonder what result you would have gotten if you hadn't used the Count expression in the order by clause but used cnt instead?