On 7 Nov 2011, at 11:34, Emma Glaisher wrote: > My database is a job tracker. The job is a publication which is broken down > into sections, so the (relevant) fields are: > > Section No, Total Pages in Section, Date Approved > > I want to display the total pages that have been approved, preferably without > the user having to go into Preview mode and view sub-summaries. (I maintain > it, the only user is my line manager who needs to report on progress daily). > > So I wanted a calculation like ‘Sum of Total_pages_in_section where > Date_Approved is not blank’, but I don’t think this can be done. > > Is there a clever trick I’ve missed?
Emma If you are using FMP11, you can display subsummary parts in browse mode. Might that solve the problem? On the other hand, if you do want to use the Sum function, you need to know that it acts on a set of related records. In your shoes, I'd have a table Publications with a child table Sections, related by publication_id. In Sections, I'd add a field Approved_Pages, with a calculation something like Case(not isEmpty(Date Approved), Total Pages in Section, 0). Then in the Publications table, add a calculated field using the Sum function, summing Approved_Pages. Place this field on a layout, and for each record in Publications you'll see the corresponding number of approved pages for that publication. I hope that helps you move forward... Steve
