Title
Merge data from many records to one record
Post
Hi there,
I am a little stuck on this any help would be greatly appreciated.
The database we are using has records for performers positions in a circus show, each record has the fields...
Performer name
Show number
Act name
Position
Timestamp
Our team leader is very excel based and wants me to build a layout that shows all acts in the show across the top (please see attached image) and each performer down the list (also showing the last five shows) The cell turns red if the performer has done the act.
My question is..... how can I combine the values of one field, in many records, for one performer, in one show, into one record so i can achieve this style of layout.
For clarification the current records we use look like the below..
Please Note the sample data and the attached image do not match
Record Number Performer Name Show number Act
1 Aaron 20 Flip
2 Aaron 20 Run
3 Aaron 20 Clown
4 Aaron 21 High Wire
The record that I wish to make would look like the below
Record Nuber Performer name Show number Flip Run Clown High Wire
1 Aaron 20 Yes Yes Yes No
2 Aaron 21 No No No Yes
Any help or pointers you could provide would be greatly appreciated.
Cheers
J
Are you using fileMaker 11?
If so you do not need that added table.
This can be a list type summary report with filtered portals to organize the data into columns.
Using your screen shot for a reference. The first two rows (the performer's name and the column headings) would be a sub summary part "when sorted by Performer".
You'd then place the performance date formatted to show day of the week in column 1 of the body, leave space for the gap below the performer's name and then add 8 one row portals to your table, each with a filter expression to filter only for a specified act.
Your portals' relationship would look like:
Table::PerformerID = Table 2::PerformerID AND
Table::ShowNumber = Table 2::ShowNumber
I am using a performerID serial number here as PerformerNames are both non unique and subject to being changed where this is not the case with an ID number. (and should come from another table with one record for each performer.)