Count no of sub summaries in report

[MitchBVI]

I am new to FM having worked with Access. I have searched the forum for an answer to this question without success and I apologize if I have done that incorrectly. I am working with an event planner where we have a number of available rooms some of which can accommodate more than one. I have a sub summary field that counts the no allocated to each room. I now want to count the number of sub summaries as this will be the number of rooms used. Can anyone help please.

Mitch

[bcooney]

I'd love to help, but I'm having difficulty inferring what tables and relations you have. My first instinct is that you are using summary fields, when in fact you should be using summary functions. When you say, "I have an event planner where we have a number of available rooms some of which can accomodate more than one", what do you mean? More than one event at a time?

To go on would be to guess. So, I'll wait for your answers.

[MitchBVI]

Thank you very much for your response. What I am doing at the moment is crude in the context of a relationship database in truth it is more of a flat-form. The situation is I have a number of rooms blocked booked. As attendees respond accepting (or not) I allocate them rooms. I can handle limited attendees and have limited number of rooms. Where I can I allocate two people to a room. What I want basically is a running total of the rooms used. Numbers as you will appreciate vary and for a number of reasons are not issued sequentially.

I have not found a way of seeing in browse mode the number of rooms allocated. What I have done so far is to sort by room #.

I have then added three fields to my database.

RoomCount which is a summary field by count.

RoomCountByHead which is a calculation field. This uses the GetSummary function with Room Count and Room # as the break field.

The last field is RoomsTotal again a summary field this time by total of RoomCountByHead.

This works fine in preview mode if I put the RoomsTotal field in a sub summary part but does not show up if I put it in a Trailing Grand Summary.

I maybe wasting you time for which I apologize because this sort of works. However what I really want is a way to see the number of rooms I have allocated as I make allocations. What I have so far I can only see in preview mode. I feel I need a custom function but am at a loss as to how I would go about that.

thanks again

Mitch

[SurferNate]

Mitch,

This is where the relationship comes in. You have only one table, but you need to have two Table Occurrences of this table on your graph. If you just want a summary of everything, you make a cartesian join, (X), then you can make a calculation that gets SUM(relatedtable::roomqty). The cartesian join just makes everything related to everything. Because it is all=all, you can use any text or number field for this join.

The idea is that with a relationship you can summarize almost anything you can filter, find, or sort.

[Søren Dyhr]

I disagree with Surfernate here. Take a look at the third example in "help":

http://www.filemaker.com/help/FunctionsRef-342.html

GetSummary(Total Sales;Total Sales) produces a summary of all records (similar to using a summary field, which is a total of total sales).

Similar could these alternative methods be studied:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000752

http://www.jonathanstark.com/downloads/FractionOfTotal.fp7.zip

--sd

[SurferNate]

While Søren Dyhr is far more experienced than I, and probably has a very good point, I would like to offer that the request was for a constantly updated summary of ALL records. Also..per the FM help file.

"when the database file is sorted by breakField."

meaning that the GetSummary function only works when you are sorted by the breakField.

Also, from the exact same help page

"Note You can get similar results using a self-join relationship and Aggregate functions. For more information, see Summarizing data in portals."

Which is exactly what I recommended, and is less dependent current sort order, and can also be creatively filtered by a match field in the parent TO.

Anyway

harrumph...

-)

[MitchBVI]

Hi Surfer Nate

Thank you as I said I am a newbie to FM and a cartesian join is at the moment beyond me I will work on it and thanks for your help.

Mitch

[MitchBVI]

Hi Soren

Thanks for the advice. Does this say I can summarize on the same field as the break field? What I was doing was dividing 1 by the number of records in the sub group which then added of course to 1 and adding the ones to return the total, crude I know.

thanks

[comment]

I think you need to clarify an important point: do you want to get the number of allocated rooms in the found set, or in the entire table?

If the former, the method you are using now is probably the best, and there aren't too many alternatives to it (a custom function comes to mind as one, but it would be rather slow, and you'd need the Advanced version to create it).

If the latter, you have more options. Probably the easiest one to implement would be to define a value list based on the room number. Value lists are automatically de-duped, so counting the items in the value list would give you the number of unique room numbers.

However, I would recommend you create a separate table of rooms, and relate it to your current table. Then it's easy to determine which/how many rooms are allocated (have a related record in the allocation table).

[Reed]

can you explain this approach of how you come up with the number of subsummary groups in more detail? I'm having trouble seeing what you mean. I've been trying to figure out how to count the number of groups and even number the groups sequentially.

[Reed]

OK now I get how you totalled the number of parts by calculating 1/getsummary (field;break) and then creating another summary field to find the total of that field.

But is there any way that one can actually number the subsummary parts sequentially?

(OK I just answered my own question... do a running total of the reciprocals)

Edited March 27, 2008 by Guest
answered my own question... twice

[Søren Dyhr]

Which is exactly what I recommended, and is less dependent current sort order, and can also be creatively filtered by a match field in the parent TO

Selfjoins ignores the found set, and scales apparently not as well as genuine summaries:

http://www.fmforums.com/forum/showpost.php?post/256765/

...and while at it, read the entire thread - if you should have a moment free!

--sd

[SurferNate]

Mitch,

A cartesian join is when you have two Table Occurrences on your graph, and instead of using field=field or another logical relationship like field>field or field

It just means that in either direction, all records in the foreign Table Occurrence are visible to the current record. This is one method one might use if one wanted to perform a "live" simple aggregate calculation of "everything".

I think that there are strong differing opinions on structure and reporting simply because FM allows one to do some things that are not "good data structure", but are easier to understand from a layperson's standpoint. (I myself falling squarely into that category of course).

I think as a general rule, and these folks might actually agree, that the bigger the job, the more critical it becomes that your structure be traditional and "normalized". For small projects and single user or very small group solutions, traditional rules can be bent in the name of convenience or fun, so long as FM allows it and performs as expected.

[SurferNate]

Soren,

I read that thread, and I'll have to read it again to figure out what the point is. There's a lot of back and forth about whether it's faster to report on stored or unstored calcs. I think I read at the end that it's six of one and half a dozen of the other.

In any case, I've been "gone" from posting and reading for quite a while, and come back to the conversations with a little more more hard-knocks DB development practice and a lot better understanding of the logical landscape now. It seems to me that you like reports a lot better than relational summaries. I'm curious about that, as I assume you have a very good and time-tested reason. Is it a paradigm that carries over from other DB work? Or is it FM specific?

[Søren Dyhr]

I think it's FM specific, relations arrived much later than the reporting tool, further more one thing you really should consider is that Filemakers strategic discourse doesn't pamper the developers demands in particular. Ernest Koe does indeed put things in perspective in this article:

http://proofgroup.com/articles/2006/jun/filemakery_part_i

...and if it doesn't put the last nail in coffin - does Ryan Rosenbergs statements here it:

http://www.filemaker.com/ltc_interview/09-22-2007

The thing is, we have to recognize their strategy, as something that certainly pays off, and urges us to look elsewhere, than the straight forward provisions in the tool, say inspiration if we have desires to implement even stronger abstractions in our solutions, than the tool in the first place were designed to do.

There is an urge to get crafty with the tool's provisions as fast as possible, since improvisations only works if you know a thing inside out.

--sd