Report w/ omitted AND found items?

[gvmelbrty]

I was wondering if someone can help me w/ this problem?

I have a db to track letters sent to ten magazines. I generate a report every two weeks showing how many letters were received during the previous two weeks for each magazine.

Sometimes only a few of the magazines receive mail, so they don't show up in the report.

Question: Is there a way to include a statement in my magazine sub-summary report like, "BirdWorld & DogWorld received no mail this period"?

FIELDS:

magazine name (text)

mail received date (date)

Maybe looping in a found set to test for the presence of a magazine name? Or use of a related databse containing all ten magazine names? A self-relation of some sort? I'm stuck.

Thxs, Tom

[gvmelbrty]

I finally figured out a solution to this problem, but it took 32! additional fields to do it. Basically, assigning a value to each received-mail record, then totalling the value, then zero = no mail received, then a concatenation replacing the zeros with magazine names and placing them in a sentence.

Fortunately, the FMPro genius in Australia - Eric Sheid - came to my rescue. Since I couldn't afford to pay what his services are really worth, he offered to let me send him a book or two from his Amazon.com wish list. That's above and beyond fair and he's a great person to work with. If you've got some especially difficult FM problems to get solved, you can find him at [email protected].

Anyway, his solution was to create a second, related database containing ten records, one for each of the ten magazines, called "magazine database." The relation key in the primary database (called "letters database") is a calculation field combining the magazine ID (or name in this case) with today's date (or some other unique value). From the found set in the primary database, this key is set via a script and then placed into the other records in the found set via a replace.

A similar relation key is set up in the magazine database, a global field: magazine ID + today's date. Then, based on this relation, a related field is created: Count(letters_key::magazine_key). The zero values in this field are the magazines that received no letters, which was my objective. Much more elegant than 32 fields.

Thanks Eric.

-tom

[ January 17, 2002: Message edited by: gvmelbrty ]