If you are more comfortable with SQL, you should probably do it in SQL. Is there a particular reason you need to use a portal (e.g. you want to click on a raw to go to the Vehicle record)? If it is just a style issue, you can load the results in a webviewer as html table.
Sorry, I just realised, I did not actually answered your question for FileMaker solution with no sql
The usual approach:
Add a calculated field to Vehicles table to calculate total costs of Services agains every vehicle
Add a self relationship to Vehicles table, cartesian (X) and sort it by your calculated field.
Add a portal based on the new relationship, no scroll bar and display 10 rows only.
If you have a lot of records in both tables, this is going to be very slow.
An alternative is to run a script at set times (e.g every hour) to rank your Vehicles records - e.g. sort them by total cost, flag top ten and create a relationship to display the flagged records only. I would also calculated the Services costs as a part of this script instead of calculation on a field.
A third alternative:
Create the Dashboard as a single-record table (or perhaps a Users table, depending on your need). Create the relationship from there to Vehicles as either a Cartesian join or via a global field. Create the total field in the Vehicles table as a number field. Use Script Triggers to update that field whenever conditions warrant (such as when a repair / maintenance record is added / updated). Then, your dashboard can sort the Vehicles records on an indexable field, which will be very fast, irrespective of the number of maintenance records added.
Thanks for your responses I'll give them a try. I watched a video on youtube for creating dashboards by the Anvil Dataworks / FM Academy but the author didn't cover how he was able to create the top 12 records in portals. He had two of them on his dashboard. I'll give your suggestions a try and see how I make out.
Portal(s), filtered or not, can definitely display summary fields. see my article on how I did this. you may or may not include ExecuteSQL(), but I simply used summary fields (from the child data). Along with the other answers here, you may have some good ideas.
This seems like a good idea. How do I access the buffered field values in FM? So if a person makes a change to one of the records I'd need to subtract the old value and then apply the new value to the total.
Correct. One method you can use is to use the OnObjectEnter trigger to capture the original value in a variable. Then, use the onObjectSave trigger to compare the old value to the new one. If they're different, you can take the difference and apply it to the total.
What a fantastic demo!!! Thanks so much Bev for sharing.