4 Replies Latest reply on Dec 22, 2010 11:11 AM by philmodjunk

    Having a problem getting the correct data from related records on report

    bate01

      Title

      Having a problem getting the correct data from related records on report

      Post

      I need help with a script where it omits all "closed" revisions on the report and to filter the latest records of the revision from two related databases. 

      The relation is based on Course revision id number.

      On the report I am having an issue getting the lastest revision record from the related databases to show correctly. 

      I am not sure what to do.

      Any help is appreciated.

      Thanks.

        • 1. Re: Having a problem getting the correct data from related records on report
          philmodjunk

          This is what I think you have:

          Revisions::Pk = RelTable1::FK

          Revisions::PK = RelTable2::FK

          Relations are from one revision record to many related records in the two related tables. You want the "most recent" related record.

          You also want to omit from your report all records where a specific field in Revisions = "closed".

          You have three options for getting most recent data from a related table.

          1) This function: Last ( RelTable1::Field1 ) will return the value of Field1 from the last related record. If the relationship from Revisions to RelTable1 does not specify a sort order, then this will be the value of Field1 from the most recent related record provided Field1 is not empty.

          2) If you specify a sort order in the relationship, you can specify an order that sorts your related records so that the most recent record is the first record in this sort order. You might sort by a date or timestamp field in descending order or by an auto-entered serial number in descending order. This option makes it easier to access multiple fields from the same record. You can simply place a field from RelTable1 on your Revisions layout and the data will come from the "most recent" related record. Note that this sort will affect any use of this relationship on any other layout--such as a portal used for data entry. You might need to create a new table occurrence so that you can specify your sort order and not affect other areas of your system that won't work correctly with this sort order.

          3) You can also display records from the related tables in one row portals and specify the sort order I described in 2 as the sort order for the portal. This doesn't make the values accessible to any calculation fields defined in Revisions, but avoids changing a relationship in a way that might affect its use in other contexts such as a portal on a different layout.

          • 2. Re: Having a problem getting the correct data from related records on report
            philmodjunk

            Oh yes, and to omit "closed" revision records, you simply need to perform a find that omits these records from the found set.

            You might use some variaton of this script to omit the closed records:

            Enter Find Mode []

            //Use set field steps to specify the criteria for records you want to include in your report here

            New Record/Request
            Set Field [ Revisions::Status ; "Closed" ]
            Omit Record // this is the same as clicking the omit button manually while in find mode.
            Set Error capture [on]
            Perform Find []
            Sort [Restore ; no dialog ]

            • 3. Re: Having a problem getting the correct data from related records on report
              KatieBates

              Thanks for the information! I am still having an issue so bare with me.

              I have the related dbs set up like you mentioned plus option 2 with an auto entered serial number. I need a report that pulls information from the parent db as well as the related db. 

              The issue I am having on the report is getting the data from the related database to show up on my reports with only the latest record. I must be missing a step. How do I set it to only give me the latest record? 

              For example: if Parent record id = test has 3 revisions in the related rev db then I only want the 3rd revision to show on my report. ie:Id = test rev = 3 etc. 

              I am getting id = test rev = 1

                  id = test rev = 2

                  id = test rev = 3

              What am I missing?

              I am new to using FileMaker.  I know what I want but am not sure how to translate that into FileMaker language.

              Thanks again.

              • 4. Re: Having a problem getting the correct data from related records on report
                philmodjunk

                In this case, you would base your layout on your Parent table. That means that in Show Records From in Layout setup, you'd select your Parent table from the list. This is actually the name of one of the boxes in Manage | Database | Relationships, so make sure you select the name of the "box" that you've linked to your two related tables.

                In Manage | Database | Relationships, find the relationship lines linking Parent to your two related tables and double click them in turn. A dialog box will appear where you can specify different details for your relationship. In your case, select the sort option for the related table, select your serial number field and specify a descending sort order.

                Return to your layout and use the field tool to add any fields you need from the related table directly to your layout. When you add fields from a related table and don't put them in a portal, filemaker will link your current record to the "first related" record in the other table. Since you have specified a sort order for your relationship, this "first" record will be the most recent record.