Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Counting on contents (from value list)


Patrick van Halderen

Recommended Posts

Hi all,

 

I don't know if this has been covered yet -couldn't find it at the moment- but I want to get the following done, and don't seem to find a standard procedure for it...

 

A friend of mine has a database for some local club, with all members in it.

These members all have a category field, in which they are grouped. What he wants, is a count of the members in each category, for example:

The available groups are "youth", "seniors" and "donators". He wants to have a count-field for each category in which the number of members are displayed.

 

Any ideas? Tanks in advance.

 

Patrick

Link to comment
Share on other sites

Hello Patrick,

 

There are several ways that your friend could go about producing a dynamic summary of the category memberships. Here is one:

 

1. He should create three global text fields called 'gYouth", 'gSeniors' and 'gDonators', place them on a layout, enter the words youth", "seniors" and "donators into the respective fields and then delete them from the layout again.

 

2. He should create a self-join relationship (ie one that joins the file to itself) called 'YouthCategory' which matches the gYouth field to the Category field, a second self-join relationship called 'SeniorsCategory' which matches the gSeniors field to the Category field, plus a third self-join relationship called 'DonatorsCategory' which matches the gDonators field to the Category field

 

3. He should then create three unstored calculation fields with result type set to number, as follows:

 

cYouthCount = Count(YouthCategory::Category)

 

cSeniorsCount = Count(SeniorsCategory::Category)

 

cDonatorsCount = Count(DonatorsCategory::Category)

 

These last three fields, when displayed by your friend in an appropriate place (eg in a footer summary at the bottom of the memberships listing) will show the current number of memberships in each category within the file. cool.gif

Link to comment
Share on other sites

Another way. Three new calc fields

 

Seniors (Number):

Case(YourIDField) = "Senior",

1,0)

 

Youths (Number):

Case(YourIDField) = "Youth",

1,0)

 

Donees (Number):

Case(YourIDField) = "Donator",

1,0)

 

Then simply create a summary field for each as thus:

 

Senior Summary: Count(Seniors)

 

Does the same thing as Ray's, but less work involved.

 

Tom

Link to comment
Share on other sites

BTW: Your summaries can be either "Count" or "Total" as each entry equals 1, they can be added for a total as well

 

Tom

Link to comment
Share on other sites

Thanks guys!

The page has been printed and given... I'll try it myself too, for feeding my curiosity :-)

Link to comment
Share on other sites

Originally posted by FileMakin' Tom:

[qb]Does the same thing as Ray's, but less work involved.[/qb]

Actually, they don't do the same thing, and the difference is important to note.

 

The method I suggested will always show the totals for the whole file, regardless of the state of the file.

 

By contrast, the method Tom outlined will always show the sub-totals for the found set. It will only be when there is no found set (eg after a 'Show All Records' command) that the calculation-and-summary-field method will give the same 'answers' as the relationship-and-calculation method.

 

Where the ability to summarise found sets is preferred, the latter method would be advantageous. However after considering the options, I chose to recommend the relationship method because there was no mention of summarising only found data sets in Patrick's outline of his friend's requirements.

Link to comment
Share on other sites

Originally posted by FileMakin' Tom:

[qb]BTW: Your summaries can be either "Count" or "Total" as each entry equals 1, they can be added for a total as well

 

Tom[/qb]

In fact that is not correct. :eek:

 

The 'Count' summary option will count all fields which have a value in them, including those fields where the value is zero - only null fields are omitted from the count.

 

Since the Case( ) formulae you recommended on this thread are constructed so as to never produce a null result, the 'Count' option will always display the total number of records in the found set, whereas the 'Total' option will show the number of found records which match the corresponding criterion. wink.gif

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use