Hello good people!

I have a database where I am trying to count the number of times a
value from a value list appears in a field.

I would also like the report to slide up, showing only the values
represented (I have another field I am attempting to organize the
information by.

An example DB would be:

"Company" is a text field that I would like to use to organize the
report. Within "Company", I would like to display counts of each time
another field "Status" equals a value list. So if I have a value list
of:

In complete
In progress
Done

So I would like the report to show all companies, and under each
company, it would show how many records match each value. And it
would slide up to nothing if no records has "Status" equalling those
values.

Can someone help me? Can I clarify anything better?

"dvfx" <damienvfx (AT) gmail (DOT) com> wrote

You'll need a new Summary Field to do the counting.

s_StatusCounter Summary Count of Status

This one field can be used to count all the various summary totals you
want - it's simplay a matter of where in the report Layout it is put and how
you Sort the records.

Then you'll need to create the report Layout. Since you don't want any of
the rRecord details, you don't need a Body Part. The report Layout would be
something like:

SUMMARY REPORT //


[Company Name]


[s_StatusCount] records are [Status]


Page ##



where the [] denote Fields.

Now it is simply a matter of Finding the appropriate Records (e.g. Records
within last two months, or Records for the whole of 2007) and sorting them
in the same order as the Sub-summary Parts of the Layout.
i.e.
Company
Status

The Status Sort Order can be either A-Z alphabetically or based on the Value
List order, whichever you want.

Once the Records are Sorted you can go to the above Layout and view it in
Preview Mode or print it.

All done ... except that with this easy method you will get lines in the
report that say, for example:

0 records are In Progress

If you really have to leave out those lines then it gets more difficult.

Helpful Harry )

On Sep 15, 2:42*pm, "Your Name" <your.n... (AT) isp (DOT) com> wrote:
Thank you Harry. That is close, but as you mentioned, the displaying
of records for "0" is something my boss does not want.

I've tried to create a separate "Company" based table, and then
replacing occurrences of my primary table layouts with the Company
Table layouts. I've then also tried to make a portal in a report
layout that would slide up if it equals zero. That was a no go.

I've already gone to the trouble of creating subtotals for each
possible value, giving a value of 1, and creating total fields of
those values. I just can't seem to create a report that displays the
information I want.

If you know of another post that has a similar solution for me that
would be great. Thank you for your help Harry!

On Sep 15, 3:38*pm, dvfx <damien... (AT) gmail (DOT) com> wrote:
I take that back. This has done EXACTLY that. Thank you Harry, once
again you've lived up to your name :-)

"dvfx" <damienvfx (AT) gmail (DOT) com> wrote

D'oh! Yep, I was wrong in that last part.

Re-thinking it, of course it wouldn't show any "0" lines. With no records of
type "In Progress" (for example) in the sort order for a Company, FileMaker
wouldn't have any Summary Part of the report Layout to display / print for
that Status. I was confusing it with a calulated total in the normal Body
Part.

Helpful Harry )

On Sep 16, 5:22*am, "Your Name" <your.n... (AT) isp (DOT) com> wrote:
Helpful Harry, one last problem with this particular report. It all
seems to be running smooth, except I can not seem to get two summary
fields to subtract from each other. Here is the calculation field I
have set-up:

c_Remaining = s_Company - s_FinishedJobs

s_FinishedJobs is a summary field counting a few different exceptable
values:

c_FinishedJobs = Status = "Complete" or
Status = "Canceled" or
Status = "etcetc"

I end up with the [c_Remaining] field returning the total number of
completes. Not how many are remaining. I also attempted a variation
for [c_FinishedJobs] with an If calculations here:

[c_FinishedJobs] = If(Status = "Complete;1;0)
If(Status = "Canceled";1;0) or
If(Status = "etcetc";1;0)

All that does is return the total number of records in the DB. Which
makes sense to me, with so many opportunities for it to return a 0.
Any thoughts on this?

On 2010-09-15 23:58:41 -0700, dvfx <damienvfx (AT) gmail (DOT) com> said:

Rather than messing with math with summary fields, try having each line
calculate whether it's finished or not, then summarize THAT.

So, for each line, make a calc, fNotInProgress = Case(Status =
"canceled" or Status = "Complete" or {more tests for not in progress
here}... ; 1, 0)

Then make a summary field to summarize fNotInProgress. Much simpler
than trying to get a summary to subtract from another summary.

Lynn Allen
FM 10 Certified Developer

In article
<68033d7e-9049-4f06-a437-1aac1ff621da (AT) q16g2000prf (DOT) googlegroups.com>, dvfx
<damienvfx (AT) gmail (DOT) com> wrote:
Summary Fields only really work in Summary Parts of a Layout when viewed
in Preview Mode or printed, with the records appropriately sorted in the
case of Sub-summary parts.

For that reason it's not really possible to use Summary Fields in
Calculations, Script commands, Relationship definitions, etc. There is a
Get Summary / Get function that can be used to grab the correct values
from Summary Fields, but, as Lynn Allen suggested, you would be best to
calculate the figures on a "per record" level and then use a new Summary
field to total those calculated values.

If all you need to know is how many are remaining, then you need Fields
something like:

c_Remaining Calculation, Number Result, Unstored
= Case( Status = "Complete"; 0;
Status = "Canceled"; 0;
Status = "..."; 0;
1)

s_Remaining Summary
Total of c_Remaining

Depending on how many possible Status values there are for "Finished" and
"Unfinished" Records, it might be easier to make the tests look for the
unfinished Status values (remembering to swap the 0 and 1 result numbers).
e.g.
c_Remaining Calculation, Number Result, Unstored
= Case( Status = "In Progress"; 1;
Status = "Yet to Start"; 1;
Status = "..."; 1;
0)

Putting the new Summary Field in the appropriate Sub-summary Part of the
report Layout (the By Company one) will make it summarise just those
records for each Company or putting it in a Trailing Summary Part will
summarise all the Found Records.

Helpful Harry )

On Sep 16, 11:27*pm, Lynn Allen <l... (AT) semiotics (DOT) com> wrote:
Hahaha. I hadn't thought about that. Thank you, that worked
perfectly.