Related records incorrectly exported?
I have, for the purposes of the post, a database with three tables related to books.
The main table, "Books" contains a BookID field which is a serial number assigned by FM.
There is also a table of contributors, who might be authors, editors or translators. Each of these records has a ContributorID field which is also a serial number assigned by FM.
To link the two together, there is a link table - Contributions. This has its own serial number, contribiution ID but, more importantly, it has a field for the TitleID, a field for ContributorID and a field for Role. So, each record tells me what sort of contribution is represents (author, editor, translator), which book and which person.
On the relationships diagram, both the Books and Contributors table are related to the Contributions table via their respective ID fields.
The contributions are shown on a portal along with the other information in the Books record and all seems to be fine.
When exporting data from related tables, Filemaker outputs the first of the related records with the other data about the book, it then takes a new line in the spreadsheet and outputs only the second of the related records, and so on until there are no more related records.
I just noticed, though, that in the export, the roles and the contributors are being mixed up where there are multiple contributors to a book. For example, one book has two authors, and editor and a translator. In the exported file, there are, indeed, two authors, an editor and a translator, but they're NOT on the same exported line as the correct people.
I can't see where I've gone wrong with my table structure and the fact that it displays correctly on the portal suggests that I've got it correct. So why is the exported data wrong? I've attached a shot of the portal and the resulting exported record in Excel.
Is this just a limitation of Filemaker or even a bug?
Can anyone shed some light on this, please?