7 Replies Latest reply on May 2, 2011 11:28 AM by philmodjunk

    How do I show only one of multiple records with highest value?

    JamesSmith

      Title

      How do I show only one of multiple records with highest value?

      Post

      I am trying to show fields from related record in a portal. There are multiple records with same related-value I want to show only the record with the highest value in a field, due date.

        • 1. Re: How do I show only one of multiple records with highest value?
          philmodjunk

          make it a portal set to show only one portal row.

          Use either the sort option in portal setup or the sort option in the relationship on which the portal is based to sort your records by the due date in descending order and only the record with the latest due date will be displayed in the portal.

          In fact, if you define the sort order for the relationship rather than the portal, you won't need a portal, you can just place the field or fields from the same table occurrence as what you'd select in Show Records From in the portal setup... dialog directly to your layout.

          • 2. Re: How do I show only one of multiple records with highest value?
            JamesSmith

            I have changed the sort order to descending order, had it assending, and the only difference is the record order, all the records are still listed. Just so it known I am using a filter on portal records to use only records with due date > 0.

            • 3. Re: How do I show only one of multiple records with highest value?
              philmodjunk

              Yes, but if you set the portal to only display one portal row, you will only see one record.

              • 4. Re: How do I show only one of multiple records with highest value?
                JamesSmith

                Yes that is true, but I have many different items and each of those items have multiple records. Maybe I need to rethink this.

                Can you have a portal within a portal, each using a diffrent table?

                How do you define a sort order for relationship?

                • 5. Re: How do I show only one of multiple records with highest value?
                  philmodjunk

                  Can't do a portal within a portal. Please describe the data in the portal's table, it's relationship to your layout's table and what you want to see here.

                  To specify a sort order for the relationship, you open up Manage | Database | Relationships, find the table occurrence boxes for the layout and portal, then double click the relationship line that links them. In the dialog that pops up, you'll find an option for specifying the sort order. Keep in mind that this sort order will affect all situations where you use this relationship, not just the portal you are working with and the results for that portal will look exactly the same as you would if you used the sort order in the portal setup...

                  • 6. Re: How do I show only one of multiple records with highest value?
                    JamesSmith

                    What I have a a vehicle maintenance data base with records for vehicles, components, service, and some additional records. Some components get PM some don't.

                    I want to show on the front "page", an area that show PM due list, (for a selected vehicle), in addition to "menu" buttons for data entry and reports selection.

                    I already have an report showin a PM Due List. Which work ok. I would just like to have a 'quick' list on the 'front page'.

                    I just have some space on the front 'page' I wanted to fill up. I guess this was just a wild idea and need think of some else.

                    Thanks anyway.

                    • 7. Re: How do I show only one of multiple records with highest value?
                      philmodjunk

                      It can be done and fairly easily, it's just that I only have a partial picture (even now) of what you are trying to do. Once we nail down the details, we can do exactly what you want here.

                      One more question: For a given vehicle, is it possible that there could be more than one PM Due record that you want to display? If so, let's work out exactly which records you want to see and which that you don't so that we can set up a portal filter that limits the records shown only to those records.

                      For example, here's an expression that filters the portal to only list those records that have PM due date, but which do not have a PM Performed date:

                      Not IsEmpty ( PortalTable::PM Due ) and IsEmpty ( PortalTable::PM Performed )