9 Replies Latest reply on Mar 14, 2013 1:45 AM by AlastairMcInnes

    Related records incorrectly exported?

    AlastairMcInnes

      Title

      Related records incorrectly exported?

      Post

           Hello,

           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?

      Authors.png

        • 1. Re: Related records incorrectly exported?
          philmodjunk

               Notice that role comes from contributions and the names come from contributors. I suspect that your "context" from which you are exporting your data is playing a role here.

               What layout is "current" at the time you export your records? (See "show records from" in Layout Setup...)

               To what Tutorial: What are Table Occurrences? does it refer?

               Exactly how are contributors and contributions linked to each other and that table occurrence?

          • 2. Re: Related records incorrectly exported?
            AlastairMcInnes

                 The layout is based on the Books table.

                 And, yes, the role comes from the contribution and the names from the contributor but they're related with a one-to-one join so there should be no way that the data gets mixed up, I wouldn't have thought.

                 I've tried creating (using the List function) a single field with all the related information in it. So, the Contribution table has a calculation field which is role & "~" & firstname & "~" & lastname. Within the Books table there's a field which joins all of those together with the List function. If I export that field, I get the data in the correct order and just have to unpack the data from the exported file.

            • 3. Re: Related records incorrectly exported?
              philmodjunk

                   To repeat:

                   Exactly how are contributors and contributions linked to each other and that table occurrence?

                   A screen shot of Manage | database | Relationships---something that you can upload here using the Upload an Image controls, would be a very good idea.

                   Your list function may be evaluating from a different context than that which is used for your data export.

                   

                        The layout is based on the Books table.

                   The precise table occurrence specified for your layout may be a factor here.

                   Are you exporting data from a script or manually?

              • 4. Re: Related records incorrectly exported?
                AlastairMcInnes

                     I've attached a bit of the relationship diagram to try to show what's going on. There are several other tables containing reviews, website categories for the book, languages, different editions, and so on, but as far as this problem goes, it's the title itself and its related contributions that are causing the problem.

                     The books (in the titles table) and the contributors are in a many-to-many relationship. To model that, the Contributions table acts as the link table and also stores the role that the contribution represents.

                     You can see here in the field list that the Contribution record is also keeping track (in an unstored calculation) of the contributor details in a single field - ExportContributors. Using the List function (and substituting "|" chars for the line breaks) this feeds into the unstored calculated field ExportContributions in the Titles table. That's what I was talking about in the List function bit.

                     The data is normally exported by a script but I've tried it manually as well with no difference in the result.

                • 5. Re: Related records incorrectly exported?
                  philmodjunk

                       Books= Titles?

                       And your current layout when exporting records is based on Titles? (The exact word Titles appears in "Show Records From" in layout setup...?)

                       Are you using Export Records or Save Records As | Excel

                        to export the data?

                  • 6. Re: Related records incorrectly exported?
                    AlastairMcInnes

                         Yes, Books = Titles. I was simplifying the description earlier. Just typing that, though, I realise that it's not, in fact, any simpler...

                         I've attached the layout properties screen.

                         I'm using Export Records (have tried both the menu option and the script step) because, it seems, Save As | Excel won't allow me to include related records at all. In that respect, the Save As option doesn't exhibit the symptoms, but it's not what I need to see.

                    • 7. Re: Related records incorrectly exported?
                      AlastairMcInnes

                           I'm not sure if this link will work, but I created a little sample database with just the three tables.

                           https://www.dropbox.com/s/ys7cex378oht2y1/ExportTest.fp7

                           There are three books in the Titles table - the first two have multiple contributors, whilst the third has none.

                           On the portal on the BrowseTitles layout, you can see the correct assignment of contributors to books.

                           Using the File -> Export Records option whilst on the BrowseTitles layout, I exported the TitleID, BookTitle, First Name, Last Name and Role from the entire Titles table. Book Title 2 exports correctly but Book Title 1 has the contributors wrongly assigned to their roles.

                           I'm wondering if it's to do with the order of creation of the contribution records. Book 2 is exactly as I first entered it whereas for Book 1 I've removed and then added back contributors.

                           However, there is a one-to-one relationship between the contribution record and the contributor record so I don't see how or why the data they hold isn't being properly displayed. I can only imagine that Filemaker is just treating all the conrtibutions and contributors as related to the title and ignoring the contribution -> contributor relationship.

                           Now I'm no database expert, but I've been using them for a long time and that seems like very odd behaviour to me.

                      • 8. Re: Related records incorrectly exported?
                        philmodjunk

                             Definitely looks like a bug to me. My guess is that Filemaker is having trouble with the related data that is more than "one remove" from the layout's table occurrence.

                             I recommend posting a bug report in Report an Issue. Include a link to this thread and a download link to the file that illustrates the issue.

                             As a work around, I was able to correctly export the data from the layout based on the join table. From there none of the exported data is more than one table occurrence away from the layout's TO. The resulting format, however, will look different as the book data will be repeated on each line of exported data.

                        • 9. Re: Related records incorrectly exported?
                          AlastairMcInnes

                               Thanks for the confirmation, Phil. I've posted a report as you suggest.

                               I think you're probably correct in your diagnosis that Filemaker is finding the contributors and contributions that are related to the title but not maintaining the "internal relationship" between the two.

                               We'll see what, if any, response there is.