Need help creating total counts

[Emilee]

Hello,

I am extremely new to filemaker and am kind of learning as I go. I am trying to create some total count fields based off of certain criteria but I cannot figure out how to do so.

My database is made up of members and practices, each from its own table with relationships connecting the two. In each table I have the province the record is from - for example John Doe is located in Alberta.  I want to create a dashboard type screen where I can total the number of active members per province and total number of active practices per province. As members or/and practices are added or removed the calculation should adjust accordingly. And active member/practice is based on a status, which is a number. If a member is active, they are a 1, otherwise they are a 3.

I have attached a photo of what i want the dashboard to look like.

If anyone has an idea please let me know, it would be really appreciated!

Edited April 21, 2016 by Emilee

[comment]

6 minutes ago, Emilee said:

I want to create a dashboard type screen where I can total the number of members per province and total number of practices per province.

This would be very easy to do if you had a table of Provinces, where each province would be a unique record. Then you would only have to count the related records (matched by province) in the other two tables.

 

6 minutes ago, Emilee said:

The dashboard would look like this

AB   150

BC    104

ON    60

This doesn't match what you said earlier: only one number is shown instead of the expected two.

[Emilee]

Sorry, i should have put a better example. I will edit...

 

9 minutes ago, comment said:

This doesn't match what you said earlier: only one number is shown instead of the expected two.

I've added a photo of what the dashboard should look like,a s my original example was not up to par.

9 minutes ago, comment said:

This would be very easy to do if you had a table of Provinces, where each province would be a unique record. Then you would only have to count the related records (matched by province) in the other two tables.

So if I have a 3rd table with all the provinces listed - what function do I use to count the related records in the firs two tables? I am still lost at that.

[AndrewPurvis]

You want to use Get ( FoundCount ) for this. It is fast and efficient. The really great part is that if you look through a relationship, it will return the number of related records. However, you can also write individual calculation fields that will find it for conditions you specify inside of them. This would reduce the need to have a relationship for each province.

Your parent (Provinces) table would have a record for each province. Your Members table would then have a calculation field that is defined solely as "Get ( FoundCount )" (without the quotation marks, of course). In a table displaying data from the Provinces table, you would list each province and place the child table's counting field next to it.

A potentially better solution would be to use a Cartesian relationship between two copies of the Provinces table, and link to the children past the second copy. This would allow you to use a portal to display the provinces and their related counts in rows that FMP will keep aligned. You list 8 provinces here, but what happens if you add another? Will you want to change your interface, or just have it update itself? The portal will do the latter, and with the formatting options and button capabilities, you will have no trouble viewing things the way you want and navigating to the child records you wish to review.

[comment]

I am sorry - I didn't see this was continued after my initial answer.

On 04/21/2016 at 7:17 PM, Emilee said:

So if I have a 3rd table with all the provinces listed - what function do I use to count the related records in the firs two tables?

Just define a summary field in each table, counting [any field that cannot be empty].

 

1 hour ago, AndrewPurvis said:

You want to use Get ( FoundCount ) for this. It is fast and efficient.

I don't think this is any faster or more efficient than a summary field - which has a number of additional potential uses (e.g. in a GetSummary() function).

 

Edited May 9, 2016 by comment