6 Replies Latest reply on Oct 17, 2013 8:30 AM by michael_alexander@me.com

    Top 10



      Top 10



           I want to make a top 10 of all de names of the children in our database.

           A top 10 for boys and a top 10 for girls. There's a Field Name and a Field gender.

           Who can help me with a simple formula?


        • 1. Re: Top 10

               There is no formula needed. And you haven't indicated what will be used to determine which 10 are the "top ten". A value in a field?

               To show a "top ten"

               Option 1:

               Use a list or table view. Sort your records to put the top 10 first and omit all other records from the found set.

               Option 2:

               Use a portal to list the names, with a sort order on either the relationship or the portal to sort the related records so that the top 10 are first. Use a 10 row portal with no scroll bar.

               Option 3:

               If you use a sorted relationship that sorts the records, LeftValues ( List ( RelatedTable::Name ) ; 10 ) can return the 10 ten.

               Option 4: The above Leftvalues function can also be used with ExecuteSQL (filemaker 12 only) to produce the same list, but without needing a defined relationship in Manage | Database to do so.

          • 2. Re: Top 10

                 Hello and thank you for your quick response. I don't no if i understand te solution.

                 The 10 must show a list of the names like this:

                 1. Sem (Sem is found 10 times) the value (10) could be a value in the record Field that's ok.

                 2. Finn (Finn is found 5 times)

                 3. Levi (Levi is found 4 times)

                 4. ...

            • 3. Re: Top 10

                   Please explain what you mean by "found 10 times". Found in what?

              • 4. Re: Top 10

                     In de database of the children. In all the records. 


                • 5. Re: Top 10

                       And this is all in the same table?

                       Let's assume that you have these fields in a table named "children":

                       Name, Gender

                       Then you can set up a "self join" relationship like this:

                       Children::name = Children 2::name AND
                       Children::Gender = Children 2::Gender

                       Then Count ( Children2::name ) will return a count of all records in the Children table with the same name and gender as the current record. This then produces a field on which you can sort in descending order to show the most frequent name first.

                       This, in turn, makes any of the options that I specified at the start possible.

                       The self join relationship shown requires that you create a duplicate table occurrence (Children 2):

                       In Manage | Database | relationships, make a new table occurrence of Children by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be something other than Children 2 if you want.

                       We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                  • 6. Re: Top 10