counting days worked

[unclejoe]

I have fields as such:

Ticket Number Date District (and others not pertinant)

Ticket Number field is auto numbered, cant change, every record is unique.

Date field is auto inserted, can change if needed.

District field values are selected from a fixed list (CGSD, CASD, JMSD, SEGSD).

I would like to keep a running count of days worked at each district and a grand total of all 4 districts. There could be several entries for each district on any day, example:

Ticket Number Date District

1 11/03/2003 CGSD

2 11/03/2003 CGSD

3 11/03/2003 CGSD

4 11/04/2003 JMSD

5 11/04/2003 JMSD

What I would like to see is that I worked 1 day at CGSD and 1 day at JMSD and total days worked would be 2.

Any Help would be appreciated

[CobaltSky]

Hello unclejoe,

There are a number of ways to approach the problem you've outlined, but one of them would be to utilize the ValueListItems( ) function to isolate unique occurrences of the values you wish to count.

For instance, to count the number of days worked at each location, you could create a stored calculation field (it need not appear on any layouts) with the formula defined as District & Date, then create a value list called 'LocationDays' which is defined to draw its values from the calc field. Once this is in place, you would be able to count the number of days worked at CGSD using an unstored calc with a formula along the lines of:

PatternCount(ValueListItems(Status(CurrentFileName), "LocationDays"), "CGSD")

- and similarly for each of the other three locations.

Along the same lines, you would be able to find the total number of days worked (regardless of location) by creating a value list called 'WorkedDays' which is defined to draw its values from the 'Date' field, then creating an unstored calculation with a formula along the lines of:

WordCount(Substitute(ValueListItems(Status(CurrentFileName), "WorkedDays"), "/", ""))

Whilst there are various other ways to come at the problem, based on what you've described, the above may be suitable.

[unclejoe]

Thanks Ray! I finally had the time to try your solution and it worked perfectly. I really appreciate the time you took to help me. Could you recommend a book or website that would assist me with questions like this?

Again Ray, I appreciate the time you took to assist me with my question.

Thanks

Joe

[CobaltSky]

Hi Joe,

Glad to hear you have your problem solved.

There are quite a lot of online resources for those who want to expand their horizons with FileMaker. Forums like this one (and there are others also) are of use, as are a selection of the sites which can be found on the FileMaker Web Ring:

http://l.webring.com/hub?ring=fmpring

(you'll find a number of useful tips and tricks sites on the ring).

Publications such as FileMaker Advisor and The FIleMaker Magazine are also accessible online and may be worth checking out:

http://filemakeradvisor.com/

http://www.filemakermagazine.com/

As regards books, thre are many available that cater to different needs, interests and skill levels and I hesitate to make a recommendation. However you will find links to reviews of a selection of different offerings on sites such as FileMaker Today

http://www.filemakertoday.com/

Last, but by no means least, you might like to browse the technical resources on the FileMaker Inc web site:

http://www.filemaker.com/

It includes a knowledge base of technical notes and a number of papers and articles about specific issues and aspects of FileMaker.