three questions about reports, summaries, and selecting by value

[Nicholas Knouf <nknouf zeitkunst org>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

First, I wanted to thank all of the glom developers for such a wonderful
piece of software.  In a day I was able to setup a set of tables for a
film festival I'm working with that would have taken me much longer if I
had to roll my own solution via PHP/python.

Anyway, I'm having three problems with reports.  In the following question I'm going to be working from this example: I have a "festivals" table, with fields such as year and primary key 
festivals_id, a "guests" table with fields such as fullname, filmmaker (limited to "yes" or "no") and primary key "guest_id", and a "guestsToFestivals" table 
for my join that links the two.  In the user interface I'm able to successfully use the "Related Records" to automatically create entries in the "guestsToFestivals" table via the interface 
I've setup to add entires to the "guest" table.  All of my relationships seem to be setup properly because I'm able to access the appropriate guest entries by fields from the "festivals" 
table.

Now I'd like to create a set of reports.  Here are my questions (I'm
using 1.14.7 on Ubuntu Lucid, which seems to be the latest version I was
able to get to compile without needing to upgrade a bunch of libraries
by hand):

1)  From the festivals table, I'm able to group my results by year, getting the list of "fullname"s that are 
linked to a particular festival year.  I'd like to drill down a bit more, and group them by year, and then by whether 
or not they are a filmmaker.  This, however, does not work.  If I first try and group by year (by using the relation 
that pulls out the year from the festivals table) and then add a nested group by filmmaker (by using the relation that 
pulls out the filmmaker field from the guests table) and subsequently pulling out the fullname field, the report 
doesn't print anything.  I get the following sql errors:

Debug: Base_DB::query_execute_select():  SELECT "festivals"."year" FROM
"festivals" GROUP BY year
Debug: Base_DB::query_execute_select():  SELECT "guests"."filmmaker"
FROM "guests" WHERE "festivals"."year" = 2008 GROUP BY filmmaker
debug: Base_DB::query_execute_select(): ServerProviderError: exception
from statement_execute_select(): code=4message=missing FROM-clause entry
for table "festivals"
LINE 1: SELECT "guests"."filmmaker" FROM "guests" WHERE "festivals"....

This error is repeated for the other years.

(The SQL that produces the first, correct, result, is the following:

Debug: Base_DB::query_execute_select():  SELECT
"relationship_festivalToGuests_guest"."fullName" FROM "festivals" LEFT
JOIN     "guestsToFestivals" AS "relationship_festivalToGuests" ON
("festivals".         "festivals_id" =
"relationship_festivalToGuests"."festivals_id")  LEFT JOIN     "guests"
AS "relationship_festivalToGuests_guest" ON
("relationship_festivalToGuests"."guestID" =
"relationship_festivalToGuests_guest"."guests_id")  WHERE
"festivals"."year" =  2010
)

For some reason the nesting of the group by causes it to ignore the
relationships that I've setup (even though I'm explicitly using them in
the report interface).  What might be going on here?

2)  Returning to the previous question and report that works grouping by
year, I'm unable to get the summary counts to work properly.  For
example, I'd like to output the number of guests per year.  I try and
setup a Summary, with a Field Summary that does a count on fullname
(that is properly outputted, sorted by year).  But the count does not
appear, and I get the following SQL error:

Glom  Base_DB::query_execute_select(): Error while executing SQL
  SELECT COUNT("relationship_festivalToGuests_guest"."fullName") FROM
  "festivals"  LEFT OUTER JOIN "guestsToFestivals" AS
  "relationship_festivalToGuests" ON ("festivals"."festivals_id" =
  "relationship_festivalToGuests"."festivals_id") LEFT OUTER JOIN
  "guests" AS "relationship_festivalToGuests_guest" ON
  ("relationship_festivalToGuests"."guestID" =
  "relationship_festivalToGuests_guest"."guests_id") WHERE
  ("festivals"."year" = 2008) ORDER BY "festivals"."festivals_id" ASC
  Internal error (Database): column "festivals.festivals_id" must appear
  in the GROUP BY clause or be used in an aggregate function
  LINE 1: ...ts_id")  WHERE "festivals"."year" = 2008 ORDER BY
  "festivals...

This error I'm not able to grok...any help would be appreciated.

3)  Is it possible to select items for a report by value?  For example,
I'd like to only output those guests whose "filmmaker" column is set to
"Yes".  I've tried setting the format in the report interface to only
allow certain values, but this does not seem to have an effect.

Thanks in advance for any help.

nick knouf
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Topal (http://freshmeat.net/projects/topal)

iEYEARECAAYFAk1XDDkACgkQoHDFiKQ8nMkfiACgn2VYtMCdKhoTPoZyxuOvu2PC
+IMAnjZNYNBkeONcfM1YzN8HB6Dn5VmF
=cXFU
-----END PGP SIGNATURE-----