Please see FileMaker Pro 12 database I've created to illustrate my problem:

 

https://dl.dropboxusercontent.com/u/24821795/Example.fmp12

 

I want to count the number of times an Activity has been assigned to a Staff member, but there are a couple of things making it tricky (not impossible, I hope):

 

1. When the user performs a Find, the count should update to only include the found records.

2. The user can add to the list of activities.

 

In the example provided, SelfJoinCount and Activities::Count are not what I want - they both count Activity (e.g. Archery has been assigned to two staff members), but do not meet criteria 1. above.

 

Try performing a Find of Gender = M

 

* The values of ReviewedCount (a summary field, counting Reviewed) change to 3, which is what I want.

 

* The values of SelfJoinCount and Activities::Count do not change. In this case, I want them to change to 1 (i.e. One record with Ballooning, one record with Bird watching and one record with Archery in the found set).

 

I could create a calculation field with a 1 in it if the activity occurs and then a summary field counting that 1 for every single activity in the database, BUT this won't work because of criteria 2. above (also, there are a lot of activities).

 

Any ideas?

If a Person can have multiple Activities, and an Activity can be assigned to several People, you need a join table (which implements a many-to-many relationship).

 

This will be the basis for “parent” tables (Staff and Activity) to look into if you want to use portal and calculations, and also if you create reports based on Activity, Staff members and /or date and use sub-summary fields.

 

See this thread (where you can substitute ”Job“ with ”Activity“).