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?
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"
Use a list or table view. Sort your records to put the top 10 first and omit all other records from the found set.
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.
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.
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)
Please explain what you mean by "found 10 times". Found in what?
In de database of the children. In all the records.
And this is all in the same table?
Let's assume that you have these fields in a table named "children":
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.
Retrieving data ...