Filter then Count...

[Eukanuba]

Good Morning Guys,

i got a quick one for you...

i want to filter the instances of "-1"'s in a certain field then count how many there are total in the DB...

how would this look?

thanks in advance,

Eukanuba

[LaRetta]

Create a calculation (result is number) with:

GetAsBoolean ( PatternCount ( yourField ; "-1" ) )

Perform a find on this field for 1. The number of records in the returned found set is your count. There are other ways (using relationships etc) but, without more specific detail explaining your needs, this would be the simplest and quickest.

UPDATE: If there are possibly more than one occurrence of -1 within the field, remove the GetAsBoolean() portion. This calc will then produce how many times the -1 appears within the field. Perform a find on this field for [color:red]>0. This will give you the record set. You will then need a summary field which is 'total of this calc.'

LaRetta :wink2:

Edited July 14, 2008 by Guest
Added update

[Eukanuba]

i need it global though so i can tell how many records have the "-1" in it...

still need help,

Euka

[LaRetta]

Your response makes no sense to me. If you perform a find on that calculation, it will give you all the records which have a -1 in the field. You need to provide more information ...

[Eukanuba]

can you show me the calculation with the find?

[LaRetta]

Again, you aren't telling me anything about the context or purpose here so let me try to cover all bases:

If you want to view the records which have a -1 in them in a found set, you will need to perform a find. You can perform the find on the calculation or just search for -1 in the text field itself. By global, I will guess you mean a total count of -1's in the table.

Anyway, see attached and see if it makes more sense this way. :wink2:

FilterCount.zip

[Eukanuba]

that's what i needed...

i didn't even know "summary" existed... that's what i needed to know

Thanks So Much,

Euka

[LaRetta]

Hi Euka,

Summary fields are very powerful because, unlike a relationship, they base their result on the found set (which may be less than the total table). I used it here because 1) I was unsure what you wanted to accomplish and 2) it is more flexible than Sum() or other aggregate functions. As per FM Help, "Use summary fields to calculate values such as subtotals, averages, and grand totals across multiple records. For example, a summary field can display the grand total of all sales in the month of May in a report." :smile2: