9 Replies Latest reply on Jun 24, 2009 10:33 PM by MaineSteve

    Export problem with related records

    MaineSteve

      Title

      Export problem with related records

      Post

      Hi all,

       

      I've been using Filemaker Pro since before it was even called Filemaker 4, when IT Dinosaurs ruled the Earth :D  I'm just getting back into using FM Pro 7 after a long hiatus and am having some problems with the quirky aspects of how it deals with related records/fields.  I usually either figure out the right way to do it or bull through with a script.  However, I'm now stuck and am wasting hours trying to regain functionality I had and now lost.  I don't give up easily, but I'm really stuck.

       

      I have two Tables in a single file.  One contains "header" records and the other table "entries".  A header can have any number of entries, related via the header record's ID and a matching ID assigned to an entry at the time of creation.  I have a layout using the header table which has a portal for the entries.  Everything looks beautiful in the database, so I know I've got the basics covered such as proper relationships.

       

      The problem is when I export to a CSV file I'm only getting a single entry for each header record in-line with the header information (i.e. exported as a single record). What's even more puzzling is that it isn't the first one in the sort order!  Yet with the same setup yesterday it was (mostly) correctly exporting everything.  There was a problem which was explainable and when I went to fix that apparently I screwed up something else.  But WHAT?!?

       

      When exporting the following things are/aren't happening:

       

      1.  I am not using "Group by" function.  At least I never specified anything and when an option shows up I do not check it.

       

      2.  All records in both tables are shown and are sorted according to the common ID number.

       

      This should be a simple problem to solve and yet I've not solved it :(  Any help would be greatly appreciated.

       

      Steve 

        • 1. Re: Export problem with related records
          comment_1
             If you want a "flat file", with header info repeated for every entry, do the export from the child table.
          • 2. Re: Export problem with related records
            MaineSteve
              

            I had thought about that, but unfortunately that's not what I need.  The way it looked before was exactly the way I wanted it, but for whatever reason I can't get it to do it anymore.  And the way I want it is the way it is supposed to be which is:

             

            HEADER1; ENTRY1

            EMPTY; ENTRY2

            EMPTY; ENTRY3

            HEADER2; ENTRY1

            EMPTY; ENTRY2

            EMPTY; ENTRY3

            EMPTY; ENTRY4

            HEADER3; ENTRY1

            EMPTY; ENTRY2

             

            Etc.

             

            At present I only get something like this:

             

            HEADER1; ENTRY3

            HEADER2; ENTRY1

            HEADER3; ENTRY2

             

            Thanks!

             

            Steve 

             

            • 3. Re: Export problem with related records
              comment_1
                 Are you sure you are not grouping the records when exporting? Because I cannot see another way to get what you say you're getting, if you're exporting from the header table.
              • 4. Re: Export problem with related records
                MaineSteve
                  

                Oh, I completely agree it sounds like "Group by" is causing the problem.  It's the only thing in all of the threads and help documentation I found that matches my situation.  The problem is that it's not enabled 

                 

                When I go into the Export dialog it says "(Unsorted)" in the "Group by" frame. There is nothing else in there.  When I try an export with a backup copy of the particular file it looks the same as my current file does.  The difference is the backup exports correctly and the current file does not.

                 

                Is it possible that I somehow triggered "Group by" and it's stuck in the on position even though it apparently isn't?

                 

                Steve 

                • 5. Re: Export problem with related records
                  MaineSteve
                    

                  Follow up...

                   

                  I figured out where things went wrong but not why.  Using my backup file I tried to reproduce the changes I made in stages to see when it stopped working correctly I found it.

                   

                  With the files unsorted Export's "Group by" says "(Unsorted)" and exports everything as it should be exported BUT not in the correct order.  So I went into both Tables and did a SORT to get things in the order that I wanted them to be in.  I then went back to Export and "Group by" now has my keyed (linked) field and a checkbox.  I do not check the checkbox because I do not want the "Group by" behavior to kick in.  But sure enough... when I hit Export, without doing a single other thing, I get a messed up Export file.

                   

                  How can this be?  Yes, Filemaker is now presenting me with the option to "Group by" my linked ID, but I am not checking it off so it shouldn't be used.  Yet the output clearly shows signs that it is.

                   

                  When this all started I was sure I did something wrong.  Now either the UI for this feature is completely unintuitive or there is a bug floating around in there somewhere.  Since nobody else seems to have this problem I don't know what to say because I am and it is consistent. 

                   

                  Steve 

                  • 6. Re: Export problem with related records
                    MaineSteve
                      

                    Another follow up...

                     

                    I have confirmed that Filemaker is "stuck" thinking I want a "Group by" that I never asked for.  I changed the Sort order for the related table and went to Export.  Filemaker informed me that I had changed the sort order and that it couldn't group the results any more.  GREAT!!!!  That's exactly what I want!!!!  But guess what?  It still exported incorrectly.

                     

                    I have tried the following things to "clear" whatever hidden variable got wrongly set:

                     

                    1.  Tried an Export Records script set.

                    2.  Performed new (selective) finds in one, the other, and both Tables to change the found sets.  I did not sort and did sort.

                    3.  Wiped out my fields in Export and put just ones from a single Table in, exported, then added back the related Table fields.

                    4.  Made new relationship, deleted old one.

                     

                    No go on any of these things.  Grrrrrrrrr... anybody have any ideas how I can get Filemaker to stop trying to Group and (more importantly) how I can get it to NOT decide, on its own, to group things?

                     

                    Steve 

                    • 7. Re: Export problem with related records
                      MaineSteve
                        

                      Yet another follow up...

                       

                      I should mention I don't have any Summary fields in either Table.

                       

                      To try and clear things out I saved a copy of the file and checked it.  Messed up export.  So I did a clone and then imported the data from the original file one table at a time.  Exporting works correctly.

                       

                      I tried sorting the related table to be in the correct order as it is in the primary table's portal.  The exporting still works fine, but the order of the related records is not correct. I sorted them again by a different method and they still exported in the same (incorrect) order.  Then I sorted in the order they were created, and yet the exported order was the same as it was before  (which doesn't follow any rules I can figure).  I thought records were exported in the order they are sorted in?

                       

                      This is killing me... 

                       

                      Steve 

                      • 8. Re: Export problem with related records
                        comment_1
                          

                        Re the first thing: you didn't say so (or otherwise), but it sounds like your export was scripted, and you didn't change the script.

                         

                        With regards to sorting: sorting the related records in their own table has no effect on the export from the header table. Child records will be exported in the order set for the relationship (or in the default order of creation, if you haven't set another order in the relationship's definition).

                        • 9. Re: Export problem with related records
                          MaineSteve
                            

                          Thanks for the quick response!  No, I'm using the menu for exporting.  I tried the Export Records script step just to see if I could work around the problem, but I get the same results.  Which isn't terribly surprising.

                           

                          AH!!  I forgot all about setting up the sort order in the relationship.  Thanks!  At least you help nail one of my issues.

                           

                          My current theory is that v7 on my Intel 10.5.x system is what's causing this.  There are a few other quirks, like keys no longer functioning until I tab to another program, that are likely due to the age difference.  I'm now thinking of upgrading to test that theory.

                           

                          Steve