combine 3 groups into 1

[jliu017]

Hello

I have data for 3 workshops, WS1, WS2 and WS3. One person can attend 1 or all 3 workshops. How can I combine the information into one report/list or something I can see who is in what workshop without going through 3 places?

I use 'find' to have a list of all 3 workshop enrolments, which looks like this:

Mary WS1 Inv#111 $100 Credit Card 5/6/12

Mary WS2 Inv#111 $125 Credit Card 5/6/12

Mary WS3 Inv#250 $50 Bank Transfer 9/6/12

Peter WS2 Inv#75 $125 cash 1/6/12

Peter WS3 Inv#75 $50 cash 1/6/12

I need something like this:

Mary WS1 WS2 WS3 Inv#111 $275 Credit Card 6/5/12

Peter WS2 WS3 Inv#75 $175 cash 1/6/12

Or maybe:

Mary WS1 WS2 WS3 Inv#111+ Inv#250 $275 Credit Card+Bank Transfer 6/5/12+9/6/12

Peter WS2 WS3 Inv#75 $175 cash 1/6/12

Or is there any other claver way without using 'find'?

Thank you

[eos]

Is Enrollment a standalone table, or (as it should be) a join table for Persons and Workshops? (A Payments and/or Invoice table wouldn't hurt, either.)

If it's the latter, you can do some trickery with relationships, calculations and a summary layout.

EDIT: If it's the former, you must throw some value lists into the mix. A wee bit complicated, but feasible.

[Lee Smith]

Automatic message

This topic has been moved from "FileMaker Product Family → FileMaker Pro 11" to "Database Schema & Business Logic → Relationships".

The General Topic FileMaker 11 is reserved for the discussion of the new functions and features that were introduced in that version of FileMaker.

Lee

[jliu017]

Thank you Lee.

Enrolment is a joint table for person and workshops. What kind of trickrey I can do? Thank you eos

[eos]

Enrolment is a joint table for person and workshops. What kind of trickrey I can do? Thank you eos

In short: create a self-join relationship for Enrolment via personID; create a field in Enrolment which for each record calculates a text string with a summary of the data in all Enrolment records for this person. Create a layout in Enrolment which has a sub-summary part sorted by Person (so only one record per person is displayed), but no body part. Place the person name and the field with the summarized information in the sub-summary part. Find all relevant Enrolment records and sort them by person name. This gives you a list of all attending persons in these workshops, each of which shows summary text.

You can get a list of unique workshop names quite easily by using List () with a Workshop TO behind the self-join TO (since there is only one enrolment per workshop per person), and summing up the amounts is also no problem.

A bit more challenging is it to get non-duplicate values for the other data which have no Parent table (Inv#xxx, payment method, payment date). You can solve this by creating several value lists with field data from related records from the self-join TO (starting at the original Enrolment TO) and then use the ValueListItems () function to get list with unique values.

If you use this system for a longer period of time, you need to consider the possibility that the same people will enroll in later workshops. In this case the method outlined above would also find older records for a person. Create a field to flag records as invoiced / non-active and use it to exclude older Enrolments in the self-join and when finding records.

Have fun! ;-)

[comment]

In short: create a self-join relationship for Enrolment via personID; create a field in Enrolment which for each record calculates a text string with a summary of the data in all Enrolment records for this person. Create a layout in Enrolment which has a sub-summary part sorted by Person (so only one record per person is displayed), but no body part. ...

Why not do the calculation in the People table, using the existing relationship/s?

[eos]

Why not do the calculation in the People table, using the existing relationship/s?

OK, then you could forego the summary layout and use a normal list view for printing, but unless this is a one-shot solution, you'd still need to add a filtered relationship to Enrolments and put the other relevant table(s) behind it, to exclude values in the old records from the calculations.

Maybe the OP can shed some light on this?

[comment]

I haven't seen any excluding requirements, even though the example could certainly be clearer.