Re: Group by - in FileMaker Pro 9 Advanced
- From: Hans Besjes <besjes@xxxxxxx>
- Date: Sun, 30 Nov 2008 18:29:33 +0100
Hi Per,
I do not have you original posting, but only the bits from this reply, so I may be of a little...
I think however, that what you want can be achieve quite easily. The method I will be describing works easiest and most elegant for fields with only 1 "state", either TRUE (= 1) or empty. The method can be adapted to handle fields with more "states", but looses elegance with growing number of "states".
Althought FileMaker may not have a separate boolean field type, you can quite easy (and you should) work with a number field to track things that are "true" or "false". As stated in the FileMaker manual, any zero value (or empty field) will be trated as "false", any non-zero value will be treated as "true". When you assign values to such a "boolean" field you should learn to use only 0 (and passibly sometimes empty fields) and 1 values to assign false or true statusses.
In your case there are 2 "states" to a record: when AC is TRUE, or when AC is FALSE.
To be able to realiably get you a count of these states in your report, we have to create 2 extra calculation fields:
- Mark.AC.True, and
- Mark.AC.False.
You will have to make your own calculations to define the conditions for the true or false state. Mark.AC.True should be assigned a 1 value when AC is true, and a 0 (zero) value when it's not. Mark.AC.False can then easily be defined by assiging it a value (by calculation) of "not Mark.AC.True".
Now you create 2 summary fields:
- Total.Records.AC.True, and
- Total.Records.AC.False
These are defined respectively Total of Mark.AC.True and Total of Mark.AC.False.
Note that a Count summary field will not give correct results because of the 0 value for the false state!
Now you can place these summary fields in the footer of your report and get the results you wanted. And there is no need for extra sub-summary parts, no sorting needed, not even preview mode. The results are there always, even in browse mode.
Hans Besjes
On 09/29/2008 11:32:45 per@xxxxxxxxxxxxx (Per Rønne) wrote:
Lynn Allen <lynn@xxxxxxxxxxxxxxxxx> wrote:
On 2008-09-24 06:56:18 -0700, per@xxxxxxxxxxxxx (Per Rønne) said:
've got at field named ac that can take two values [in reality it should
had been a boolean but that type isn't really supported in FM]. I need
to list the number of records containing either value, in reality a:
select ac, count(ac) from where group by ac;
The information should be listed in the footer in a listing of the
records meeting the condition - which is a date condition.
I'm using FMP 9 Advanced in MacOS X Leopard. Does anybody know a
solution?
It had been much easier if the FMP 9 Bible 'knew' SQL ...
If you want to work in SQL, that's one thing. FM isn't SQL,
I know; I just gave the select statement to emphasize what I wanted done.
so you have to learn FM terms and techniques. I recommend a thorough
reading of the online help file for the terms listed below, and possibly
a third party reference manual to help you get more familiar with FM
conventions.
First let's examine some of your assumptions.
1. FM lacks Booleans. Well, no. It lacks a field type called Boolean,
true. However, if you define a number field, then attach a value list
with a single value of "1" and format the field as a checkbox using that
value list, you can toggle the field between one and empty. Please be
aware that in FM, there is a difference in behavior between a field that
is empty and a field with a value of 0 in it.
That, I had already found out.
Or the field can result in 1 or empty as the result of a calculation. For
instance, if one of the date fields you refer to is between certain other
dates, or is not empty.
You can also format a number AS a boolean, with custom readouts (as long
as they're less than 8 characters) for 1 and 0 values, such as Yes and
No. This leaves the underlying data untouched, while giving users a more
friendly readout.
2. A select statement isn't available. In FM, a combination of layouts
and finds is the equivalent of a query. So create a layout containing
the fields from the table you want to search. Then create a find (in a
script if this is something that needs to happen often) by entering Find
Mode, using Set Field to insert the data you're searching for in the
field(s) you're searching, and performing the find. During design &
testing, we do these finds manually before writing the script.
Quite sophistocated Finds are possible, including multiple AND, OR and
OMIT conditions.
3. Grouping data happens in the query. Not in FM. Once the data set is
found, one creates (or has already created) a subsummary report. That
is, once the data is sorted by the break field, the layout shows you the
data grouped by that break field in Preview mode or when printed. It
doesn't show in Browse mode. Creating subsummary reports can be quite
complex, but basic reports are easy, particulary when there is only one
break field involved.
Well, I have given up getting the values in the listing. Instead I have
made Boolean fields [Number fields with value list '1' and a checkbox] and
a form where the number of true are listed.
Not automatic but it works.
As for listing the information (what information?) in the footer, you may
need to create a summary field to aggregate whatever data you're
summarizing, and put that in the footer or a Trailing Grand Summary part.
.Sounds like you have a lot of learning to do! Good luck. It's not SQL,
but in many ways, it's a lot more fun.
- Prev by Date: Re: Print Output
- Next by Date: Re: Editing Permissions
- Previous by thread: Re: Print Output
- Next by thread: Re: Editing Permissions
- Index(es):
Relevant Pages
|
Loading
