5 Replies Latest reply on Jul 1, 2011 2:31 PM by philmodjunk

    "Bookmarking" a portal row

    ScottNikiel

      Title

      "Bookmarking" a portal row

      Post

      I feel like this should be fairly easy to do, but I just can't find the right calculation. I have two tables related by a auto-entered serial id number from the main table. The second table serves as an optional list of records that apply only to the record in the main table that matches the id number. In the second table, there are two crucial fields, a date field and a text field. I would like to be able to be able to find and return the newest record from the second field (for each record in the main table) and its corresponding text field into a date field and text field (respectively) in the main table, preferably using calculated fields. Any ideas?

        • 1. Re: "Bookmarking" a portal row
          philmodjunk

          If the relationship to the related table does not specify a sort order, data from the most recently added record will be returned by the Last function.

          Last ( Relatedtable::TextField) would thus return the text from the most recently added record that is related to your main table and for which TextField is not empty.

          • 2. Re: "Bookmarking" a portal row
            ScottNikiel

            Thanks, that could work. I would prefer if it could specifically find the record with the most recent date field (as the date field can be altered at any time), but if that will complicate things too much, your first suggestion would work.

            Thanks,
            Scott 

            • 3. Re: "Bookmarking" a portal row
              philmodjunk

              That's just the most straight forward approach. You can also specify a sort order in a relationship to this file that sorts the records by date in ascending order. By using a different table occurrence for the related table, this change need not affect your current portal nor any other references to this table. So if you have this relationship:

              MainTable::PrimaryKey = SecondaryTable::ForeignKey

              but specify a sort order for SecondaryTable that sorts by the date field in descending order....

              Then a reference to SecondaryTable from the MainTable will automatically refer to the related record with the most recent date. This assumes you don't have two related records with the same date.

              • 4. Re: "Bookmarking" a portal row
                ScottNikiel

                I tried to do that, but the final date field in the main table wouldn't refresh. It's good to know that it should work, and that I probably just need to make sure that all of my fields are set to update automatically. Thanks!

                • 5. Re: "Bookmarking" a portal row
                  philmodjunk

                  First, make sure that the date field is really a field of type date or it won't sort correctly.

                  If that wasn't the issue, feel free to tell me exactly how it "didn't work" and I may be able to guide you through that to get it to work.