3 Replies Latest reply on Sep 13, 2013 2:07 PM by philmodjunk

    Help with related fields/sorting

    Atkins

      Title

      Help with related fields/sorting

      Post

           Hello,

           I'm having sort of a complex issue.  I think I know how it should work but am not having any luck.  I have two tables related through a key field.  This relationship is working fine in many of the areas I'm using it.  Here's what I'm trying to accomplish:

           I have a "report" layout that is displaying in List View.  It consists of maintenance records for specific building/room locations.  I have a field called Maintenance Log ID in one table that is a running unique serial number for each maintenance log created.  The other table, for room locations, is the table associated with this report list.  The problem occurs once I have multiple maintenance log records for a single room location.  The room location info populates in the list just fine, but I need the MOST RECENT maintenance log ID to be associated with each room record.  Right now I'm getting the FIRST maintenance log ID created for each room in my list.  Because these logs are time-specific and the purpose for logging them is to know how much time until the next maintenance needs to be done, I need to have the most recent record showing.

           I've tried setting up the maintenance log ID field in a portal and setting the sort order both ascending and descending, but with no luck.  I thought that through the two tables' relationship, using a portal, I would essentially have access to all the maintenance logs for each room location.  That doesn't appear to be the case as I still wind up with the first record created in my portal.

           I've tried to come up with other ways to achieve this but haven't had any luck.  Is there something I'm missing on how to use a portal in this situation, or how to set the default "sort" to make the latest record auto-populate instead of the first record?  If you need more info I'd be happy to dialogue.  I know this is complex and is rather difficult to describe here in this post.  Thanks for any help!

            

           In the image I've uploaded, the field on the left is the field I'm trying to work with.  You will see that it is displaying record '0023' which is the first maintenance log created for AG 00202.  What it should be displaying is '0025' which is the most recent maintenance log created for AG 00202.  Hopefully that provides a little clarity to what I'm trying to achieve.

      sc.jpg

        • 1. Re: Help with related fields/sorting
          raybaudi

               "how to set the default "sort" to make the latest record auto-populate instead of the first record"

               You'll need to sort the relationship, not the portal.

          • 2. Re: Help with related fields/sorting
            Atkins

                 I didn't think that would work because my relationship was not based on the field I needed to sort, but it actually did work!  Thanks for the tip.

            • 3. Re: Help with related fields/sorting
              philmodjunk
                   

                        I thought that through the two tables' relationship, using a portal, I would essentially have access to all the maintenance logs for each room location.

                   That is actually true. BUT, you have to specify WHICH of the potentially many maintenance log records you are trying to access or display data from. When a calculation refers to a field from a related table with no modifying function or you place a field from a related table directly on a layout, you get data from the "first" related record. If you haven't specified a sort order for the relationship as raybaudi suggests, you get the first related record ever created.

                   So to get to data from the most recently created record, you have to do one of the following:

                     
              1.           Use a sorted relationship that sorts the most recent record to be the first.
              2.      
              3.           Use the Last () function to access the last related record.
              4.      
              5.           Use GetNthRecord to access data from the nth related record
              6.      
              7.           Use a filtered and/or sorted portal. (but this is to display data only, this doesn't make that data accessible for a calculation.)
              8.      
              9.           Use aggregate functions such as sum, count, list, etc to pull together data from all the related records.