I allways get stuck with the same type of problem.

For example:
Each record in a database contains a name of a person and field where
5 different flavours of
icecream can be marked.

Lets say: 
Person A likes flavour 1,2 and5
Person B likes flavour 2 and 3
Person C likes flavour 1
Person D likes flavour 1,2,3,4 and 5

Now I would like to print a list, sorted by flavour that schould look
like:
Flavour1
Person A
Person C
Person D

Flavour2
Person A
Person D  ....

..... i hope you get the point. How can I do this with style. I don't
really want to write the names of
the flavours hard into the script.

I would be very very happy to receve any kind of help or hint or link
where to look for an answer...

Thanks

Roman

You need to create a new file called "Flavours", with fields for the
person's ID and for the ice-cream flavour.

In the main file, create a relationship based on the person's ID field, make
it able to create new records and place portal based on that relationship on
the layout where the old check box field was (you won't need this anymore).
Format the flavour field as a popup list that  that references the value
list.

People enter their favourite flavours via the portal, creating a new record
in the Flavours file for each flavour.

To print a list of flavours and the people who like them, you need to create
a report in the Flavours file with a sub summary part for the flavour, and
ID field in the body part. The layout wizard will walk you through this. If
you want to be able to see the persons name as well as ID, you need to place
a related field (using a reverse relationship) from the main file in the
body part.

This should solve the problem as stated.  However, if it is more complicated
than that, you might want to look into many to many relationships.  Why?
Because a person can like many ice-cream flavours and an ice-cream flavour
can be liked by many different people.

There are two ways to set up a many to many relationship in FileMaker Pro:

1. 3 file technique: 2 parents (a file for people and a file for ice-cream )
with a common child
2. 2 file technique: 2 parents (linked by a multiline key).

There have been many posts on many to many relationships in this group,
including a few of my own.  You should be able to find them by doing a
search at 

http://www.google.com/advanced_group_search?hl=en

-- 

Bridget Eley

(to email direct, replace "DOT" with "." and remove ".invalid")


in article 4e2849b6.0309250955.5167648f@posting.google.com, Roman at
kusters@bluewin.ch wrote on 26/9/03 3:55 AM:

> I allways get stuck with the same type of problem.
> 
> For example:
> Each record in a database contains a name of a person and field where
> 5 different flavours of
> icecream can be marked.
> 
> Lets say: 
> Person A likes flavour 1,2 and5
> Person B likes flavour 2 and 3
> Person C likes flavour 1
> Person D likes flavour 1,2,3,4 and 5
> 
> Now I would like to print a list, sorted by flavour that schould look
> like:
> Flavour1
> Person A
> Person C
> Person D
> 
> Flavour2
> Person A
> Person D  ....
> 
> .... i hope you get the point. How can I do this with style. I don't
> really want to write the names of
> the flavours hard into the script.
> 
> I would be very very happy to receve any kind of help or hint or link
> where to look for an answer...
> 
> Thanks
> 
> Roman

I don't want to upset anyone but this is what I would do.
(Needs Excel.)
You will need to have tha data like this in FM.
"A common child" file.

Name      Flavour
Person A     1
Person A     2
Person A     5
Person B     2
Person B     3
Person C     1
Person D     1
Person D     2
Person D     3
Person D     4
Person D     5



Then via the Menu
File, Export, All Records, Save as ".mer" type file.
(No, we're not going to mailmerge them ...)
Save the file as WhoLikesWhichFlavour.mer
Now you open a blank Excel spreadsheet and you use the Data, Pivot Table
Wizard to make a Pivot Table based on External Data.

It is a bit confusing at first but once you have the hang of it its easy.

Advantages ?
You just make the Pivot Table once and - if you specify, when saving the
file, that you want it saved empty with the auto update feature, Excel will
do that just that, and display tyhe Table including any changes exported to
the .mer file.

FM can export new data to the .mer file at intervals with a script.

When some one opens Pivot Table in the Spreadsheet Excel can automatically
update the data from the WhoLikesWhichFlavour.mer file.
Or you can leave the Spreadsheet open and it can automatically update itself
at intervals.

The Pivot table can read records beyond the 64000 records (lines) limit that
Excel has in an ordinary spreadsheet.
Excel has built in functions for Grouping By Years, Quarters, Months etc.

Here's a few presntations that can be obtained with a few clicks.
Of course this in text because of the newsgroup format.

Number of Name Flavour
-------------------------------------
Name           1   2   3  4  5  Total
-------------------------------------
Person A       1   1         1    3
Person B           1   1          2
Person C       1                  1
Person D       1   1   1  1  1    5
-------------------------------------
Total          3   3   2  1  2   11
-------------------------------------


Number of Name
-----------------------
Name        Flavour Total
-------------------------
Person  A
             1         1
             2         1
             5         1
-------------------------
Total Person A         3
-------------------------
Person  B
             2         1
             3         1
-------------------------
Total Person B         2
-------------------------
Person C
             1         1
-------------------------
Total Person C         1
-------------------------
Person  D
             1         1
             2         1
             3         1
             4         1
             5         1
-------------------------
Total Person D         5
-------------------------
Total             11
-------------------------





Nunmber of Name
-------------------------
Flavour Name     Total
-------------------------
1     Person A     1
      Person C     1
      Person D     1
-------------------------
Total 1            3
-------------------------
2     Person A     1
      Person B     1
      Person D     1
-------------------------
Total 2            3
-------------------------
3     Person B     1
      Person D     1
-------------------------
Total 3            2
-------------------------
4     Person D     1
-------------------------
Total 4            1
-------------------------
5     Person A     1
      Person D     1
-------------------------
Total 5            2
-------------------------
Total             11
-------------------------



It is a bit difficult to be convincing about this as you can't see a Pivot
Table in a newsgroup.
If anyone wants more info, contact me.
Send me some data, I'll send back a table.

Seamus
at
Wanadoo
dot
fr