Hello, I am newbie to Filemaker, switching from access. I would like
to do the equivalent of a query which would look through all the
records in my database and perform a calculation. To make it simple,
say all the records have a Y/N field to say whether they have children
and a numeric field with the number of children. I would like to
compute how many of my friends have children and how many children on
average. How can I do such a thing - or I suspect, where do I get
help? The manual does not seem to consider doing queries an important
part of database working...

In article <1132236668.010738.156550 (AT) g43g2000cwa (DOT) googlegroups.com>,
"bucht" <nospam (AT) bucht (DOT) com> wrote:

Think Different

In FileMaker, a "query" is some combination of Find, Sort,
relationships, calculation fields, scripts, etc. It makes sense after
you understand that.

There are several ways of doing the specific "query" that you describe.

For the sake of discussion, I will use the name "Has Children" for your
y/n field, and the name "ChildrenNumber" for the number field that has
the number of children. We will also suppose your database table name is
"Contacts" and your basic layout is named "Basic Info"

Here is one method:


Define a Summary field named, say, ChildrenAverage. Define it to show
the Average of ChildrenNumber.

Make a layout that has a Trailing Grand Summary part. Put the field
ChildrenAverage in that part. Name this layout, say, "Children Average."

Do a Find for the records that have the value "y" in the field "Has
Children."

Go to the layout "Children Average." The average of the children in the
found set will be displayed.

You could write a script to do this in a single operation, something
like this (FMP 7 or 8; slightly more cumbersome in earlier versions):

Go to Layout[Children Average]
Enter Browse Mode
Perform Find [Contacts::HasChildren = "y"]


Bill Collins

--
For email, remove invalid.

In article <1132236668.010738.156550 (AT) g43g2000cwa (DOT) googlegroups.com>,
"bucht" <nospam (AT) bucht (DOT) com> wrote:

My earlier response neglected to tell you how to find out how many of
your friends have children. It told you how to calculate the average
number of children, but not how many have children.

After you do the Find for people where HasChildren = y, the number of
records in the found set is displayed in the toolbar on the left side of
the window. That is the answer.

You could also define another summary field that shows the Count of
Children, and put that in the grand summary part. That would enable y9ou
to print out the result showing both the number who have children and
the average number of children.

Bill Collins

--
For email, remove invalid.

Use FILE/DEFINE DATABASE to set fields to Calculation and specifiy the
desired calculation. This changes all records all of the time so it may
not suit your needs.

As a fellow convert from MS Access I assume you need the equivalent of
an update query. To do this FIND the records you want to change. Then
change to Table View (View/View as Table) so you can check the results
after the calculation. Click on any cell in the field (column) you wish
to calculate. Now select RECORDS/REPLACE FIELD CONTENTS. Now choose
REPLACE WITH CALCULATED RESULT. Specify you calculation, OK then
REPLACE. Confirm to wish to replace the contents of all the found
records. Done.

The other way is with scripting. You can semi-automate the above steps
with scripting and put a button on the form to do the calculation. As it
happens and plan to post my own questions on this subject.

Michael Paine

bucht wrote: