4 Replies Latest reply on Dec 10, 2012 1:25 PM by cmspe

    Can Related Records Be Viewed Like Fields?

    cmspe

      Title

      Can Related Records Be Viewed Like Fields?

      Post

           I need to have a CSV file exported from FM Pro that has student names in the first column and each run grade in subsequent columns.  Currently, I have Table_Students with fields Student_Name and __pkStudent_ID.  I have another table, Table_Runs, with fields _fkStudent_ID, Run_#, and Run_Grade.  The table, Table_Runs, contains multiple runs for each student, 10 runs to be exact.  How do I do this, if I can do this?  The picture is of the CSV file I need.

           Table_Students                    Table_Runs

           __pkStudent_ID------------<_fkStudent_ID

           Student_Name                    Run_#

                                                          Run_Grade

      Screen_Shot_2012-12-07_at_10.06.11_PM.png

        • 1. Re: Can Related Records Be Viewed Like Fields?
          philmodjunk

               You have more challenges than just the 1 column for each run issue. You won't be able to recreate the header rows in your CSV file either without some extra work providing some "dummy" records to export for that purpose.

               Only way that I can think of is to set up a table for export purposes that has a field for each run. Those fields can be a calculation field that references data from different related records in Table_Runs. Either a separate relationship for each column or calculations using GetNthRecord may be used.

          • 2. Re: Can Related Records Be Viewed Like Fields?
            cmspe

                 I am trying calculation fields in Table_Students to get the related records to view as fields.  I am using GetNthRecord to find the right run.  It's all working except not in the order I want.  For example, I want the Endurance runs 1-8 to come up in that order and then Miles 1-2.  However, they're coming up in the order they're inputted into the database.  If I put the first mile score 3rd out of all 10 combined runs, it comes up as the 3rd record instead of the ninth.  I've sorted the relationship between the two tables, but it's not changing which records are 1st , 2nd, 3rd, etc.  What am I doing wrong?

            • 3. Re: Can Related Records Be Viewed Like Fields?
              philmodjunk

                   If you define a sort order in the relationship (Find the relationship line and double click it), the order of related records can be such that getNthrecord can refer to them in the desired order.

                   Since that isn't happening, you'll need to take a closer look at what you have set up. Be very careful as to the context you are specifying for the getNthRecord calculation fields as this might be a case where the relationship where you specified the sort order is not the relationship being referenced when the field evaluates. ("Context" is specified for a calculation field by selecting a table occurrence from the "context of" drop down at the very top of the specify calculation dialog.)

              • 4. Re: Can Related Records Be Viewed Like Fields?
                cmspe

                     Under "Storage Options", I checked "Do not store calculation results--recalculate when needed", and it showed the right results.