Hi,
I am wondering how to do the calculation in FM6.0

for one database, how to count the unique field numbe. for example

in one database, there is only field called textField, and there are
10 records,

5 records with textField = "w"
2 records with textField = "x"
3 records with textField = "y"

how to get the calculation result of 3

Thanks for any tips/

In article <1174054865.841847.195690 (AT) n59g2000hsh (DOT) googlegroups.com>,
"Yoyo" <xiangdong (AT) gmail (DOT) com> wrote:

It depends on what you want to do iwht the number.

If all you want is for it to appear on a printed report, then a Summary
field is the easiest way.
eg.
TextFieldCount Summary Count of textField

then create a layout something like:

[textField]

Body
------------

Count of [textField] = [TextFieldCount]

Sub-summary by textField (Trailing)
------------

Total records = [TextFieldCount]

Grand Summary Trailing
------------

Where [] denotes the fields themselves.

Sort the records by textField and then Preview / print this layout. The
records MUST be sorted for the Sub-summary part to work properly.

For the example records above you should get a report that looks like:

w
w
w
w
w

Count of w = 5

x
x

Count of w = 2

y
y
y

Count of y = 3

Total records = 10

If you want to, you can remove the Body part of the Layout and you
won't get all the record details printed, just the summary lines.




Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Hi,
Thanks for replying this message, but this is not what I want.

what we need is that in 10 records,
5 records with textField = "w"
2 records with textField = "x"
3 records with textField = "y"


How to set up the calculation fields or scripts to get the result
of 3
which is the number of unique recrods of w, x, y.





On Mar 16, 6:38 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:

In article <1174319445.967372.117780 (AT) n76g2000hsh (DOT) googlegroups.com>,
"Yoyo" <xiangdong (AT) gmail (DOT) com> wrote:

Opps! Sorry, I misunderstood where the "3" was coming from - I thought
it was the number of "y" records.

Again it depends on what you want to do with the number, but if you
just want the number to appear on a printed report, then you can still
use the Summary field approach, but need a little extra mathematical
trickery as well. (If you need an updating on-screen number rather than
a printout / Preview number, then you can do something similar using
Relationships and Calculation fields instead of Summary fields, but it
can slow down the database.)

First you need the same Summary field from the previous reply which
counts the number of occurrences of each field (ie. 5 "w" records, 2
"x" records and 3 "y" records).
eg.
TextFieldCount Summary Count of textField

Now comes the mathematical trickery. We need to give each record 1/Xth
of the appropriate Summary count so that when you Total these for each
textField value you'll get 1 - therefore when you total across all the
records you'll get 3 (for the above example data).

We can obtain this 1/Xth value by using the Get Summary function to
retrieve the sub-summary numbers for each value of textField.
eg.
OneXth Calculation, Number Result, Unstored
= 1 / Get Summary(TextFieldCount, textField)

By using the textField as the second parameter of the Get Summary
function, we're telling FileMaker to use textField as the beakdown /
sorting point, ie. a sub-total every time the value of textField
changes.

If you put this field on a Layout and then scroll through the records
you see that EVERY "w" record has given a value of 1/5 (or 0.2), every
"x" record has 1/2 (or 0.5) and every "y" record has 1/3 (or 0.3333).

We can now total these values using another Summary field. This will
give us a total of 1 for each different value of textField, and a grand
total that equals the number of different data values in textField.
eg.
UniqueRecordsCount Summary Total of OneXth

This field can now be used on a similar Layout to the previous reply.
Something like (I've left in the count of each record from the previous
reply too):

[textField] [OneXth]

Body
------------

Count of [textField] = [TextFieldCount]
No. of unqiue records = [UniqueRecordsCount]

Sub-summary by textField (Trailing)
------------

Total records = [TextFieldCount]
No. of unqiue records = [UniqueRecordsCount]

Grand Summary Trailing
------------

Where [] denotes the fields themselves.

Sort the records by textField and then Preview / print this layout. The
records MUST be sorted for the Sub-summary part to work properly - it
doesn't matter whether the sort order is ascending or descending or
custom, we simply need the records for each different textField data
value to be grouped together.

For the example records above you should get a report that looks like:

w 0.2
w 0.2
w 0.2
w 0.2
w 0.2

Count of w = 5
No. of unique records = 1

x 0.5
x 0.5

Count of w = 2
No. of unique records = 1

y 0.3333
y 0.3333
y 0.3333

Count of y = 3
No. of unique records = 1

Total records = 10
No. of unique records = 3

Again, you can leave out the Body part so you don't see individual
lines for every record. You can also leave out the Sub-summary part - I
only put that in so you can see the Summary field sub-totals to 1 for
each value of textField.

Note: If you get counts like 0.99999 or 1.222222 it will be because of
rounding errors. To get rid of these change the Number format of the
UniqueRecordCount field(s) on the Layout as Fixed Decimal with 0
decimal places.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)