Complex record set from many to many relationship

[Gabe]

I am having a problem with my relational logic here. I'm trying to create a record set that has data from two different many to many relationships.

Basically, I have a table full of lawsuit records, a table full of lawsuit party records, and a join table to show which parties are in a suit. I also have a dockets table, and a join table between dockets and suits to show which lawsuits show up on each docket.

I'm trying to build a relationship from these that will result in a complete docket report, which will show a list of dockets for a given date, and within each docket it will show which suits are listed, and within each suit, the list of parties involved.

Problem is, I cant figure out how to get a complete list of dockets with a complete list of party members from the two tables. Could somebody give me a hint as to how to set up a relationship that will give me access to the data I need?

[Gabe]

To simplify things a bit, I've narrowed it down to two of the tables.

I have a many to many relationship between the suitsindocket table, and the partiesinlawsuit table.

If I can figure out how to get a recordset that shows me every party for every suit thats in every docket, I can sort it out from there.

Basically I need to know how to get a record set that contains every possible combination in a many to many relationship. Possible?

[Søren Dyhr]

Countn't explain it with say "speakers" and "conferences" instead - I'm unfortunately loosing track with your entities ...what confuses me is that the word "suit" is used for both entities - which also happens in my language sometimes such as assigning "Firefighters" to a number of "Fireengines" ...the issue is that if you just find another more distinctive word e.g. person and vehicle and making "Fire" implied.

Explain what you wan't this way, so it makes sence to a pair of foreign ears!!!:

Some witers havn't written more than one book

Some writers have written more than one book

Some books are written by several writers

Some books are written solely by one writer

(many 2 many)

Opposed to:

A child has only one biological mother

A mother can have several children

("many 2 one" and a "one 2 many")

If you use a join table, put a name to it like assignments or calendar....

--sd

[Gabe]

Its not as complicated as I probably made it sound.

Within a docket there are a number of lawsuits. Within each lawsuit there are a number of parties.

There are basically two tables forming the basis of the record set I need. One is a table containing docket numbers and suit numbers, which is the set of which suits belong to which docket. Then, I have a table with party id's and suit numbers, which tells me which parties belong to which suit.

I have them set so that the suit number in the partiesinsuit table matches the suit number in the suitsindocket table. There is a many to many relationship between them.

So, I basically need to be able to draw a list of all possible combinations of those fields and be able to search on that list.

I need any kind of suggestion I can get.

[comment]

I am also not too sure what a docket means, but I think you want to do something like this:

Create a new columnar layout "PartiesBySuit" based on the partiesinlawsuit join table.

Add a sub-summary part, when sorted by SuitID, print above.

In the body part, put Parties::PartyName.

In the sub-summary part, put Suits::Suit

Now create the script for the report:

Go to Related Record [ From table: "partiesinlawsuit"; Using layout: "PartiesBySuit"] [ Show only related records ]

Sort Records [ partiesinlawsuit::SuitID ; ascending ]

Enter Preview Mode [ Pause ]

Enter Browse Mode

Go to Layout [ original layout ]

When you call this script from the Docket table, it will show you the parties related to the current docket, grouped by their respective suits.

This is just the basic mechanism - you will need to add a few elements, e.g. a global field identifying the calling docket. But it should give you an idea how it's supposed to work.

[Gabe]

A docket is basically just a list of suits that will appear before the judge on a given court date. They're just categorized according to certain types of things that will be dealt with.

Thanks A LOT for the tip, I will work on that.

[comment]

I have just noticed that you want "a complete docket report, which will show a list of dockets for a given date, and within each docket it will show which suits are listed, and within each suit, the list of parties involved."

This IMHO is not possible with the current structure. Let me try and explain why. Say we have 2 dockets, 2 suits and 2 parties. The same 2 parties are in both suits, and the same 2 suits are in both dockets. The report you want should look like this:

Docket 1

- Suit 1

-- Party 1

-- Party 2

- Suit 2

-- Party 1

-- Party 2

Docket 2

- Suit 1

-- Party 1

-- Party 2

- Suit 2

-- Party 1

-- Party 2

Now, there are 8 records needed for this report. But there's no table that has 8 records. This is the result of normalization.

So to generate this kind of report you will need to loop.

Either:

Go to the first docket:

Generate your first report;

Go to the next docket, etc.

Or:

Go to the first docket;

Go to related records in the Suit-Party join table (show related);

Go to a new Table, and import records from the Suit-Party join table;

Go to the next docket, etc.

[Gabe]

Yes.. I'm glad you understand why I'm stuck. You've hit the nail on the head.

Now.. I've programmed in multiple languages, so I understand what looping is about, however, I'm new to applying the concepts to filemaker.

I'm confused about both of your recommendations. First, I'm not sure if/how I can combine reports. You're saying to generate them one docket at a time, but how do I get a complete report out of several reports?

As for your second recommendation, I suppose youre telling me that I need to create a new table to copy all of the records I need into one place so that a report can be generated from there.

I think I'll try to work on the second option, but I'd still like to know if there is a way to string together different reports into one.

thanks again for the suggestions.

[comment]

Sorry, that wasn't very clear.

The first option means print a report of one docket, then do the next. To "string together different reports into one" you need to take the second route. Both options can be completely scripted, so the difference is not as great as it might seem.

Yes, you will need a new table to hold the records for the report. One thing that didn't occure to me then: you will want to export, probably to a text file, before importing - you want the related fields as well, and I don't think you can import those directly from a FMP table. So the loop would be something like:

Go to the first docket;

Go to related records in the Suit-Party join table (show related);

Export to text file;

Go to the Report table, and import records from the text file;

Go to the next docket, etc.

[Gabe]

Actually I set it to import from itself and it worked just fine. I dont know how it will work when I set it up under fm server. If it breaks, I'll keep your suggestion in mind (about the txt file).

Thanks again. Second option works great.