Jump to content
balooka

Counting number of occurrences in a found set

Recommended Posts

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 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites
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 by comment

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites
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 by comment

Share this post


Link to post
Share on other sites
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 by balooka

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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 by comment

Share this post


Link to post
Share on other sites

 

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)

 

 

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by comment

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

By using this site, you agree to our Terms of Use.