Summary Field problem

[Greg Hains]

Hi,

Im sure that this is easy to some, but my head is all scrambled trying to get my brain around it.

To keep the actual scenario as simple as I can, I

have the following setup. 700-odd records, with a number of fields storing information about timesheets. For example a structure of this

Location: Office1 (could be one of several items)

JobDate: 1/7/07 (could be any date)

Duration: 1.5 (cold be any duration)

Im looking to setup a summary field that will total up all the duraction hours if certain conditions are met with the Location and JobDate fields. Now I understand the actual calculation required, but so far I have the following method which is not good. One field that toggles a 1 or 0 if the record meets the criteria then another to total those records if they have that 0. Another method was to run a script that went from record 1 to totalling as it went, but I have 24 summary fields that I need to work with. As it also runs over the Internet it will be slow. Clumsy.

Is there a better way to setup this summary field so that it tallies up data within records with specific criteria?

Any tips appreciated.

Greg

[Anuviel]

Not sure if this is what you are looking for but I'll give it a try., if I am way off please let me know or anyone else when they post the correct answer

Set up a case calculation saying

Case ( YourFieldName = "Location1" ; Sum (YourDurationFieldName) ; YourFieldName = "Location2" ; Sum (YourDurationFieldName) ; "")

You can repeat as necessary. Rather then hard coding your location names into the calculation you could make a table that will hold your location names and use that field names in the calculation so you can adjust it dynamically.

[Anuviel]

If you want to add a date criteria into the calculation just add AND to insert the date range into the test part of the statement.

That will add all the data within each record and then you can pull it out depending on if you have a different layout at which you want to see all the summaries.

[Greg Hains]

Hi Anuviel,

Looks good - I will try it this evening when I get back to my FmPro.

Thanks, and I'll let you know.

Greg :P

[Greg Hains]

Excellent and perfect. Thankyou. :