Best result in current year
How to write the formula so as show the best result in the current year?
Best result of what data? Structured in what table or group of fields?
And what marks a value as "best"? Largest? Smallest? Most consistent? Closest to a specified target? or ???
Best result means highest value.
There is a table for personal profile and a separate table for results of competition.
I want to be able to list the best result (highest value) in the current year or even every single year pulling data from the table for results.
Does that separate table have a field for either the date or the year?
You can perform a find for for all records for a given year and sort then by this value in descending order. The first record will be the best score for the year.
Relationships and filtered portals can also use sorting to show the best value from a set of related records (such as all records for a given year).
And all of these methods can be modified to handle the situation of using one such table to record results for more than one type of competition.
I have attached an image showing the tables involve.
What I would like to have is to have a field in the individual personal profile page showing the best result this year and even best result yearly (e.g. best result at 13 years old, best result at 14 years old, etc.) without having to sort and find as every individual would have competed in a hundreds of competitions over a few years.
You wouldn't have to "sort and find" as this would be done for you as part of the portal and or relationship options you would specify for that portal or relationship. Please note that a more detailed description of what you wanted in your original post would have saved me time in describing a possible solution for you.
If you just wanted to see the largest "score" in your Results Main table for a given record in Ath Profile Main (I can't spot any one field in Results Main that is clearly the field you want here.), You simply double click the relationship line and specify that the records in Results Main be sorted by this score field in descending order. If you then put this score field from Results Main directly on your Ath Profile Main layout, it will show the best score ever recorded for that athlete.
Since you want to see this score for different ages, you can add one row filtered portals to Results Main to your profile layout and each can filter for a different year or athlete age. You can either specify the same sort order as I described in the previous paragraph or you can leave your relationship unmodified and specify the same sort order in the one row filtered portals to sort the records by score in descending order.
Retrieving data ...