Trying to get a simple count report
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.
YEAR (datefield) AS YEAR,
count(dateField) AS cnt
YEAR DESC , COUNT(datefield) DESC
To give me a simple list of counts by year
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,
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.