A better way????

[bckwheat]

I have a simple database that has a field name "status". This field has three options on a pop up menu "out, low, completed". What I am trying to do is tally all the "outs" and place in a field, as well as "lows" and "completed" in their separate fields. I tried using using a "find" function and tallying but I think that a summary field or maybe a script would actually work. Any ideas?

[DykstrL]

You need 3 calculation fields and 3 summary fields.

Out_Calc = Case(PatternCount(status,"out") = 1,1,0)

Low_Calc = Case(PatternCount(status,"low") = 1,1,0)

Completed_Calc = Case(PatternCount(status,"completed") = 1,1,0)

Each field result needs to be a number and do not store the result so they will calculate any changes.

The 3 summary fields would be a total of the calculation field.

[bckwheat]

Thanks

I imported the first line of your script in the test field called "completed count number". I changed the "out_calc" with the field "Out count number" and I get a circular error.

I have enclosed the file if you would like to take a look at it.

The count totals for "out, low, and completed" are the red fields at the top of the layout.

Film manager.zip

[Fitch]

You could certainly script a Find, then Set Field to Status(CurrentFoundCount).

Or you could create a summary field that is Count(someField) -- someField has to be one that always has a value in it. Then put the summary field and the status field on a subsummary part that is sorted by status. Now Sort by status and preview or print. I don't think you need the extra PatternCount calcs in this case, since the field is based on a popup menu -- so I assume it has only one value per record. If you were using checkboxes, then dykstrl's method would be correct.

(BTW, Case(PatternCount(status,"out") = 1,1,0) can be written simply as:

PatternCount(status,"out"), at least in your case where it's always going to have just one occurrence. Or you could use (status = "out"), again it's the same thing in your case.)

You could also use a global field with a self-join relationship to status, along with Count(global::related). The advantage of this is that it dynamically updates. It will always total your whole file though, not just a found set, which may or may not be what you want. It could also be slow if you have thousands of records.

[bckwheat]

"Or you could use (status = "out"), again it's the same thing in your case.) "

I was trying to use status="out" and then trying to add to the total everytime it found an out but I couldn't figure out the counter and "total" number of "outs" would show in my "outs number count" field.

I am just trying to find the total number of outs, lows, completes and put that value, i.e. 8,9 whatever in a the appropriate total field (outs number count)

Thanks for your help.

B.

[Ugo DI LUCA]

Why don't use relationship for that.

3 global fields

g_out filled with "out"

g_low, filled with "low"

g_completed, filled with "completed"

and 3 relationships g_out::status, g_low::status and g_completed:: status

If you don't like global, just make it an auto enter text data.

Then count(relationship::status) will give each counts separately, until...you decide that the status should be filled by calculation upon certain job done, not done, to do...

[Fitch]

Ugo is spelling out the method I mentioned in my last paragraph above. That's one way to do it.

Another way is, if you're using the status="out" calc, which I'll call StatusOut, make one more calculated number field called "One," and the calc = 1. Just the number 1. OK, now make a relationship of the file to itself, we generally call that a self-join. In this case, call the relation "Uno" and make it One::One.

Now you can make calc fields like this:

Sum(Uno::StatusOut)

[Ugo DI LUCA]

Oops, sorry Tom I didn't saw the last paragraph as I went on what Bckweat didn't understood.

Your right that a Uno relationship would be great