Counting number of occurrences in a found set

[balooka]

Hi

After doing a search I get 65 records. The records have field [A] that holds 3 values; ONE, TWO and THREE. In the search result (as list view) I'd like to see the total amount of ONE's, TWO's and THREE's. I can self relate to get that value but isn't there a more elegant way to count N occurrences in a found set?

 

Thanks 

[rwoods]

Hi Balooka

I'm not sure there is a very elegant way of doing this, but here is the inelegant way.

Create some number fields with an Auto Enter Calculation (NOT a calculation field to improve performance) that says :-

Field Name: OneifOne

If (A = "One" ; 1 ; 0 )

Field Name: OneifTwo

If (A = "Two" ; 1 ; 0 )

Field Name: OneifThree

If (A = "Three" ; 1 ; 0 )

Then create summary fields :-

FieldName: TotalOfOnes

Summary ( Total of OneifOne)

FieldName: TotalOfTwos

Summary ( Total of OneifTwo)

FieldName: TotalOfThrees

Summary ( Total of OneifThree)

This would get very boring if you had 100 different value options in the field A.

Maybe someone else has a more elegant solution?

[balooka]

Hahah that would indeed get very boring, but this solution might just work fine for 5 options. I'll give this a shot - thank you very much!

[comment]

3 hours ago, balooka said:

I can self relate to get that value

No, you cannot do that, if you want the results to reflect the found set. A relationship will ignore your find results and report on the entire table instead.

The elegant way to do this is to sort your records by the A field, and have a corresponding sub-summary part on the layout. Place a summary field defined as Count of [A] (or of any field that cannot be empty, such as the record's unique ID field) inside this part. If you like, you can delete the body part of the layout, so that only summaries are shown.

 

 

Edited March 1, 2017 by comment

[rwoods]

Ah yes, I had forgotten that FileMaker will now show sub-summaries in Browse Mode, it only used to be possible in Preview Mode.

If it is acceptable to have the records sorted by 'A' in your use-case, then I wholeheartedly agree with comment, that it would be more elegant that way!

[comment]

17 minutes ago, rwoods said:

FileMaker will now show sub-summaries in Browse Mode, it only used to be possible in Preview Mode.

If I am not mistaken, "now" refers to version 10?

 

BTW:

3 hours ago, rwoods said:

Create some number fields with an Auto Enter Calculation (NOT a calculation field to improve performance)

In what way does using an auto-entered calculation improve the performance compared to a stored calculation field?

 

Edited March 1, 2017 by comment

[balooka]

1 hour ago, comment said:

No, you cannot do that, if you want the results to reflect the found set. A relationship will ignore your find results and report on the entire table instead.

The elegant way to do this is to sort your records by the A field, and have a corresponding sub-summary part on the layout. Place a summary field defined as Count of [A] (or of any field that cannot be empty, such as the record's unique ID field) inside this part. If you like, you can delete the body part of the layout, so that only summaries are shown.

 

 

But to make use of the sub-summary I must sort on the A-field values, correct? I need to sort on another value and not disturb the current layout. 

ETA:

Solved it in another manner using pattern count. This way I can sort on whatever field I need and see the individual summary values. 

 

FIELD PATTERN ONE would be: 

PatternCount ( Field_A ; "ONE" )

This will look for the pattern in field A

and a resume total of that field

FIELD SUMMERY ONE would be: Resume total of FIELD PATTERN ONE

 

Edited March 1, 2017 by balooka

[rwoods]

If you cannot sort on the 'A' field, then using sub-summaries won't be possible, and you may have to use my original suggestion.

Comment, yes, I may be referring to a very old version!

Comment, I tend to avoid un-stored calculation fields unless they are only ever going to be used on simple form views, since summarising unstored calcs is slow, particularly if you are using a list view with lots of found records. In this situation it may make very little difference to performance, as FileMaker could store the calculation results anyway, it was just a general point for good design.

[comment]

44 minutes ago, balooka said:

I need to sort on another value and not disturb the current layout. 

Then I would suggest you script the process of getting the sub-summary values and place the results in a global text field. You could use the Fast Summaries method for this, or possibly the ExecuteSQL() function (although here you would have to replicate your find criteria).

 

21 minutes ago, rwoods said:

it was just a general point for good design.

I think you will find it difficult to come up with a practical example where an auto-entered calculation can effectively replace a calculation field that would have to be unstored. There would be nothing to trigger a refresh when the related value changes.

 

Edited March 1, 2017 by comment

[comment]

 

I forgot to mention another possibility: define a summary field sListA as List of {A], then use a calculation field (result is text) along the lines of =

List (
"One: " & ValueCount ( FilterValues ( sListA ; "One" ) ) ;
"Two: " & ValueCount ( FilterValues ( sListA ; "Two" ) ) ;
"Three: " & ValueCount ( FilterValues ( sListA ; "Three" ) )
)

(untested)

 

 

 

[LaRetta]

5 hours ago, rwoods said:

I tend to avoid un-stored calculation fields unless they are only ever going to be used on simple form views, since summarising unstored calcs is slow

Such a calculation would be stored and thus would not be slow.  

10 hours ago, balooka said:

The records have field [A] that holds 3 values; ONE, TWO and THREE.  I can self relate to get that value

Then field [A] is a multiline that you need to report on its individual values separately?  I suggest you consider using another table.

[comment]

21 minutes ago, LaRetta said:

Then field [A] is a multiline

Ah, that is a good question. That would preclude any solution based on sorting. But my last suggestion using a 'List of' summary field would still work, I think.

 

 

Edited March 1, 2017 by comment