I've been pulling my hair out trying to solve this one!
A client's database in ZC contains contractors, projects, and project costs. Many of these contractors have multiple projects. My client wants to generate a "report" showing three columns: contractors, total number of projects for the contractor, and total cost for those projects.The report should be sorted by total number of projects. In addition, this report is a filtered view based on other criteria.
I approached this problem by generating an html view using table html as follows
- Made a list of all contractors per some criteria via .getall()
- Removed duplicates for this list per typical if .contains loop - also sorted it alphabetically.
- Using this list of unique contractors, a for each loop then calculates via two aggregate functions the total number of projects (records) for a contractor (as an integer list), and the sum of project costs. These were put into two more lists.
Ok, so now I have three lists, contractors, total number of projects, and total cost of projects. I need to sort on total number of projects and somehow keep the contractor and cost lists aligned. How?
I then looked to see if maps could help but alas there are no functions to sort them.
So now I have an html table but it's nor sorted per my clients wishes.
Any ideas?
Cheers,
John Whitney
PS to my followers: I'll be posting more and more under this new account, buzzap.