5 Replies Latest reply on Feb 13, 2009 4:07 PM by Sorbsbuster

    is there a way to maintain a sort order when exporting information displayed in a portal?

    kwseattle

      Title

      is there a way to maintain a sort order when exporting information displayed in a portal?

      Post

      I'm new to using the relational part of FileMaker, so this may be a basic question.

       

      I have information displayed in a portal, and it's nicely sorted.  When I export that info, it comes out in a different order than what's displayed.  Is there a way to maintain the sort on export?  

       

      This issue caused real problems when I was exporting information in the portal from two different related tables because the information from the two tables was sorted differently, and they didn't "match up" (see simple example below).  The first two fields came from one table; the second two came from another, but they were related through an ID.  

       

      Displayed results:

       

      Person_1_name    Person_1_ID    Person_1_ID   Person_1_grade

      Person_2_name    Person_2_ID    Person_2_ID   Person_2_grade

      Person_3_name    Person_3_ID    Person_3_ID   Person_3_grade

      Person_4_name    Person_4_ID    Person_4_ID   Person_4_grade

        

      Export results: 

       

      Person_4_name    Person_4_ID    Person_1_ID   Person_1_grade

      Person_1_name    Person_1_ID    Person_3_ID   Person_3_grade

      Person_3_name    Person_3_ID    Person_2_ID   Person_2_grade

      Person_2_name    Person_2_ID    Person_4_ID   Person_4_grade

         

      ---Karen 

        • 1. Re: is there a way to maintain a sort order when exporting information displayed in a portal?
          Orlando
            

          Hi Karen

           

          Can I check where you are sorting the portal? I think the issue is that you are sorting the Portal, on the layout, and not the Relationship, via the relationship diagram.

           

          Having just done a few tests I have noticed if you Save as Excel it will keep the sort of a portal on the layout, but if you File > Export... it will only refer to the sort order of the Relationship in the Relationship Graph.

           

          Give that a try and see if that does the trick. 

           

          Not knowing how you have your tables and relationships setup, but from what you are asking here a better way may be to get the only the same line as the Person record and just have one Portal on the layout, so no mismatch. The realtionships might look something like. Assuming you are looking at the records from an EXAM table.

           

          EXAM                Exam_PERSON      Exam_Person_GRADE 

          ExamID   --=--   ExamID

                                  PersonID   --=--   PersonID

           

           

          And then the fields in your portal would be 

           

          Exam_PERSON::Name

          Exam_Person_GRADE::Grade

           

          Now there is a lot more to it but this is a basic outline, as you may need to have the ExamID on the GRADE record to single out the record for that exam from other exams that person has taken, but like I say not knowing how you have it now its hard to guess.

           

          Please let me know if the answer at the beginning helps and also if you want any additional help with my suggestion.

          • 2. Re: is there a way to maintain a sort order when exporting information displayed in a portal?
            kwseattle
               <!--   StartFragment   -->

            Hi Orlando,

             

            Thanks for yourreply.  I’m new enough to the relational side of FileMaker that I havesome questions about what you wrote. I’ll start with a little more information about my database. 

             

            Here are therelationships:

             

            Participants               Participant enrollments         Course_information

            StudentNumID  --=--       StudentNumID

                                                   QtrID              --=--        QtrID

             

            Here’s the info about thelayout where I’ve run into problems doing the export.  There is a single portal:

             

            Layout based onCourse_information

            Portal setup is on Participant_enrollments

            Two fields in the portal are from Participant enrollments::StudentNumID and Participant enrollments::StudentNumID.  (I have more, but I'm using these to make sure that the data match up.)

             

            Is that my mistake?  Can I not have fields in a single portal that come from different-but-related tables?  FileMaker lets me do so, so I was figuring that it was OK.  Now that I'm re-editing this response, I'm realizing that I'm not actually exporting what's in the portal, so there's probably no reason for the data to be "matched up" when I export. 

             

            If that's not the issue:  I’m not sorting theportal, so I don’t think that’s the problem.  On the relationships setup dialog box, I tried sorting onStudentNumID on both Participants and Participant_enrollments in the dialoguebox that shows how those tables are related, but I still got a mismatch.  

             

            I did try making a highlysimplified database that has the same basic structure as the database that I’m working on, and Ididn’t have problems with exports there. However, I created the records very systematically, which might explainwhy I haven’t had problems.  (3 quarters worth of data, the same 6 students each quarter.)  Ichecked another database that I’ve been working on (where the relationshipswere set up by someone who understands how relationships work in FileMaker),and it has a similar problem with mis-matching on export.  It could very well be that I’m trying to do something that I really shouldn’t bedoing.  My husband (a softwareengineer who works with databases, just not ones in FileMaker) has been helpingme develop this database, but he’s new to FileMaker.  It's also true that I should have been a beta tester.  I have a skill for finding bugs (not that this necessarily is one).  

             

            ---Karen

            <!--   EndFragment   -->
            • 3. Re: is there a way to maintain a sort order when exporting information displayed in a portal?
              Sorbsbuster
                

              May I chip in with some questions, please?

               

              - Was it a typo that you say you have two fields in the portal (they are both exactly the same)?:

               

              Participant enrollments::StudentNumID

              and

              Participant enrollments::StudentNumID

               

              - Your first 'correct' example table shows a clear relation of:

              1 StudentName = 1 StudentID = 1 StudentGrade

              as if they only did one exam and and had only one grade, or had one overall Year Grade, maybe.

               

              If you have only a one-to-one relationship (both ways) then you would not need a portal.  You can simply start from the first table and drag the 4 fields from the starting table and the related table and they will match perfectly.  No sorting would be necessary, anywhere.

               

              - or is there the possibility that the relationship is one-to-many at the Grade level?:

               StudentName A = StudentID A = StudentGradeA Exam 1

                                                        = StudentGradeA Exam 2

                                                        = StudentGradeA Exam 3

               StudentName B = StudentID B = StudentGradeB Exam 1

                                                        = StudentGradeB Exam 2

                                                        = StudentGradeB Exam 3

               

              If that is the case I'm not sure what you are expecting to see in the export.  You will see one record for StudentNameA and one matching record from the Exams Table.  Depending upon the order of record creation (and the sort as defined by the relationship, not the portal display sort) you could get a different matched record than you expected.

               

              - If you were expecting in the case above to see 6 lines exported with the 3 results for 2 students, then you would have to start the export from the  Exams Table and pull StudentNames and IDs into those Exam Results records from the other tables.  Then you will get one complete, matched, set of data for each exam result.

               

              - Just a point of potential confusion - a portal is defined as being drawn from a particular relationship.  Filemaker will not stop you from dragging fields into that portal that are created from another relationship entirely.  They may even work - or look as if they work. But you just can't do it (not without the weirdest of results, anyway) - always and only have fields in a portal that are all defined by the same relationship.

               

              I hope I haven't missed the point entirely...

               

              Alan.

               

              • 4. Re: is there a way to maintain a sort order when exporting information displayed in a portal?
                kwseattle
                   <!--     StartFragment     -->

                Hi Alan,

                 

                Argh!  Yes, that was a typo:  The two fields in the portal are:

                 

                Participants::StudentNumID

                and

                Participant enrollments::StudentNumID

                 

                I have a one-to-many relationship between Participants and Participant enrollments.  Each participant may be enrolled in one of several courses each quarter. The “Participants” table has one record for each, well, participant.  The “Participant enrollments” table has one record for each participant each quarter that he or she is enrolled.  [The “Course_information” table has one record for each quarter, and the fields there describe what was offered.] So yes, there is a one-to-many relationship at the “grade” level. 

                 

                Since my layout is based on “Course_information” which has quarter as the key/ID, I get one record for each quarter.  I then set up the portal to show related records from “Participant enrollments,” which allows me to see a list of students (by StudentNumID) who were enrolled that quarter and what course they took.  Since we (as people) recognize our students by name rather than student number, I put in the names as well (even though they’re from a different, but related, table).  I think I need to just make the names show up in the “Participant enrollments” table, not just the “Participants” table. 

                 

                I that think your last paragraph contains the answer: FileMaker lets me do things that I shouldn’t.  It sounds like portals are intended to show records from just one table, not one or more related tables. The data display just fine, which makes it look like what I’m doing is OK, but I run into problems on the export. 

                 

                Thanks so much for your reply!

                 

                ---Karen

                <!--     EndFragment     -->
                • 5. Re: is there a way to maintain a sort order when exporting information displayed in a portal?
                  Sorbsbuster
                    

                  Two points:

                   

                  - yes, you have confirmed that you were putting fields from different relationships into a portal.  Results will be confusing at best - it's a no-can-do, I'm afraid.  I don't know why FM doesn't stop you doing it - they aren't stupid people, so I bet there is a very clever use for it somewhere, but I've never worked it out.

                   

                  - I think you are trying to export from 'up the line' a bit.  You should drive the export from the lowest granular level of data you will want to extract.  In this case, I think it is the Exam Results.  Start the export from that table, and pull the names and things down into the export from the other tables.  The flow of relationships from your Exam Results Table will always be flowing one-to-one-to-one (I believe - you'll know better).

                   

                  Alan.