One to many to many tooo many.....

[Singlequanta]

Greetings all. I'm having a problem understanding how to do a slightly more complex report out of several related tables. The project itself is a lot more complex than the test file I'm attaching, but understanding how to do the test example correctly will give me the necessary "know how" to do this right.

In essence, my primary table is a "company" table. Associated to this is a "Lot" table that defines how many lots are in each company (related via a company id index) Tertiary to this is the "contacts" table which provides three seperate contacts per lot per company (related via a contact ID index)

So...

1. In my attached example, there are five companies.

2. Each company has a random number of lots (in the real life project)

3. Each lot has three contacts (These contacts are actually imported through an odbc connection to an SQL database so this table needs to remain independant)

Eventually this data needs to be exported to a CSV file.

In the attached sample, there is a script which exports the data. If the data is just "exported" raw then the output file works. But if a find and/or sort are done on the company, then the export does not work at all.

Any ideas on what I'm doing wrong here? Eventually what I need to arrive at, is the ability to either find and/or sort the company data and export the related lots/contacts based on that sorted subset.

Any help is greatly appreciated and will be returned with vast quantities of Gold and Silver.... OK, I'll send you an E-card..

help.zip

[-Queue-]

There is a bug when sorting and exporting. It does not matter which field you sort by or whether you select a 'summarize by' field in the export. It will result in a goofy export as soon as you dare sort.

[Singlequanta]

NNNNNNNNnnnoooooooooooooooooooooooooooooooooooo!

LOL! I'm assuming that the latest "patch" fixes this? I'm just downloading it now.

[-Queue-]

Unfortunately, it doesn't. :c(

A temporary workaround may be to use an additional table and import all the data from your other tables, so that you can make it a one table report. Perform your export and then delete all records in it. Repeat as necessary.

Unless someone else can think of a better solution...

[Fenton]

I'm not sure I understand the full extent of the problem; but when has that ever stopped me from giving advice :-]

I'm assuming you want the export to look like the Works.csv, but which can be sorted? The problem is that FileMaker removes the data from subsequent records of the "summarized by" field, leaving only the 1st; which is what you want. But you don't want it to actually summarize by that field, ie., only return 1 record for each unique value of that sort field.

One way to do this is to sort by another field, which, when combined with the above field, would be unique; such as the Unit Index in this case. Then summarize by BOTH. That prevents FileMaker from removing the subsequent records, but removes the data from the 1st field in subsequent records; like your Works.csv file.

It works more or less just like a Summary Parts report would. If you summarized by 2 fields, the combination of which was unique, the 1st sorted field would be in the top Subsummary part, with the 2nd Subsummary part under it; but all records would show, just as if the 2nd part was the Body.

If you really only wanted 1 sort to the export, you could do the 2nd sort with a unique serial ID field, summarize by it, but don't export it.

Does this have anything to do with what you were asking?

[-Queue-]

Fenton, the problem is that it doesn't matter if you summarize by a field or not, as soon as you sort, exports get screwed up.

[Singlequanta]

LOL!!! I had to read Fentons reply a couple of times and think about it. It is Sort of what i need to accomplish -- you're both right.

To summarize I require and export that looks like "works.csv" This file generates when open the database and just "export" the data without finding/sorting anyting on the company table.

However, if you DO find/sort the company table and perform an export, you get the other two examples which don't work. They don't include the lot details for the subsequent relations.

it baffles me.

I've taked your advice Queue, and created an independant table that usings a somewhat complex script to go through all the tables and generate the report. It isn't the way I would have preferred to do it, but it does work.

Ideally I would like to to this "The right way" so I'd like to keep this issue open and ongoing in the hope that someone out there knows, and/or Filemaker fixes the bug...

[Fenton]

I can produce the Works.csv, after summarizing. It is kind of tricky though, trying to get the summarize fields into the Group by box. But it works for me.

And I don't know exactly what fields you're exporting, 'cause in Works.csv some of your name fields don't line up with the IDs. So I'm exporting all 3 Contacts.

Test2.zip