3 Replies Latest reply on Jun 20, 2014 11:27 AM by trent.fowler@hci.utah.edu

    Trying to get most recent date from portal records

    vet_guy

      Title

      Trying to get most recent date from portal records

      Post

      I have a calculation that attempts to get the most recent date for a particular category in related records in a portal.

      The calc: If(Month from date ≤ 11 and Year from date = this year and category = 7; active; inactive). The calc grabs the 1st(eariest) record in the portal row. I've tried Max(date) which doesn't work.

      Any suggestions?

      Thanks

        • 1. Re: Trying to get most recent date from portal records
          philmodjunk

          When you have a one to many relationship (one record matches to more than one related records), a direct reference to fields in the related table will refer to the "First" related record. If the relationship is unsorted, that will be the oldest related record. To refer to the related record with the most recent date, specify a sort order for the relationship that sorts on that date field in descending order. This is an option controlled by a check box in the dialog that opens when you double click a relationship line in Manage |Database | Relationships.

          If you are also trying to isolate the most recent related record in a category, you can set up a sorted relationship that includes matching to a value in a category field. This can be done with a new relationship kept separate from existing relationships by making a new table occurrence of the portal's table.

          • 2. Re: Trying to get most recent date from portal records
            vet_guy

            Thanks. That works.

            • 3. Re: Trying to get most recent date from portal records
              trent.fowler@hci.utah.edu

                   Phil - Thanks for your answer to this post. As you said in the second part of your response I am attempting to isolate the most recent related record in a category. I would like to create a layout that has a list of the most recent child records from each parent. I have created a second occurrence of the child table and created a relationship between it and the parent table based on the primary field that is shared with between the tables. Now I need to create a second part of this relationship that essentially filters for the most recent date. What is the best way to do this?

                   Thanks!